Sales by month Group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sales by month Group

Author

Topic

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-01-14 : 16:12:55

Hi everyone, I need your support on this: I have a sql 2008 view and this shows me the total sales per mounth of every item, the problem is that the items has one row per mounth and I need only one row per item but with the sales per month, exemple.
[item-1] [month-1] [10] [mont-2] [2 sold] how can I do this?

my code is this :

SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Month
FROM         dbo.View_VK_ORDERS_SUMARY_F
WHERE     (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor, Month
ORDER BY ItemCode

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-01-14 : 17:28:15

Use PIVOT - something like this:

SELECT * FROM 
(
SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Month
FROM         dbo.View_VK_ORDERS_SUMARY_F
WHERE     (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor, Month
) S
PIVOT (SUM(Quantity) FOR Month IN ([Month-1],[mont-2])) P

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-01-14 : 18:20:11

Thanks James,it works but without the [Month-1] instruction, the problem now is how can I control the year? because I can have month from diferent year, this is my code

SELECT * FROM 
(
SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Month
FROM         dbo.View_VK_ORDERS_SUMARY_F
WHERE     (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor, Year, Month
) S
PIVOT (SUM(Quantity) FOR Month IN ([12],[11],[10],[9],,[7],[6],[5],[4],[3],[2],[1])) P
ORDER BY ItemCode ASC

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-01-14 : 21:01:08

Finally it works but only in screen, whe I try to access the data from Crystal Reports it don't has the Months numbers (12,11,10,9...)
I suppose this is because I have a pivot table, is this true? how can I get all the data, exist another way to get all this data to export to crystal? this is the code :

SELECT * FROM 
(SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Month
FROM         dbo.View_VK_ORDERS_SUMARY_F
WHERE     (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor, Year, Month) S
PIVOT (SUM(Quantity) FOR Month IN ([12],[11],[10],[9],,[7],[6],[5],[4],[3],[2],[1])) P
ORDER BY ItemCode ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 05:57:24

quote:
Originally posted by mjimenezh

Finally it works but only in screen, whe I try to access the data from Crystal Reports it don't has the Months numbers (12,11,10,9...)
I suppose this is because I have a pivot table, is this true? how can I get all the data, exist another way to get all this data to export to crystal? this is the code :

SELECT * FROM
(SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Month
FROM dbo.View_VK_ORDERS_SUMARY_F
WHERE (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor, Year, Month) S
PIVOT (SUM(Quantity) FOR Month IN ([12],[11],[10],[9],,[7],[6],[5],[4],[3],[2],[1])) P
ORDER BY ItemCode ASC

why not populate this to another table with more detailed column names and use it as crystal report source?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-01-15 : 10:20:08

Hi Visakh16, I don't do that you recommend because I don't know how, i'm new on sql development can you explain me more?

Thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 05:05:13

make the query like this and see


SELECT ItemCode, ItemDescription, DebCode, Debtor, Year,
[12] AS Dec,
[11] AS Nov,
[10] AS Oct,
[9] AS Sep,
 AS Aug,
[7] AS Jul,
[6] AS Jun,
[5] AS May,
[4] AS Apr,
[3] AS Mar,
[2] AS Feb,
[1] AS Jan
FROM 
(SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Month
FROM         dbo.View_VK_ORDERS_SUMARY_F
WHERE     (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor, Year, Month) S
PIVOT (SUM(Quantity) FOR Month IN ([12],[11],[10],[9],,[7],[6],[5],[4],[3],[2],[1])) P
ORDER BY ItemCode ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-01-16 : 11:38:55

Hi Visakh16, I made the changes that you recommend and works but the problem is that I just have one column per year and 12 columns with the months but one month can be from another year:
[2013] [dec][nov][oct][sep][aug][jul][jun][may][apr][feb][jan]

I don't know If it can be this way because one mounth can be from 2014:

[dec13][nov13][oct13][sep13][aug13][jul13]....[jan14]

Thanks, I really appreciate your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 14:01:24

quote:
Originally posted by mjimenezh

Hi Visakh16, I made the changes that you recommend and works but the problem is that I just have one column per year and 12 columns with the months but one month can be from another year:
[2013] [dec][nov][oct][sep][aug][jul][jun][may][apr][feb][jan]

I don't know If it can be this way because one mounth can be from 2014:

[dec13][nov13][oct13][sep13][aug13][jul13]....[jan14]

Thanks, I really appreciate your help

then just take year field out from select as well as group by


SELECT ItemCode, ItemDescription, DebCode, Debtor, 
[12] AS Dec,
[11] AS Nov,
[10] AS Oct,
[9] AS Sep,
 AS Aug,
[7] AS Jul,
[6] AS Jun,
[5] AS May,
[4] AS Apr,
[3] AS Mar,
[2] AS Feb,
[1] AS Jan
FROM 
(SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Month
FROM         dbo.View_VK_ORDERS_SUMARY_F
WHERE     (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor,  Month) S
PIVOT (SUM(Quantity) FOR Month IN ([12],[11],[10],[9],,[7],[6],[5],[4],[3],[2],[1])) P
ORDER BY ItemCode ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-01-17 : 11:26:48

But I need to know the year of the month how can I do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-18 : 02:14:21

in that case try like this

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ee67702a-97cc-4a9f-9aba-41f163a518b4/count-rows-with-sum-range?forum=sqlgetstarted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-18 : 02:16:41

in that case try like this


SELECT ItemCode, ItemDescription, DebCode, Debtor, 
[201312] AS [Dec13],
[201311] AS [Nov13],
[201310] AS [Oct13],
[201309] AS [Sep13],
[201308] AS [Aug13],
[201307] AS [Jul13],
[201306] AS [Jun13],
[201305] AS [May13],
[201304] AS [Apr13],
[201303] AS [Mar13],
[201302] AS [Feb13],
[201301] AS [Jan13],
[201401[  AS [Jan14]
FROM 
(SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Year * 100 +Month
FROM         dbo.View_VK_ORDERS_SUMARY_F
WHERE     (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor,  Year * 100 +Month) S
PIVOT (SUM(Quantity) FOR Month IN ([201312],[201311],[201310],[201309],[201308],[201307],[201306],[201305],[201304],[201303],[201302],[201301],[201401])) P
ORDER BY ItemCode ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2014-01-27 : 12:35:18

Hi Visakh16, I followed your suggestions but I'm receiving an error :
"No column name was specified for column 6 of S' Invalid Column Name Month"

SELECT ItemCode, ItemDescription, DebCode, Debtor, 
[201312] AS [Dec13],
[201311] AS [Nov13],
[201310] AS [Oct13],
[201309] AS [Sep13],
[201308] AS [Aug13],
[201307] AS [Jul13],
[201306] AS [Jun13],
[201305] AS [May13],
[201304] AS [Apr13],
[201303] AS [Mar13],
[201302] AS [Feb13],
[201301] AS [Jan13],
[201401] AS [Jan14]
FROM 
(SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Year * 100 +Month
FROM         dbo.View_VK_ORDERS_SUMARY_F
WHERE     (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor,  Year * 100 +Month) S
PIVOT (SUM(Quantity) FOR Month IN ([201312],[201311],[201310],[201309],[201308],[201307],[201306],[201305],[201304],[201303],[201302],[201301],[201401])) P
ORDER BY ItemCode ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-27 : 14:00:21

[code]
SELECT ItemCode, ItemDescription, DebCode, Debtor,
[201312] AS [Dec13],
[201311] AS [Nov13],
[201310] AS [Oct13],
[201309] AS [Sep13],
[201308] AS [Aug13],
[201307] AS [Jul13],
[201306] AS [Jun13],
[201305] AS [May13],
[201304] AS [Apr13],
[201303] AS [Mar13],
[201302] AS [Feb13],
[201301] AS [Jan13],
[201401] AS [Jan14]
FROM
(SELECT DebCode,Debtor,ItemCode,ItemDescription,SUM(Sold) AS Quantity,Year * 100 +Month AS [Month]
FROM dbo.View_VK_ORDERS_SUMARY_F
WHERE (DebCode = 10559)
GROUP BY ItemCode, ItemDescription, DebCode, Debtor, Year * 100 +Month) S
PIVOT (SUM(Quantity) FOR Month IN ([201312],[201311],[201310],[201309],[201308],[201307],[201306],[201305],[201304],[201303],[201302],[201301],[201401])) P
ORDER BY ItemCode ASC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources