August 4, 2003 at 1:00 pm
Am trying a complex stored procedure where I have to calculate the sales for every month based for each product
for example OUT PUT should be
SALES BY MONTHS
Product Jan Feb Mar ..... Dec
XYZ 100 120 130 .....
WAX 120 100 150 .....
ABC 100 150 120 .....
My table structure is
SALESTABLE - NAME OF THE TABLE
ID AutoNumber
productCode VarChar(3)
OrderDateTime DateTime
QuantitySold Number
How do i create a stored procedure that will generate the above output. Am using SQL SERVER 2000.
Please send in ur suggestions and Stored Procedures to my email ndsheik@sahada.biz
Thanks in Advance
Nazeem
August 4, 2003 at 2:53 pm
There are numerous way to do this. The way I prefer is throught the use of derived tables.
CREATE PROCEDURE p_Get_YearlySalesByMonth (
@iYear int)
AS
SET NOCOUNT ON
SELECT ProductCode AS Product,
ISNULL(JanNum, 0) AS Jan,
ISNULL(FebNum, 0) AS Feb,
ISNULL(MarNum, 0) AS Mar,
ISNULL(AprNum, 0) AS Apr,
ISNULL(MayNum, 0) AS May,
ISNULL(JunNum, 0) AS Jun,
ISNULL(JulNum, 0) AS Jul,
ISNULL(AugNum, 0) AS Aug,
ISNULL(SepNum, 0) AS Sep,
ISNULL(OctNum, 0) AS Oct,
ISNULL(NovNum, 0) AS Nov,
ISNULL(DecNum, 0) AS Dec
FROM SalesTable LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS JanNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 1 GROUP BY ProductCode) a ON SalesTable.ProductCode = a.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS FebNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 2 GROUP BY ProductCode) b ON SalesTable.ProductCode = b.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS MarNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 3 GROUP BY ProductCode) c ON SalesTable.ProductCode = c.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS AprNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 4 GROUP BY ProductCode) d ON SalesTable.ProductCode = d.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS MayNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 5 GROUP BY ProductCode) e ON SalesTable.ProductCode = e.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS JunNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 6 GROUP BY ProductCode) f ON SalesTable.ProductCode = f.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS JulNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 7 GROUP BY ProductCode) g ON SalesTable.ProductCode = g.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS AugNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 8 GROUP BY ProductCode) h ON SalesTable.ProductCode = h.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS SepNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 9 GROUP BY ProductCode) i ON SalesTable.ProductCode = i.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS OctNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 10 GROUP BY ProductCode) j ON SalesTable.ProductCode = j.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS NovNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 11 GROUP BY ProductCode) k ON SalesTable.ProductCode = k.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS DecNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 12 GROUP BY ProductCode) l ON SalesTable.ProductCode = l.ProductCode
ORDER BY ProductCode
August 6, 2003 at 4:11 am
What I understand is u want a crosstab query.
If yes then the following query accomplishes it.
SELECT ST.productCode,
SUM(CASE WHEN (MONTH(OrderDateTime)) = 1 THEN ST.QuantitySold ELSE 0 END) AS [Jan],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 2 THEN ST.QuantitySold ELSE 0 END) AS [Feb],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 3 THEN ST.QuantitySold ELSE 0 END) AS [Mar],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 4 THEN ST.QuantitySold ELSE 0 END) AS [Apr],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 5 THEN ST.QuantitySold ELSE 0 END) AS [May],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 6 THEN ST.QuantitySold ELSE 0 END) AS [Jun],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 7 THEN ST.QuantitySold ELSE 0 END) AS [Jul],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 8 THEN ST.QuantitySold ELSE 0 END) AS [Aug],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 9 THEN ST.QuantitySold ELSE 0 END) AS [Sep],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 10 THEN ST.QuantitySold ELSE 0 END) AS [Oct],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 11 THEN ST.QuantitySold ELSE 0 END) AS [Nov],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 12 THEN ST.QuantitySold ELSE 0 END) AS [Dec]
FROM SALESTABLE ST
GROUP BY productCode
HTH,
Regards.
Edited by - kashif on 08/06/2003 04:13:16 AM
August 6, 2003 at 10:26 am
Hi
Thanks for the response It was really help full I used the same logic as u suggested and it worked great.
I really appriciate your help
Thanks Once again
Nazeem Sheik
quote:
What I understand is u want a crosstab query.If yes then the following query accomplishes it.
SELECT ST.productCode,
SUM(CASE WHEN (MONTH(OrderDateTime)) = 1 THEN ST.QuantitySold ELSE 0 END) AS [Jan],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 2 THEN ST.QuantitySold ELSE 0 END) AS [Feb],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 3 THEN ST.QuantitySold ELSE 0 END) AS [Mar],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 4 THEN ST.QuantitySold ELSE 0 END) AS [Apr],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 5 THEN ST.QuantitySold ELSE 0 END) AS [May],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 6 THEN ST.QuantitySold ELSE 0 END) AS [Jun],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 7 THEN ST.QuantitySold ELSE 0 END) AS [Jul],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 8 THEN ST.QuantitySold ELSE 0 END) AS [Aug],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 9 THEN ST.QuantitySold ELSE 0 END) AS [Sep],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 10 THEN ST.QuantitySold ELSE 0 END) AS [Oct],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 11 THEN ST.QuantitySold ELSE 0 END) AS [Nov],
SUM(CASE WHEN (MONTH(OrderDateTime)) = 12 THEN ST.QuantitySold ELSE 0 END) AS [Dec]
FROM SALESTABLE ST
GROUP BY productCode
HTH,
Regards.
Edited by - kashif on 08/06/2003 04:13:16 AM
August 6, 2003 at 10:28 am
Hi
Thaks for ur response and support I used ur logic to get the date for the rolling year which u emailed me it worked really great
I really appriciate your timing help
Thanks once again
Nazeem Sheik
quote:
There are numerous way to do this. The way I prefer is throught the use of derived tables.CREATE PROCEDURE p_Get_YearlySalesByMonth (
@iYear int)
AS
SET NOCOUNT ON
SELECT ProductCode AS Product,
ISNULL(JanNum, 0) AS Jan,
ISNULL(FebNum, 0) AS Feb,
ISNULL(MarNum, 0) AS Mar,
ISNULL(AprNum, 0) AS Apr,
ISNULL(MayNum, 0) AS May,
ISNULL(JunNum, 0) AS Jun,
ISNULL(JulNum, 0) AS Jul,
ISNULL(AugNum, 0) AS Aug,
ISNULL(SepNum, 0) AS Sep,
ISNULL(OctNum, 0) AS Oct,
ISNULL(NovNum, 0) AS Nov,
ISNULL(DecNum, 0) AS Dec
FROM SalesTable LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS JanNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 1 GROUP BY ProductCode) a ON SalesTable.ProductCode = a.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS FebNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 2 GROUP BY ProductCode) b ON SalesTable.ProductCode = b.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS MarNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 3 GROUP BY ProductCode) c ON SalesTable.ProductCode = c.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS AprNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 4 GROUP BY ProductCode) d ON SalesTable.ProductCode = d.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS MayNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 5 GROUP BY ProductCode) e ON SalesTable.ProductCode = e.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS JunNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 6 GROUP BY ProductCode) f ON SalesTable.ProductCode = f.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS JulNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 7 GROUP BY ProductCode) g ON SalesTable.ProductCode = g.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS AugNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 8 GROUP BY ProductCode) h ON SalesTable.ProductCode = h.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS SepNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 9 GROUP BY ProductCode) i ON SalesTable.ProductCode = i.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS OctNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 10 GROUP BY ProductCode) j ON SalesTable.ProductCode = j.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS NovNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 11 GROUP BY ProductCode) k ON SalesTable.ProductCode = k.ProductCode
LEFT JOIN (
SELECT ProductCode, SUM(QuantitySold) AS DecNum FROM SalesTable WHERE Year(OrderDateTime) = @iYear AND Month(OrderDateTime = 12 GROUP BY ProductCode) l ON SalesTable.ProductCode = l.ProductCode
ORDER BY ProductCode
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply