Dynamic Horizontal Date Table - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Horizontal Date Table

Author

Topic

blued
Starting Member

9 Posts

Posted - 2010-09-16 : 05:27:56

Hi all,

I'm new to the forum and hope you can help me out. I'm trying to write a report in SSRS and am having some problems with the stored procedure.

The user needs to put in a date and then have a list of locations with a count of incidents at each one from 12 months ago up until the month the date falls in. So if they entered 16/09/2010

Location - 10/09 - 11/09 - 12/09 - 01/10 - 02/10 .... 09/10

Locat.1 - 13 - 12 - 5 - 0 - 9 .... 7
Locat.2 - 4 - 11 - 33 - 6 - 3 .... 3
Locat.3 - 15 - 9 - 4 - 0 - 1 .... 3

So far I have the following but cant work out how to make it dynamic

SELECT
FacilityCode,
SUM(CASE WHEN DatePart(day,DateOccurred) = 1 THEN 1 ELSE 0 END) AS 'Month 1',
SUM(CASE WHEN DatePart(day,DateOccurred) = 2 THEN 1 ELSE 0 END) AS 'Month 2',
SUM(CASE WHEN DatePart(day,DateOccurred) = 3 THEN 1 ELSE 0 END) AS 'Month 3',
SUM(CASE WHEN DatePart(day,DateOccurred) = 4 THEN 1 ELSE 0 END) AS 'Month 4',
SUM(CASE WHEN DatePart(day,DateOccurred) = 5 THEN 1 ELSE 0 END) AS 'Month 5',
SUM(CASE WHEN DatePart(day,DateOccurred) = 6 THEN 1 ELSE 0 END) AS 'Month 6',
SUM(CASE WHEN DatePart(day,DateOccurred) = 7 THEN 1 ELSE 0 END) AS 'Month 7',
SUM(CASE WHEN DatePart(day,DateOccurred) = 8 THEN 1 ELSE 0 END) AS 'Month 8',
SUM(CASE WHEN DatePart(day,DateOccurred) = 9 THEN 1 ELSE 0 END) AS 'Month 9',
SUM(CASE WHEN DatePart(day,DateOccurred) = 10 THEN 1 ELSE 0 END) AS 'Month 10',
SUM(CASE WHEN DatePart(day,DateOccurred) = 11 THEN 1 ELSE 0 END) AS 'Month 11',
SUM(CASE WHEN DatePart(day,DateOccurred) = 12 THEN 1 ELSE 0 END) AS 'Month 12'
FROM
dbo.CG_AI_Details
GROUP BY
FacilityCode
ORDER BY
FacilityCode

Any help appreciated.

Sachin.Nand

2937 Posts

Posted - 2010-09-16 : 05:35:16

SUM(CASE WHEN DatePart(day,DateOccurred) = 1 THEN 1 ELSE 0 END) AS 'Month 1'

Should this not be DatePart(MM,DateOccurred) ???

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

blued
Starting Member

9 Posts

Posted - 2010-09-16 : 05:47:05

Sorry yes, I posted the wrong one in.

SELECT
FacilityCode,
SUM(CASE WHEN DatePart(MM,DateOccurred) = 1 THEN 1 ELSE 0 END) AS 'Month 1',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 2 THEN 1 ELSE 0 END) AS 'Month 2',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 3 THEN 1 ELSE 0 END) AS 'Month 3',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 4 THEN 1 ELSE 0 END) AS 'Month 4',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 5 THEN 1 ELSE 0 END) AS 'Month 5',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 6 THEN 1 ELSE 0 END) AS 'Month 6',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 7 THEN 1 ELSE 0 END) AS 'Month 7',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 8 THEN 1 ELSE 0 END) AS 'Month 8',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 9 THEN 1 ELSE 0 END) AS 'Month 9',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 10 THEN 1 ELSE 0 END) AS 'Month 10',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 11 THEN 1 ELSE 0 END) AS 'Month 11',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 12 THEN 1 ELSE 0 END) AS 'Month 12'
FROM
dbo.CG_AI_Details
GROUP BY
FacilityCode
ORDER BY
FacilityCode

Sachin.Nand

2937 Posts

Posted - 2010-09-16 : 06:22:27

I dont think there is a need for dynamic SQL as long as there are fixed 12 months in a year.Try this



SELECT 
FacilityCode,
SUM(CASE WHEN DatePart(MM,DateOccurred) = 1 THEN 1 ELSE 0 END) AS 'Month 1',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 2 THEN 1 ELSE 0 END) AS 'Month 2',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 3 THEN 1 ELSE 0 END) AS 'Month 3',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 4 THEN 1 ELSE 0 END) AS 'Month 4',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 5 THEN 1 ELSE 0 END) AS 'Month 5',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 6 THEN 1 ELSE 0 END) AS 'Month 6',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 7 THEN 1 ELSE 0 END) AS 'Month 7',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 8 THEN 1 ELSE 0 END) AS 'Month 8',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 9 THEN 1 ELSE 0 END) AS 'Month 9',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 10 THEN 1 ELSE 0 END) AS 'Month 10',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 11 THEN 1 ELSE 0 END) AS 'Month 11',
SUM(CASE WHEN DatePart(MM,DateOccurred) = 12 THEN 1 ELSE 0 END) AS 'Month 12' 
FROM

(

	select all the records from dbo.CG_AI_Details for the last one year

)T 

GROUP BY FacilityCode
ORDER BY FacilityCode

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

blued
Starting Member

9 Posts

Posted - 2010-09-16 : 07:08:54

I'm not sure I can achieve the output I need like this?

I need some way to put the months in the header with the current month being furthest to the right.

08/2009 | 09/2009 | 10/2009....07/2010 | 08/2010 | 09/2010

With your example I think the position of the months would be fixed? Also in the example I posted originally I havent taken into consideration the years because it will group month 9 for example for both 2009 and 2010 data.

blued
Starting Member

9 Posts

Posted - 2010-09-21 : 04:04:37

I'm still stuck with this, does anyone have any other ideas?

blued
Starting Member

9 Posts

Posted - 2010-09-21 : 06:33:14

ok this is what I've come up with...

CREATE TABLE #Temp (Yr int, Period int, Total int)

INSERT INTO #Temp

SELECT
CAST(year(DateOccurred) as varchar) Yr,
CAST(month(DateOccurred) as varchar) Period,
COUNT(*) Total
FROM
CG_AI_Details
WHERE
DateOccurred BETWEEN DateAdd(MM,-11,GetDate()) AND GetDate()
GROUP BY
CAST(year(DateOccurred) as varchar),
CAST(month(DateOccurred) as varchar)
UNION
SELECT DISTINCT
Yr,
Period,
0
FROM
CG_Dates
WHERE
Date BETWEEN DateAdd(MM,-11,GetDate()) AND GetDate()
ORDER BY
Yr,
Period

SELECT
Yr,
Period,
SUM(Total) Total
FROM
#Temp
GROUP BY
Yr,
Period
ORDER BY
Yr,
Period

CG_Dates is a list of date, period and year and I'm using it so even months with no results will be returned. Next step is to see if I can get this in a horizontal table using a matrix in SSRS.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-21 : 08:42:00

Try this -
Following Dynamic Query looks Complicated but its not.


DECLARE @Date AS DATETIME, @SQL AS VARCHAR(MAX)
SET @Date = '2010-09-01'

SELECT @SQL = 

'SELECT 
FacilityCode,
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,''' + CONVERT(VARCHAR(30), @Date ) + ''') THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,@Date)) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date)) + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-1,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-1,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-2,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-2,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-3,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-3,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-4,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-4,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-5,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-5,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-6,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-6,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-7,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-7,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-8,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-8,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-9,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-9,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-10,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-10,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''',
SUM(CASE WHEN DatePart(MM,DateOccurred) = DATEPART(MM,DATEADD(MM,-11,''' + CONVERT(VARCHAR(30), @Date ) + ''')) THEN 1 ELSE 0 END) AS ''' + CONVERT(VARCHAR(2), DATEPART(MM,DATEADD(MM,-11,@Date))) + '/' + CONVERT(VARCHAR(4), DATEPART(YEAR,@Date))  + ''' 
FROM

(

	SELECT * FROM dbo.CG_AI_Details WHERE DateOccurred > = DATEADD(Month, -11, ''' + CONVERT(VARCHAR(30), @Date ) + ''')

)T 

GROUP BY FacilityCode
ORDER BY FacilityCode
'

EXEC ( @SQL	)

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources