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
 Stored Procedure

Author  Topic 

Vack
Aged Yak Warrior

530 Posts
Posted - 2009-05-15 : 15:14:34
Currently the following displays data as:

Item_no YearVal Jan Feb March.....
ABC 2009 333 233 444
ABC 2008 323 345 543

ALTER PROCEDURE [dbo].[UsageCurrentYear]
AS
SELECT item_no,Year(doc_dt) as YearVal,
SUM(CASE WHEN DATEPART(mm,doc_dt)=1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS January,
SUM(CASE WHEN DATEPART(mm,doc_dt)=2 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Feb,
SUM(CASE WHEN DATEPART(mm,doc_dt)=3 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS March,
SUM(CASE WHEN DATEPART(mm,doc_dt)=4 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS April,
SUM(CASE WHEN DATEPART(mm,doc_dt)=5 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS May,
SUM(CASE WHEN DATEPART(mm,doc_dt)=6 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS June,
SUM(CASE WHEN DATEPART(mm,doc_dt)=7 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS July,
SUM(CASE WHEN DATEPART(mm,doc_dt)=8 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Aug,
SUM(CASE WHEN DATEPART(mm,doc_dt)=9 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Sept,
SUM(CASE WHEN DATEPART(mm,doc_dt)=10 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Oct,
SUM(CASE WHEN DATEPART(mm,doc_dt)=11 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Nov,
SUM(CASE WHEN DATEPART(mm,doc_dt)=12 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS Dec
--SUM(CASE WHEN DATEPART(mm,doc_dt)=1 THEN quantity ELSE 0 END) AS January,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=2 THEN quantity ELSE 0 END) AS February,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=3 THEN quantity ELSE 0 END) AS March,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=4 THEN quantity ELSE 0 END) AS April,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=5 THEN quantity ELSE 0 END) AS May,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=6 THEN quantity ELSE 0 END) AS June,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=7 THEN quantity ELSE 0 END) AS July,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=8 THEN quantity ELSE 0 END) AS August,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=9 THEN quantity ELSE 0 END) AS September,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=10 THEN quantity ELSE 0 END) AS October,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=11 THEN quantity ELSE 0 END) AS November,
--SUM(CASE WHEN DATEPART(mm,doc_dt)=12 THEN quantity ELSE 0 END) AS December
from iminvtrx_sql
where doc_type = 'I' or (source = 'O' and doc_type = 'R') and
doc_dt>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND doc_dt< DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
group by item_no, year(doc_dt)
order by item_no


Is there any way to change it so it puts all the data into one record?

Item_no Jancurrent FebcurrentMarchcurrent...JanLastYear FebLastYear..
ABC 333 233 444
ABC 323 345 543

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-05-16 : 02:59:56
[code]
ALTER PROCEDURE [dbo].[UsageCurrentYear]
AS
SELECT item_no,
SUM(CASE WHEN DATEPART(mm,doc_dt)=1 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS JanuaryCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=2 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS FebCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=3 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS MarchCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=4 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS AprilCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=5 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS MayCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=6 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS JuneCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=7 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS JulyCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=8 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS AugCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=9 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS SeptCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=10 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS OctCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=11 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS NovCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=12 AND year(doc_dt) = year(GETDATE()) THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS DecCurrent,
SUM(CASE WHEN DATEPART(mm,doc_dt)=1 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS JanuaryLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=2 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS FebLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=3 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS MarchLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=4 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS AprilLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=5 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS MayLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=6 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS JuneLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=7 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS JulyLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=8 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS AugLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=9 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS SeptLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=10 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS OctLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=11 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS NovLastYear,
SUM(CASE WHEN DATEPART(mm,doc_dt)=12 AND year(doc_dt) = year(GETDATE())-1 THEN CASE WHEN doc_type = 'I' THEN quantity WHEN doc_type = 'R' THEN -1 * quantity END ELSE 0 END) AS DecLastYear
from iminvtrx_sql
where doc_type = 'I' or (source = 'O' and doc_type = 'R') and
doc_dt>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND doc_dt< DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
group by item_no
order by item_no
[/code]
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site