July 11, 2012 at 7:40 am
Hi All
I have the following query, which generates publishing run times and cross tabs them into a pivoted view, all good there, the only thing I am strugling with is getting the column names in the case statements to be dynamic to pull in the DATENAME(Weekday,GETDATE()-7) etc.
Typically the report is automatically run on a Sunday, so hardcoding them like they already are in the report works fine, but there is a requirement to run this to order, so hardcoded column names will need changing as it could be run on a Wednesday which means column "Thursday" is actually Sunday.
Any assistance would be appreciated
WITH CTE AS
(
SELECT
RE.DISPLAYTITLE,
CONVERT(DATE,PPS.Start_Date) AS DateStamp,
DATEDIFF(MINUTE,PPS.Start_Date,PPS.End_Date) AS TimeTaken
FROM
percussion_prod.dbo.PSX_PUBLICATION_STATUS PPS
INNER JOIN
percussion_prod.dbo.RXEDITION RE
ON
PPS.EDITION_ID = RE.EDITIONID
WHERE
CONVERT(DATE,Start_Date) >= CONVERT(DATE,GETDATE()-7)
AND
CONVERT(DATE,END_DATE) <= CONVERT(DATE,GETDATE())
AND
LEFT(DISPLAYTITLE,2) = 'pp'
)
SELECT
DisplayTitle,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-7) THEN TimeTaken ELSE 0 END) AS Sunday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-6) THEN TimeTaken ELSE 0 END) AS Monday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-5) THEN TimeTaken ELSE 0 END) AS Tuesday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-4) THEN TimeTaken ELSE 0 END) AS Wednesday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-3) THEN TimeTaken ELSE 0 END) AS Thursday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-2) THEN TimeTaken ELSE 0 END) AS Friday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-1) THEN TimeTaken ELSE 0 END) AS Saturday
FROM
CTE
GROUP BY
DisplayTitle
July 11, 2012 at 8:21 am
You can declare a variable, say @today to hold the value for DATEPART(WEEKDAY, GETDATE()) and modify the code as below
DECLARE@today TINYINT
SET @today = DATEPART(WEEKDAY, GETDATE())
WITH CTE AS
(
SELECT
RE.DISPLAYTITLE,
CONVERT(DATE,PPS.Start_Date) AS DateStamp,
DATEDIFF(MINUTE,PPS.Start_Date,PPS.End_Date) AS TimeTaken
FROM
percussion_prod.dbo.PSX_PUBLICATION_STATUS PPS
INNER JOIN
percussion_prod.dbo.RXEDITION RE
ON
PPS.EDITION_ID = RE.EDITIONID
WHERE
CONVERT(DATE,Start_Date) >= CONVERT(DATE,GETDATE()-7)
AND
CONVERT(DATE,END_DATE) <= CONVERT(DATE,GETDATE())
AND
LEFT(DISPLAYTITLE,2) = 'pp'
)
SELECT
DisplayTitle,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-7 + @today) THEN TimeTaken ELSE 0 END) AS Sunday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-6 + @today) THEN TimeTaken ELSE 0 END) AS Monday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-5 + @today) THEN TimeTaken ELSE 0 END) AS Tuesday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-4 + @today) THEN TimeTaken ELSE 0 END) AS Wednesday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-3 + @today) THEN TimeTaken ELSE 0 END) AS Thursday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-2 + @today) THEN TimeTaken ELSE 0 END) AS Friday,
SUM(CASE WHEN DateStamp = CONVERT(DATE,GETDATE()-1 + @today) THEN TimeTaken ELSE 0 END) AS Saturday
FROM
CTE
GROUP BY
DisplayTitle
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 11, 2012 at 8:21 am
instead of dynamically assigning column names, statically figure out the day of the week with datepart.
SELECT
SUM(CASE WHEN DATEPART(weekday,datestamp) = 1 THEN TimeTaken ELSE 0 END) AS Sunday,
SUM(CASE WHEN DATEPART(weekday,datestamp) = 2 THEN TimeTaken ELSE 0 END) AS Monday,
SUM(CASE WHEN DATEPART(weekday,datestamp) = 3 THEN TimeTaken ELSE 0 END) AS Tuesday,
SUM(CASE WHEN DATEPART(weekday,datestamp) = 4 THEN TimeTaken ELSE 0 END) AS Wednesday,
SUM(CASE WHEN DATEPART(weekday,datestamp) = 5 THEN TimeTaken ELSE 0 END) AS Thursday,
SUM(CASE WHEN DATEPART(weekday,datestamp) = 6 THEN TimeTaken ELSE 0 END) AS Friday,
SUM(CASE WHEN DATEPART(weekday,datestamp) = 7 THEN TimeTaken ELSE 0 END) AS Saturday
FROM
cte
WHERE datestamp> GETDATE()-7something like this
July 11, 2012 at 8:38 am
Thanks Gents, I didnt explain the situation clearly enough, by re-reading the originial post.
The idea is to show the earliest date on the left and the latest date on the right, so having it static with Sunday far left and Saturday far right doesnt fit the spec.
So if report ran today, the column order should be Wednesday (2012-07-04), Thursday (2012-07-05), Friday (2012-07-06), Saturday (2012-07-07), Sunday (2012-07-08), Monday (2012-07-09), Tuesday (2012-07-10)
After a bit more digging and I am gussing better search terms in Google I came accross the soltution below which gives the expected outcome.
DECLARE @SD DATE = GETDATE()-14, @ED DATE = GETDATE()
SELECT DISTINCT convert(varchar(10), Start_Date, 120) AS [Date]
INTO #Dates
FROM [percussion_prod].[dbo].[PSX_PUBLICATION_STATUS]
WHERE CONVERT(DATE,START_DATE) >= @SD
AND CONVERT(DATE,END_DATE) < @ED
ORDER BY [Date]
-- Building a comma separated list of Dates in #Dates
DECLARE @cols varchar(1000)
SELECT @cols = COALESCE (@cols + ', [' + [Date] +']',
'[' + [Date] + ']'
)
FROM #Dates
-- Building the query appending columns
DECLARE @qry varchar(4000)
SET @qry =
'SELECT DisplayTitle, '
+ @cols
+ ' FROM
(
SELECT
RE.DISPLAYTITLE,
CONVERT(varchar(10),PPS.Start_Date,120) AS Datestamp,
DATEDIFF(MINUTE,PPS.Start_Date,PPS.End_Date) AS TimeTaken
FROM
[percussion_prod].[dbo].[PSX_PUBLICATION_STATUS] PPS
INNER JOIN
[percussion_prod].[dbo].[RXEDITION] RE
ON
PPS.EDITION_ID = RE.EDITIONID
WHERE
CONVERT(DATE,Start_Date) >= ' + CHAR(39)+CONVERT(VARCHAR(10),@SD,120) + CHAR(39) + '
AND
CONVERT(DATE,END_DATE) < '+ CHAR(39) + CONVERT(VARCHAR(10),@ED,120) + CHAR(39) + '
AND
LEFT(DISPLAYTITLE,2) = ''pp''
) AS p
PIVOT
(
SUM(TimeTaken) FOR
DateStamp IN (' + @cols + ')
)
AS pvt;'
select @qry
-- Executing the query
EXEC(@qry)
-- Dropping temporary table
DROP TABLE #Dates
This was my initial way of doing it in a pivot command instead of CASE cross tabs but couldnt get the pivoting right for the dates, but that solution gives me what I need.
July 11, 2012 at 12:48 pm
Hi Anthony-
I'm not very familiar with other reporting systems but with SSRS you're able to use a Matrix which will pivot the data for you. What I'm imaging is changing your dataset to something like this:
Date Time Taken
2012-07-01 14:01
2012-07-02 8:41
2012-07-03 21:19
Basically, for each "column" of the report, it's a different row. Then when you have your Matrix, you'll pivot on Date (and you can even sort this ascending) and then just display the Time Taken, so you're end up with something like this:
2012-07-01 2012-07-02 2012-07-03
14:01 8:41 21:19
Unfortunately if you aren't using SSRS, this will not be of much use to you :crying:
July 12, 2012 at 12:53 am
Thanks Mike, this is sent via sp_send_dbmail as we use a 3rd party for hosting and we dont have SSRS installed on the 3rd party environment yet, but it is coming, but not quick enough.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply