Multiple Dates Query - 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

Multiple Dates Query

Author

Topic

Chamark
Starting Member

28 Posts

Posted - 2010-09-23 : 13:56:29

I need help in creating a query to get info for year selected and previous year of the year selected. Needs to be by quarter(QTR) for both years. QTR1 will always be the month 03, QTR2 month 06, QTR3 month 09, & QTR4 month 12. No computations are required or averaging.

Fields/columns are YEARMONTH stored as mm/dd/yyyy with dd always being 01, ACTUAL_MONTH(float)for all QTRs based on current YEARMONTH selected and one year prior,TARGET_MONTH(float) for all QTRs based on YEARMONTH selected, and PROJECTION_MONTH(float) for all QTRs based YEARMONTH selected. I also need to chart this by quarter in SSRS 2008. Any help is greatly apprciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:58:50

post table structure along with some sample data and then tell what output you're looking at.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chamark
Starting Member

28 Posts

Posted - 2010-09-23 : 14:24:30

Table
YEARMONTH (PK, Datetime)
ACTUAL_MONTH(Float)
TARGET_MONTH(Float)
PROJECTION_MONTH(Float)

Q1 Q2 Q3 Q4
Actualxx xxx xxx xxx xxx
Actualxx xxx xxx xxx xxx
Targetxx xxx xxx xxx xxx
Projectionxx xxx xxx xxx xxx

The user selects a YEARMONTH, so could be 07/01/2010 or 07/01/2009 so need to show numbers for that particular year & previous year(Actual_Month only)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 00:24:09

[code]
SELECT Cat,
YEAR(YEARMONTH) AS Yr,
SUM(CASE WHEN MONTH(YEARMONTH) BETWEEN 1 AND 3 THEN Val ELSE 0 END) AS Q1,
SUM(CASE WHEN MONTH(YEARMONTH) BETWEEN 4 AND 6 THEN Val ELSE 0 END) AS Q2,
SUM(CASE WHEN MONTH(YEARMONTH) BETWEEN 7 AND 9 THEN Val ELSE 0 END) AS Q3,
SUM(CASE WHEN MONTH(YEARMONTH) BETWEEN 10 AND 12 THEN Val ELSE 0 END) AS Q4
FROM
(
SELECT YEARMONTH,Cat,Val
FROM (SELECT * FROM YourTable WHERE (YEARMONTH >= DATEADD(yy,DATEDIFF(yy,0,@Date)-1,0) AND YEARMONTH < DATEADD(yy,DATEDIFF(yy,0,@Date)+1,0))
)t
UNPIVOT (Val FOR Cat IN ([ACTUAL_MONTH],[TARGET_MONTH],[PROJECTION_MONTH]))u
)t
GROUP BY Cat,
YEAR(YEARMONTH)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources