CODEWITHSUNDEEP
sql-servert-sql
NikRED
NikRED

Reputation: 1195

How can calculate transaction data

I have a table like this,Consider i have around 5 million records.

           Transaction id|Amount|CustomerId|date
                1        |  100 | 20       |1/1/2012  
                2        |  230 | 30       |2/2/2012
                3        |  320 | 20       |2/3/2012
                   etc...

How can i find total amount for last 5 transactions of each customer in each quarter in 2012?

Output: Quarter|Customerid|totalAmount

             1   |  20      | 40000
             1   |  30      | 300000
             2 ...etc...

Please write an efficient method..

Upvotes: 1

Views: 101

Answers (1)

Gulli Meel
Gulli Meel

Reputation: 891

You should post the ddl.But You can try something like this.It should work..

with mycte as
(
select customerid,datepart(qq,dt) as qtr,amount,
row_number() over(partition  by                    
datepart   (qq,dt),customerid order by dt desc,transaction id desc) as rn
from table where dt >= '01/01/2012'
) 
select qtr,customerid,sum(amount) as amt
from mycte
where rn <= 5
group by qtr,customerid

If you want someone else to write efficient queries for you.Then you have to do some hard work by providing the ddl,indexes etc and some sample data and what approaches you have used till now.

Upvotes: 1

Related Questions