SUM and JOIN SELF

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 result like

``````Buyer    Januar  Februar   Mart    April   May   AMOUNT
Samuel   0          0       200      0      0      200
Maria    0         150        0      0      0      150
``````
-
Why are you trying to denormalize the data? –  Thomas Stringer Aug 19 '12 at 22:44
also you say "linkKey=linkKey", but you have two linkKeys for Maria and only one entry for Maria in the result? –  Simon Righarts Aug 19 '12 at 22:52
Do you have 1 row per sales person for each month? –  Emmad Kareem Aug 20 '12 at 4:33
Take a look at SQL Server pivots - e.g. blogs.msdn.com/b/spike/archive/2009/03/03/… –  Emmad Kareem Aug 20 '12 at 5:09
I need report for credit by buyer for period. –  eden Aug 20 '12 at 11:22

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.

-

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 (
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%"

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%"

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%"
) inlineview
) final
left join (
from Table
``````
-

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
Amount      = Credit - Debit,
TotalAmount = SUM(Credit - Debit) OVER (PARTITION BY Buyer)
FROM atable
)
SELECT
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 `Date`s.

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.

-