SUM and JOIN SELF

Question

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

Answer 1

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) 

Answer 2

Best is probably to do this in your client, such as Reporting Services (SSRS).

SSRS has a useful function called RunningValue. You should be able to use this to get the Sum of Credit-Debit up to the month of interest. You should also be able to use a Matrix to put the months across the columns.

The biggest problem is that I'm not really understanding your logic around the values in the example you've shown.

...but the answer definitely seems to lie in RunningValue and a Matrix.

Answer 3

Basically, it seems you need to group by Buyer and the name of the correct month, then pivot the aggregated results on the month name column. Here's how you could go about it, provided you are using SQL Server 2005 or later version:

WITH monthly AS (
  SELECT
    Buyer,
    Amount      = Credit - Debit,
    CreditMonth = DATENAME(MONTH, MIN(Date) OVER (PARTITION BY Buyer, Info2, LinkKey)),
    TotalAmount = SUM(Credit - Debit) OVER (PARTITION BY Buyer)
  FROM atable
)
SELECT
  Buyer,
  January  = ISNULL(January , 0),
  February = ISNULL(February, 0),
  March    = ISNULL(March   , 0),
  April    = ISNULL(April   , 0),
  May      = ISNULL(May     , 0),
  Amount   = TotalAmount
FROM monthly
PIVOT (
  SUM(Amount) FOR CreditMonth IN (January, February, March, April, May)
) p
;

This is how the query works:

1. Every Credit/Debit column pair in the monthly common table expression (CTE) is represented as a single column, Credit - Debit, aliased Amount.

2. The month name for every Amount value is derived, using DATENAME(), from the minimum Date value in the same group (or partition) of Buyer, Info2, LinkKey as the current row. (The requirement is to use the credit date. The credit date is supposed to go before the debit one(s), hence looking for the minimum date.) The query uses a window MIN() function to get the minimum Dates.

3. The TotalAmount column is the sum of all Credit - Debit results per Buyer. It is calculated using a window aggregate function too, which is SUM() this time. (The column is re-aliased as Amount in the final SELECT to match your expected output, but it seemed to me to make more sense to call it TotalAmount at this stage.) Eventually, this is what the monthly CTE produces:

   Buyer    CreditMonth  BIL_Amount  Amount
   Samuel   March        500         200
   Samuel   March        -300        200
   Maria    May          300         150
   Maria    May          -300        150
   Maria    February     150         150
   

4. The PIVOT clause in the main query does both grouping and pivoting of the above result set. Grouping is implicit: all columns in the monthly dataset except one (Amount) are the (implicit) GROUP BY columns, that's just how PIVOT works. (The CreditMonth column, in addition to being a GROUP BY column, is specified to be the one that the result set is pivoted on.) So, essentially, the monthly results are being grouped by Buyer, CreditMonth, Amount.

5. Not all months may be present for every buyer. That means some month columns might contain NULLs in the final result set. That is the reason why the final SELECT uses ISNULL(): to default those NULLs to 0's.

So, that is what the query is doing, and you can try and play with it at SQL Fiddle too.