How can I divide grouped data by date in do columns

Question

I have a data set that I would like to group by a customer code and for each code I would like to Sum transaction totals between the dates of a month and have each month become a separate column. I can make this dynamic my main concern is structuring the initial select to out put the data as desired.

What I have:

SELECT [cmasterno]
    ,CASE 
        WHEN Month([dtrs]) = 1
            AND Year([dtrs]) = YEAR(GETDATE())
            THEN SUM([nftrsamt])
        END AS 'Jan 2014'
    ,CASE 
        WHEN Month([dtrs]) = 2
            AND Year([dtrs]) = YEAR(GETDATE())
            THEN SUM([nftrsamt])
        END AS 'Feb 2014'
FROM [AccountMate].[dbo].[gltrsn]
WHERE csource LIKE 'AR'
    AND cacctid LIKE '%4220%'
GROUP BY cmasterno

A sample of the data would be

cmasterno   dtrs    ntrsamt
CEN01-A    1/24/2014    -22.2
AUT129-A    2/24/2014   -0.84
CEN01-A    1/22/2014    -19.86
CEN01-A    2/22/2014    -13.2
AUT129-A    1/22/2014   -18.42
AUT129-A    1/22/2014   -1
AUT129-A    2/22/2014   -316.56
CEN01-A    2/21/2014    -2.04

score 2 · Accepted Answer · answered Nov 19 '15 at 16:19

2

Use conditional SUM

SELECT [cmasterno],
       SUM (CASE 
               WHEN Month([dtrs]) = 1 AND Year([dtrs]) = YEAR(GETDATE())
                    THEN [nftrsamt]
               ELSE 0
            END
           ) AS 'Jan 2014',
       SUM (CASE 
               WHEN Month([dtrs]) = 2 AND Year([dtrs]) = YEAR(GETDATE())
                    THEN [nftrsamt]
               ELSE 0
            END
           ) AS 'Feb 2014'
FROM [AccountMate].[dbo].[gltrsn]
WHERE csource LIKE 'AR'
    AND cacctid LIKE '%4220%'
GROUP BY cmasterno

answered Nov 19 '15 at 16:19

Juan Carlos Oropeza

47,252
12
78
118

Looks like this will work testing and Ill let you know. Thanks !! It didnt occur to me to move the sum outside of the case! – Allen Craig Nov 19 '15 at 16:25
Should work. Dont worry those things you have to learn it on the way. You can do the same with `COUNT()` or `AVG()` – Juan Carlos Oropeza Nov 19 '15 at 16:28

score 1 · Answer 2 · answered Nov 19 '15 at 16:26

Just to be complete you can do this with joins too

SELECT [cmasterno], sum(jan.nftrsamt) as 'Jan 2014', sum(feb.nftrsamt) as 'Feb 2014'
FROM [AccountMate].[dbo].[gltrsn] b
LEFT JOIN [AccountMate].[dbo].[gltrsn] jan on b.cacctid = jan.cacctid and Month(jan.[dtrs]) = 1 AND Year(jan.[dtrs]) = YEAR(GETDATE())
LEFT JOIN [AccountMate].[dbo].[gltrsn] feb on b.cacctid = feb.cacctid and Month(feb.[dtrs]) = 2 AND Year(feb.[dtrs]) = YEAR(GETDATE())
WHERE csource LIKE 'AR' AND cacctid LIKE '%4220%'
GROUP BY cmasterno

Depending on your indexes a join can be much faster than a case. That is O(1) and not O(N)

How can I divide grouped data by date in do columns

2 Answers2