Sales quantities columns month

Question

Post reply

Sales quantities columns month

LuisSurf

Valued Member

Points: 73
More actions
December 9, 2010 at 7:52 am

#230414

Hi everyone!!! I have a very large sales data and want to represent it in month columns. Table is like this:
TrxId ItemId Date Quantity
-------- ------ ---------- --------
Sales001 ItemA 2010-01-01 2
Sales002 ItemB 2010-01-01 4
Sales003 ItemA 2010-01-05 5
Sales004 ItemA 2010-02-17 3
Sales005 ItemB 2010-04-23 7
[/Code]
I want to get something like this:
ItemId Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dic
------ --- --- --- --- --- --- --- --- --- --- --- ---
ItemA 7 3 0 0 0 0 0 0 0 0 0 0
ItemB 4 0 0 7 0 0 0 0 0 0 0 0
Thank's in advanced!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

-- Note you can use PIVOT for this as well

DECLARE @T TABLE(TrxId VARCHAR(10), ItemId VARCHAR(10),[Date] DATETIME, Quantity INT)

INSERT INTO @T(TrxId,ItemId,[Date],Quantity)

SELECT 'Sales001','ItemA','20100101' ,2 UNION ALL

SELECT 'Sales002','ItemB','20100101' ,4 UNION ALL

SELECT 'Sales003','ItemA','20100105' ,5 UNION ALL

SELECT 'Sales004','ItemA','20100217' ,3 UNION ALL

SELECT 'Sales005','ItemB','20100423' ,7

SELECT ItemId,

SUM(CASE WHEN DATEPART(month,[Date])=1 THEN Quantity ELSE 0 END) AS 'Jan',

SUM(CASE WHEN DATEPART(month,[Date])=2 THEN Quantity ELSE 0 END) AS 'Feb',

SUM(CASE WHEN DATEPART(month,[Date])=3 THEN Quantity ELSE 0 END) AS 'Mar',

SUM(CASE WHEN DATEPART(month,[Date])=4 THEN Quantity ELSE 0 END) AS 'Apr',

SUM(CASE WHEN DATEPART(month,[Date])=5 THEN Quantity ELSE 0 END) AS 'May',

SUM(CASE WHEN DATEPART(month,[Date])=6 THEN Quantity ELSE 0 END) AS 'Jun',

SUM(CASE WHEN DATEPART(month,[Date])=7 THEN Quantity ELSE 0 END) AS 'Jul',

SUM(CASE WHEN DATEPART(month,[Date])=8 THEN Quantity ELSE 0 END) AS 'Aug',

SUM(CASE WHEN DATEPART(month,[Date])=9 THEN Quantity ELSE 0 END) AS 'Sep',

SUM(CASE WHEN DATEPART(month,[Date])=10 THEN Quantity ELSE 0 END) AS 'Oct',

SUM(CASE WHEN DATEPART(month,[Date])=11 THEN Quantity ELSE 0 END) AS 'Nov',

SUM(CASE WHEN DATEPART(month,[Date])=12 THEN Quantity ELSE 0 END) AS 'Dec'

FROM @T

GROUP BY ItemId

ORDER BY ItemId;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

LuisSurf Valued Member Points: 73 More actions · Answer 2

Wow Mark!!! Thank's a lot!!!

Didn't know that can use a CASE inside a SUM().

It runs very fast! Over 5million rows in less 8sec!!!

LuisSurf Valued Member Points: 73 More actions · Answer 3

Hi!!! I really sorry... I forget one thing... The quantities includes returns and the returns quantities are stored as positive numbers like this:

TrxId ItemId Date Quantity

--------- ------ ---------- --------

Sales001 ItemA 2010-01-01 2

Sales002 ItemB 2010-01-01 4

Sales003 ItemA 2010-01-05 5

Sales004 ItemA 2010-02-17 3

Sales005 ItemB 2010-04-23 7

Return001 ItemA 2010-01-31 1

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 4

luissurf_70 (12/9/2010)
Hi!!! I really sorry... I forget one thing... The quantities includes returns and the returns quantities are stored as positive numbers like this:
TrxId ItemId Date Quantity
--------- ------ ---------- --------
Sales001 ItemA 2010-01-01 2
Sales002 ItemB 2010-01-01 4
Sales003 ItemA 2010-01-05 5
Sales004 ItemA 2010-02-17 3
Sales005 ItemB 2010-04-23 7
Return001 ItemA 2010-01-31 1

Maybe this?

SELECT ItemId,

SUM(CASE WHEN DATEPART(month,[Date])=1 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Jan',

SUM(CASE WHEN DATEPART(month,[Date])=2 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Feb',

SUM(CASE WHEN DATEPART(month,[Date])=3 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Mar',

SUM(CASE WHEN DATEPART(month,[Date])=4 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Apr',

SUM(CASE WHEN DATEPART(month,[Date])=5 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'May',

SUM(CASE WHEN DATEPART(month,[Date])=6 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Jun',

SUM(CASE WHEN DATEPART(month,[Date])=7 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Jul',

SUM(CASE WHEN DATEPART(month,[Date])=8 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Aug',

SUM(CASE WHEN DATEPART(month,[Date])=9 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Sep',

SUM(CASE WHEN DATEPART(month,[Date])=10 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Oct',

SUM(CASE WHEN DATEPART(month,[Date])=11 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Nov',

SUM(CASE WHEN DATEPART(month,[Date])=12 THEN CASE WHEN TrxId LIKE 'Sales%' THEN Quantity ELSE -Quantity END ELSE 0 END) AS 'Dec'

FROM @T

GROUP BY ItemId

ORDER BY ItemId;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

LuisSurf Valued Member Points: 73 More actions · Answer 5

LuisSurf

Valued Member

Points: 73

December 9, 2010 at 10:29 am

#1260668

That's it!!! Thank's a lot!!!