Sql-server – SUM and JOIN SELF

sql server

I need help with sum and join self.

I have table like 

Buyer   Info2     LinkKey    Date           Credit   Debit
Samuel    20       S15       2012-03-15      500         0
Samuel    20       S15       2012-04-26       0        300
Maria     20       123       2012-05-03      300         0
Maria     20       123       2012-07-20       0        300
Maria     20       456       2012-02-09      150         0

I need get sum(credit-debit) where buyer=buyer, info2=info2, linkKey=linkKey by period (period from credit)

I need result like 

Buyer    Januar  Februar   Mart    April   May   AMOUNT
Samuel   0          0       200      0      0      200
Maria    0         150        0      0      0      150

Best Answer

I hope this helps. Used a union to denormalize your data and then wrap the 3 union queries with the sum of each month so each buyer will only have a single row. Then you join this result with total (credit + debit) for each buyer for the last column of the final result. 

select final.* , inline2.total 
from (
        select inlineview.Buyer ,
            sum(inlineview.Jan) , 
            sum(inlineview.Feb)  , 
            sum(inlineview.Mar) 
        from (
            select Buyer ,sum(Credit+Debit) as Jan , sum(0)  as Feb , sum(0) as Mar 
            from table
            where CONVERT(VARCHAR(12),date, 100) like "Jan%"
            group by Buyer

            Union 

            select Buyer , sum(0) as Jan , sum(Credit+Debit) as Feb , sum(0) as Mar 
            from table
            where CONVERT(VARCHAR(12),date, 100) like "Feb%"
            group by Buyer

            Union

            select Buyer , sum(0) as Jan , sum(0) as Feb , sum(Credit+Debit) as Mar 
            from table
            where CONVERT(VARCHAR(12),date, 100) like "Mar%"
            group by Buyer 
        ) inlineview  
        group by Buyer 
    ) final 
    left join (
        select Buyer, sum(Credit+Debit) as Total 
        from Table 
        group by Buyer 
    ) inline2 on (inline2.Buyer = final.Buyer)
Related Question