I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in
OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT )
between 400 & 500 # 20
> 501 # 10
NOV between 300 & 400 #30
between 400 & 500 # 20
> 501 # 10
and so on for a 6 month period.You will need to use a self join. Something like this:
select Table1.CustomerID, sum(Table2.CheckValue)
from YourTable Table1 inner join YourTable Table2
on Table1.CustomerID = Table2.CustomerID
and Table1.CheckDate > Table2.CheckDate
and datediff(day, Table2.CheckDate, Table1.CheckDate) <= 4
group by Table1.CustomerID
having sum(Table2.CheckValue) > 400
Monday, March 12, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment