Help with Stored Proc
-
Hi all,
I'm hoping the collective brilliance here can help me out. I'm far from an SQL guru and certainly not a n00b but for the life I me, I can't get my head around how to achieve what I want.
I have the following stored Proc
ALTER PROCEDURE [dbo].[REP_MonthlyYearOnYear_SYD]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CurrentYEAR INT
DECLARE MY_Cursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT timesheetPeriodYear FROM timesheetPeriod
OPEN MY_Cursor
FETCH NEXT FROM MY_Cursor INTO @CurrentYear
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT { fn MONTHNAME(b.timesheetPeriodStart) } AS TheMonth, b.timesheetPeriodYear AS TheYear,
(CAST({ fn MONTHNAME(b.timesheetPeriodStart)} AS VARCHAR) + ' ' + CAST(b.timesheetPeriodYear AS VARCHAR(4))) AS CycleTitle,
SUM(a.totalWords) AS TotalWords
FROM timesheetEntry a
INNER JOIN timesheetPeriod b ON b.timesheetPeriodID = a.timesheetPeriodID
INNER JOIN aspnet_Users AS c ON c.userID = a.UserId
INNER JOIN aspnet_UsersInRoles AS d ON d.UserId = c.UserId
INNER JOIN aspnet_Roles AS e ON e.RoleId = d.RoleId
WHERE (e.RoleName = 'SYD-FTusers' OR e.RoleName = 'SYD-CASusers')
AND b.timesheetPeriodYear = @CurrentYEAR
GROUP BY {fn MONTHNAME(b.timesheetPeriodStart)}, b.timesheetPeriodYear
ORDER BY MAX(b.timesheetPeriodStart)
FETCH NEXT FROM MY_Cursor INTO @CurrentYEAR
END
CLOSE MY_Cursor
DEALLOCATE MY_Cursor
END
Now this stored proc returns the following result sets.
The result set that I want is this
For the life I me I can't figure out how to get the result set that I want above. I'm assuming the number of columns in the result set will need to be dynamic in the sense that if there are 5 different years, then there will be a column for each of the years etc.
I'm using the resulting data for some comparison reporting and I guess I could run each year separately and there piece it together bit by bit in my code behind file (asp.net) but I'm sure there must be a way to achieve this using T-SQL.
Any help will be greatly appreciated. Thanks in advance.
-
Ahh, my favorite TSQL word comes into play here: CASE
Try working something like this into your query:
SUM(CASE WHEN year = 2012 then a.totalWords else 0 end) AS 2012TotalWords,
SUM(CASE WHEN year = 2013 then a.totalWords else 0 end) AS 2013TotalWords,
...
Here "year" is a thing (DATEPART perhaps, or part of CONVERT string, time table column, etc) that gives you 4 digit year value.
You will also need to drive the entire query with a set of data of some flavor that gives you ALL month/year combinations you need - i.e. Jan 2012 through December 2014. There are multiple easy ways to accomplish that.
The "dynamic" nature of it can be added, but it introduces significant complexity into the algorithm. Search online for ways to incorporate that if/when you decide you need it. You can build a large-range output set into a temp table and then just project out the complete years that contain at least one month of data more easily than you can make it dynamic I bet. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Thanks for that Kevin. The thing is, the resulting output table will need to be dynamic in the sense that as the database grows (i.e. more years of data) I'll need the result data to be funneled into it. Right now there are 3 years worth of data so I could specify them statically, but I that would mean amending the stored proc every year and who the heck is going to remember to do that 😀 I was kinda looking for a 'set and forget' option that just grows as the database grows.
-
As Kevin suggested you would need to build Dynamic SQL and execute it
Something like this should give you an Idea
DECLARE @Year TABLE
(
CalYear INT
)
INSERT INTO @Year
VALUES (2014),(2015),(2016)
DECLARE @sql VARCHAR(4000) = 'SELECT Month,'
DECLARE @SQLFrom VARCHAR(4000) = 'FROM <TABLE>'
SET
@sql=@SQL+
(
SELECT
STUFF
(
(
SELECT
', SUM(CASE WHEN year = '
+CONVERT(varchar(4),CalYear)
+' then a.totalWords else 0 end) AS ['
+CONVERT(varchar(4),CalYear)
+'TotalWords]'
FROM @Year
FOR XML PATH('')
)
,1,1,'')
)
+@SQLFrom
Print @sql
The issue is if you have a lot of complex code, so you might want to wrap any joins ETC into a View and Select from that. You could also add on a Where clause but that can get convoluted,
Check out this blog on Catch all Queries http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Is there any reason why you can't use the PIVOT command. You would need to include a month number and well as the month name otherwise the data will be sorted alphabetically by month-name
-
cre8tivedaze (12/13/2014)
Thanks for that Kevin. The thing is, the resulting output table will need to be dynamic in the sense that as the database grows (i.e. more years of data) I'll need the result data to be funneled into it. Right now there are 3 years worth of data so I could specify them statically, but I that would mean amending the stored proc every year and who the heck is going to remember to do that 😀 I was kinda looking for a 'set and forget' option that just grows as the database grows.It is set-and-forget. Set it up with 10 years (or 50) of months in the range and then just filter out the years that don't have at least one month with values in them. This is a trivial effort both to code and to execute at runtime.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply