May 14, 2010 at 7:52 am
ciss1 (5/13/2010)
Please don't let my thread die a slow death.If there is some additional information I need to provide, please let me know.
Thanks
The reason no one has responded is that your post is a little confusing.
It took me quite a while just to figure out what your data meant and what you were trying to accomplish.
Here is a first shot. It is not final, but I wanted to know if it's near what you are looking for before moving forward.
-- first calculate the yearly amounts
;WITH YearToDate AS
(
SELECT SUM([NumFoodSvcInsPerformed]) AS [NumFoodSvcInsPerformed]
, SUM([NumRoutineFoodSvcInsPerformed]) AS [NumRoutineFoodSvcInsPerformed]
, SUM([NumFollowupFoodSvcInsPerformed]) AS [NumFollowupFoodSvcInsPerformed]
, SUM([NumCompliantFoodSvcInsPerformed]) AS [NumCompliantFoodSvcInsPerformed]
, SUM([NumTouristAccomInspections]) AS [NumTouristAccomInspections]
, SUM([NumSolidWasteInsPerformed]) AS [NumSolidWasteInsPerformed]
, SUM([NumFacilitiesClosed])AS [NumFacilitiesClosed]
, CAST('12/31/2009' AS DATETIME) AS EndDate
FROM [HHSDataTest]
)
-- now combine week and year counts
-- original data is already weekly aggregates
, WeekAndYear AS
(
SELECT [NumFoodSvcInsPerformed]
, [NumRoutineFoodSvcInsPerformed]
, [NumFollowupFoodSvcInsPerformed]
, [NumCompliantFoodSvcInsPerformed]
, [NumTouristAccomInspections]
, [NumSolidWasteInsPerformed]
, [NumFacilitiesClosed]
, EndDate
FROM [HHSDataTest]
UNION ALL
SELECT [NumFoodSvcInsPerformed]
, [NumRoutineFoodSvcInsPerformed]
, [NumFollowupFoodSvcInsPerformed]
, [NumCompliantFoodSvcInsPerformed]
, [NumTouristAccomInspections]
, [NumSolidWasteInsPerformed]
, [NumFacilitiesClosed]
, EndDate
FROM YearToDate
)
-- now unpivot our data
-- to get the column headers as data rows
, UnpivotedData AS
(
SELECT TotalNum
, IncidentType
, EndDate
FROM WeekAndYear
UNPIVOT
(
TotalNum
FOR IncidentType IN ([NumFoodSvcInsPerformed]
,[NumRoutineFoodSvcInsPerformed]
,[NumFollowupFoodSvcInsPerformed]
,[NumCompliantFoodSvcInsPerformed]
,[NumTouristAccomInspections]
,[NumSolidWasteInsPerformed]
,[NumFacilitiesClosed])
) AS P
)
-- now pivot the data
-- to get end date as a column
SELECT *
FROM UnpivotedData
PIVOT
(
MAX(TotalNum)
FOR EndDate IN ([2009-06-26],[2009-07-03],[2009-07-11],[2009-07-17],[2009-12-31])
) AS P
May 14, 2010 at 9:02 am
Unfortunately I don't have the time to work this today, but shouldn't this be doable with simple CASE statements in a single pass? Hopefully someone else can work that angle...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 14, 2010 at 9:17 am
TheSQLGuru (5/14/2010)
Unfortunately I don't have the time to work this today, but shouldn't this be doable with simple CASE statements in a single pass? Hopefully someone else can work that angle...
I agree, it looks like a perfect job for CrossTab (as mentioned by Jeff in the very first reply...). But I also think what the OP really need is a DynamicCrossTab solution since I don't think having hard-coded date values is not really an option.
What I'm still waiting for is to see some effort the OP put into the subject by following the concept Jeff mentioned. I'd be more than willing to help to get it working and to change it into dynamic SQL, but...
May 14, 2010 at 10:08 am
http://www.sommarskog.se/pivot_sp.sp could help. Erland has some really good stuff on his website.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 14, 2010 at 10:10 am
TheSQLGuru (5/14/2010)
http://www.sommarskog.se/pivot_sp.sp could help. Erland has some really good stuff on his website.
I could make the pivot dynamic, but first I want to make sure the data is what the OP wants.
May 14, 2010 at 12:17 pm
Mine is similar to Goldie's. Main difference is that the IncidentType column has the desired text (instead of the column name), and it is sorted according to the sort order in the first table. I actually coded all of this before seeing that Goldie and gone this approach (just shows that I need to read all of the post!), but it was a very good learning exercise for me. I'm going to leave it to the OP to make this dynamic - I indicated the areas that need to be modified. I'd suggest dumping the pivoted results to a temp table, then making that final part to select from that dynamic.
-- Add some missing test data!
Insert Into dbo.HumanServicesTest (IncidentType, SortOrder)
SELECT '# of food service inspections performed', 15
-- Build the CTEs to hold your unpivoted data.
-- WeeklyReport has been modified to also get the EndDate,
-- and to convert the [Reporting Week] to an integer
-- so that a pivot can sum it up.
;With WeeklyReport (IncidentType, EndDate, [Reporting Week])
As
(
Select IncidentType, EndDate, [ReportingWeek] = convert(int, [Reporting Week])
From
(
Select EndDate,
CONVERT(NVarChar(150), NumFoodSvcInsPerformed) As [# of food service inspections performed],
CONVERT(NVarChar(150), NumRoutineFoodSvcInsPerformed) As [# of routine food service inspections performed],
CONVERT(NVarChar(150), NumFollowupFoodSvcInsPerformed) As [# of follow-up food service inspections performed],
CONVERT(NVarChar(150), NumCompliantFoodSvcInsPerformed) As [# of complaint food service inspections performed],
CONVERT(NVarChar(150), NumTouristAccomInspections) As [# of tourist accommodation inspections],
CONVERT(NVarChar(150), NumSolidWasteInsPerformed) As [# of solid waste inspections performed],
CONVERT(NVarChar(150), NumFacilitiesClosed) As [# of facilities closed]
From HHSDataTest
--Where (startDate = @StartDate and EndDate = @EndDate)
) As HHSData
UnPivot
(
[Reporting Week] For IncidentType In ([# of food service inspections performed],
[# of routine food service inspections performed],
[# of follow-up food service inspections performed],
[# of complaint food service inspections performed],
[# of tourist accommodation inspections],
[# of solid waste inspections performed],
[# of facilities closed])
) As HHSDataUnpivot
),
-- YtdReport has not been modified
YtdReport (IncidentType, YTD)
As
(
Select IncidentType, [Reporting Week]
From
(
Select CONVERT(NVarChar(150), SUM(NumFoodSvcInsPerformed)) As [# of food service inspections performed],
CONVERT(NVarChar(150), SUM(NumRoutineFoodSvcInsPerformed)) As [# of routine food service inspections performed],
CONVERT(NVarChar(150), SUM(NumFollowupFoodSvcInsPerformed)) As [# of follow-up food service inspections performed],
CONVERT(NVarChar(150), SUM(NumCompliantFoodSvcInsPerformed)) As [# of complaint food service inspections performed],
CONVERT(NVarChar(150), SUM(NumTouristAccomInspections)) As [# of tourist accommodation inspections],
CONVERT(NVarChar(150), SUM(NumSolidWasteInsPerformed)) As [# of solid waste inspections performed],
CONVERT(NVarChar(150), SUM(NumFacilitiesClosed)) As [# of facilities closed]
From HHSDataTest F
--Where (YEAR(StartDate) = @ReportYear) And (StartDate <= @StartDate)
) As HHSData
UnPivot
(
[Reporting Week] For IncidentType In ([# of food service inspections performed],
[# of routine food service inspections performed],
[# of follow-up food service inspections performed],
[# of complaint food service inspections performed],
[# of tourist accommodation inspections],
[# of solid waste inspections performed],
[# of facilities closed])
) As HHSDataUnpivot
)
-- pivot the data, and return it.
SELECT pvt.IncidentType,
[6/26/2009] = pvt.[20090626], -- <<<< NOTE! You will need to follow Jeff Moden's article
[7/3/2009] = pvt.[20090703], -- <<<< http://www.sqlservercentral.com/articles/Crosstab/65048/
[7/11/2009] = pvt.[20090711], -- <<<< to make this dynamic
[7/17/2009] = pvt.[20090717],
Y.YTD
FROM (SELECT IncidentType, EndDate, [Reporting Week]
FROM WeeklyReport) NeedlessAlias
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< these fields below also need to be modified to make dynamic
PIVOT (SUM([Reporting Week]) FOR EndDate IN ([20090626],[20090703],[20090711],[20090717]) ) pvt
JOIN dbo.HumanServicesTest hst
ON hst.IncidentType = pvt.IncidentType -- JOIN to this table to get the sort order
JOIN YtdReport Y
ON pvt.IncidentType = Y.IncidentType -- JOIN to this table to get the YTD (total) value
ORDER BY hst.SortOrder
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 14, 2010 at 12:52 pm
Here's an alternative way to do it:
;WITH cte AS
(
SELECT reportqid,incidenttype,sortorder,CONVERT(CHAR(10),enddate,112) AS enddate
FROM
( SELECT *
FROM HHSDataTest
) p
UNPIVOT (sortorder FOR incidenttype IN
(
[NumFoodSvcInsPerformed],
[NumRoutineFoodSvcInsPerformed],
[NumFollowupFoodSvcInsPerformed] ,
[NumCompliantFoodSvcInsPerformed] ,
[NumTouristAccomInspections],
[NumSolidWasteInsPerformed],
[NumFacilitiesClosed]
)
) AS unpvt
),
transform AS
(
SELECT '# of routine food service inspections performed' AS c1,'NumRoutineFoodSvcInsPerformed' AS c2 UNION ALL
SELECT '# of follow-up food service inspections performed','NumFollowupFoodSvcInsPerformed' UNION ALL
SELECT '# of complaint food service inspections performed','NumCompliantFoodSvcInsPerformed' UNION ALL
SELECT '# of tourist accommodation inspections','NumTouristAccomInspections' UNION ALL
SELECT '# of solid waste inspections performed','NumSolidWasteInsPerformed' UNION ALL
SELECT '# of food service inspections performed','NumFoodSvcInsPerformed' UNION ALL
SELECT '# of facilities closed','NumFacilitiesClosed'
)
SELECT
hst.IncidentType,
MAX(CASE WHEN enddate ='20090626' THEN cte.sortorder ELSE NULL END) AS [20090626],
MAX(CASE WHEN enddate ='20090703' THEN cte.sortorder ELSE NULL END) AS [20090703],
MAX(CASE WHEN enddate ='20090711' THEN cte.sortorder ELSE NULL END) AS [20090711],
MAX(CASE WHEN enddate ='20090717' THEN cte.sortorder ELSE NULL END) AS [20090717],
SUM (cte.sortorder) AS total
FROM cte
INNER JOIN transform
ON cte.incidenttype = transform.c2
INNER JOIN dbo.HumanServicesTest hst
ON hst.IncidentType = transform.c1
GROUP BY hst.IncidentType,hst.SortOrder
ORDER BY hst.SortOrder
May 14, 2010 at 4:56 pm
I want to sincerely and wholeheartedly thank you ALL for your kindness.
I really appreciate it.
I ran all 3 scripts and they appear to work real good.
I know that Goldie's is only missing the YTD total that's as a result of waiting to hear from me.
I really want to apologize for taking this long.
I traveled to Tennessee this morning - not by choice at all. So, sorry for the delay in responding.
Lutz, I didn't get much done. It wasn't like I was sitting around waiting for you great people to do it.
It was a bit stumping to know how to get started.
I am grateful for all the assitance.
I will attempt now to make to make the dates dynamic.
BTW, what is OP?
Sorry for a dumb question.
May 14, 2010 at 5:23 pm
Ok, the easy stuff first: OP = original poster = the person who started the related thread (in this case: you). That's not a dumb question at all!, since it isn't really an official abbreviation. 😉
Regarding the scripts:
Try to understand them and play with them (for example: expand Goldie's query to include the YTD values). Compare performance and execution plans to figure out which one will fit best.
Read Jeff Modens article regarding DynamicCrossTab (referenced in my signature) and try to modify your code.
If you get stuck, post here what you're struggling with including the code you have so far. I'm sure there will be quite a few people willing to help you.
May 14, 2010 at 7:17 pm
Thank you Lutz.
You are absolute ANGELS.
Since I discovered this great forum, I have not been disappointed.
I will do the best I can to show that I am interested in learning.
Thanks(AGAIN) to all of you.
May 14, 2010 at 11:01 pm
lmu92 (5/14/2010)
Ok, the easy stuff first: OP = original poster = the person who started the related thread (in this case: you). That's not a dumb question at all!, since it isn't really an official abbreviation. 😉
I can see how he'd want to know what it was... three posters referenced it!
Read Jeff Modens article regarding DynamicCrossTab (referenced in my signature) and try to modify your code.
If you get stuck, post here what you're struggling with including the code you have so far. I'm sure there will be quite a few people willing to help you.
I'll second that. Now that you know how to post data to get people to actually look at your problem, and if you show what you're trying to do, there are many people here that will step in to help you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 15, 2010 at 7:18 am
I like Lutz's answer the best since it uses the CASE statement I prefer - although not in the way I intended it. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 17, 2010 at 8:31 am
simflex-897410 (5/14/2010)
I want to sincerely and wholeheartedly thank you ALL for your kindness.
You're quite welcome!
I know that Goldie's is only missing the YTD total that's as a result of waiting to hear from me.
My script is not missing the YTD totals. They are listed under '12/31/2009' because I wasn't sure what date to put for that header
May 17, 2010 at 10:50 am
Thanks a lot again, Goldie.
I am still struggling with the dynamic date. I will continue to try to figure it out.
It isn't making sense to me. I am more of .net programmer but that's not an excuse.
May 17, 2010 at 11:12 am
Think about it this way. You know the list of dates before you start the query.
Then you can use them as columns.
You can try something like this. (no promises that it's perfect)
-- create a temp table to store our unpivoted data
-- store dates as strings because year to date values does not have an actual date
CREATE TABLE #WeekYearValues
(
TotalNumINT
,IncidentTypeVARCHAR(50)
,EndDateVARCHAR(30)
)
-- now create the data to fill the temp table
-- first calculate the yearly amounts
;WITH YearToDate AS
(
SELECT SUM([NumFoodSvcInsPerformed])AS [NumFoodSvcInsPerformed]
, SUM([NumRoutineFoodSvcInsPerformed])AS [NumRoutineFoodSvcInsPerformed]
, SUM([NumFollowupFoodSvcInsPerformed])AS [NumFollowupFoodSvcInsPerformed]
, SUM([NumCompliantFoodSvcInsPerformed])AS [NumCompliantFoodSvcInsPerformed]
, SUM([NumTouristAccomInspections])AS [NumTouristAccomInspections]
, SUM([NumSolidWasteInsPerformed])AS [NumSolidWasteInsPerformed]
, SUM([NumFacilitiesClosed])AS [NumFacilitiesClosed]
, 'YearToDate'AS EndDate
FROM [HHSDataTest]
)
-- now combine week and year counts
-- original data is already weekly aggregates
, WeekAndYear AS
(
SELECT [NumFoodSvcInsPerformed]
, [NumRoutineFoodSvcInsPerformed]
, [NumFollowupFoodSvcInsPerformed]
, [NumCompliantFoodSvcInsPerformed]
, [NumTouristAccomInspections]
, [NumSolidWasteInsPerformed]
, [NumFacilitiesClosed]
, CONVERT(VARCHAR(30), EndDate, 101) AS EndDate
FROM [HHSDataTest]
UNION ALL
SELECT [NumFoodSvcInsPerformed]
, [NumRoutineFoodSvcInsPerformed]
, [NumFollowupFoodSvcInsPerformed]
, [NumCompliantFoodSvcInsPerformed]
, [NumTouristAccomInspections]
, [NumSolidWasteInsPerformed]
, [NumFacilitiesClosed]
, EndDate
FROM YearToDate
)
-- now unpivot our data
-- to get the column headers as data rows
, UnpivotedData AS
(
SELECT TotalNum
, IncidentType
, EndDate
FROM WeekAndYear
UNPIVOT
(
TotalNum
FOR IncidentType IN ([NumFoodSvcInsPerformed]
,[NumRoutineFoodSvcInsPerformed]
,[NumFollowupFoodSvcInsPerformed]
,[NumCompliantFoodSvcInsPerformed]
,[NumTouristAccomInspections]
,[NumSolidWasteInsPerformed]
,[NumFacilitiesClosed])
) AS P
)
-- insert unpivoted data into temp table
-- will pivot data with dynamic SQL
INSERT INTO #WeekYearValues(IncidentType, TotalNum, EndDate)
SELECT IncidentType
, TotalNum
, EndDate
FROM UnpivotedData
-- set up dynamic query
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)
-- get a list of the columns desired these are the "dynamic" columns
-- in this case a list of dates in the tables
-- if you only want a certain date range add a WHERE clause
SELECT @Columns = COALESCE(@Columns + ',[' + CONVERT(VARCHAR(30), EndDate, 101) + ']', '[' + CONVERT(VARCHAR(30), EndDate, 101) + ']')
FROM [HHSDataTest] T
-- WHERE EndDate BETWEEN @StartDate AND @EndDate <- use this WHERE clause to limit the data returned
ORDER BY T.EndDate
-- use YearToDate as our final column name
SELECT @Columns = @Columns + ',[YearToDate]'
-- take a look at what the "dynamic" columns will be
SELECT @Columns
-- now pivot the data in #WeekYearValues
-- to get end date as a column
-- set up dynamic SQL
-- notice the dynamic part is only the pivot columns
SET @Query = '
SELECT *
FROM #WeekYearValues
PIVOT
(
MAX(TotalNum)
FOR EndDate IN (' + @Columns + ')
) AS P'
-- you can take a look at the query executed by running this
SELECT @Query
-- execute the dynamic SQL String
EXEC SP_EXECUTESQL @Query
-- drop table so we can run query again
DROP TABLE #WeekYearValues
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply