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)
 Join 12 result sets into a 12-column result set

Author  Topic 

zaz999
Starting Member

5 Posts
Posted - 2009-02-13 : 07:29:36
Hi all,

This probably simple but I just don't know how to do it, always the way...
I'm trying to write a stored procedure to join 12 result sets into one table so that each result set has its own column.

Each result set is the result of running the same SQL query but with different date parameters. There are 12 because each one represents data for a month of the year. I want to join them into one 12-column table so that I can bind the result to a Gridview.

Here's my SQL query:

SELECT Percentage FROM dbo.CareElementData
WHERE WardID=@WardID
AND CareCategoryID = @CareCategoryID
AND (MONTH(SubmittedOn)= @Month) AND (YEAR(SubmittedOn) =@Year)
ORDER BY ElementCode

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-13 : 08:19:23
[code]CREATE PROCEDURE dbo.usp_MyGridViewFeed
(
@Year SMALLINT,
@WardID INT,
@CareCategoryID INT
)
AS

SET NOCOUNT ON

IF @Year < 1900 OR @Year > 2999 OR @Year IS NULL
SET @Year = DATEPART(YEAR, GETDATE())

DECLARE @m00 DATETIME,
@m01 DATETIME,
@m02 DATETIME,
@m03 DATETIME,
@m04 DATETIME,
@m05 DATETIME,
@m06 DATETIME,
@m07 DATETIME,
@m08 DATETIME,
@m09 DATETIME,
@m10 DATETIME,
@m11 DATETIME,
@m12 DATETIME

SELECT @m00 = DATEADD(YEAR, @ThisYear - 1900, 0),
@m01 = DATEADD(MONTH, 1, @m00),
@m02 = DATEADD(MONTH, 1, @m01),
@m03 = DATEADD(MONTH, 1, @m02),
@m04 = DATEADD(MONTH, 1, @m03),
@m05 = DATEADD(MONTH, 1, @m04),
@m06 = DATEADD(MONTH, 1, @m05),
@m07 = DATEADD(MONTH, 1, @m06),
@m08 = DATEADD(MONTH, 1, @m07),
@m09 = DATEADD(MONTH, 1, @m08),
@m10 = DATEADD(MONTH, 1, @m09),
@m11 = DATEADD(MONTH, 1, @m10),
@m12 = DATEADD(MONTH, 1, @m11)

SELECT ElementCode,
SUM(CASE WHEN SubmittedOn >= @m00 AND SubmittedOn < @m01 THEN Percentage ELSE 0 END) AS January,
SUM(CASE WHEN SubmittedOn >= @m01 AND SubmittedOn < @m02 THEN Percentage ELSE 0 END) AS February,
SUM(CASE WHEN SubmittedOn >= @m02 AND SubmittedOn < @m03 THEN Percentage ELSE 0 END) AS March,
SUM(CASE WHEN SubmittedOn >= @m03 AND SubmittedOn < @m04 THEN Percentage ELSE 0 END) AS April,
SUM(CASE WHEN SubmittedOn >= @m04 AND SubmittedOn < @m05 THEN Percentage ELSE 0 END) AS May,
SUM(CASE WHEN SubmittedOn >= @m05 AND SubmittedOn < @m06 THEN Percentage ELSE 0 END) AS June,
SUM(CASE WHEN SubmittedOn >= @m06 AND SubmittedOn < @m07 THEN Percentage ELSE 0 END) AS July,
SUM(CASE WHEN SubmittedOn >= @m07 AND SubmittedOn < @m08 THEN Percentage ELSE 0 END) AS August,
SUM(CASE WHEN SubmittedOn >= @m08 AND SubmittedOn < @m09 THEN Percentage ELSE 0 END) AS September,
SUM(CASE WHEN SubmittedOn >= @m09 AND SubmittedOn < @m10 THEN Percentage ELSE 0 END) AS October,
SUM(CASE WHEN SubmittedOn >= @m10 AND SubmittedOn < @m11 THEN Percentage ELSE 0 END) AS November,
SUM(CASE WHEN SubmittedOn >= @m11 AND SubmittedOn < @m12 THEN Percentage ELSE 0 END) AS December
FROM dbo.CareElementData
WHERE SubmittedOn >= @00
AND SubmittedOn < @m12
AND WardID = @WardID
AND CareCategoryID = @CareCategoryID
GROUP BY ElementCode
ORDER BY ElementCode[/code]
E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Bodestone
Starting Member

18 Posts
Posted - 2009-02-13 : 08:54:49
Alternatively you could use the pivot:
[CODE]
CREATE PROCEDURE percentEachMonthByYearWardAndCategory
(
@WardID int,
@careCategoryID int,
@year int
) AS

SELECT elementCode,[1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12]
FROM (
SELECT elementCode,
Percentage,
month(SubmittedOn) AS [monthSubmitted]
FROM CareElementData
WHERE WardID=@wardID
AND CareCategoryID = @CareCategoryID
AND YEAR(SubmittedOn) =@Year
) c
PIVOT
(
sum(percentage) FOR [monthSubmitted] IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])
) p
[/CODE]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-02-13 : 09:01:46
will you be always considering 1 years data at a time?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-13 : 09:02:41
Or will you use a sliding 12-month measurementperiod?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

zaz999
Starting Member

5 Posts
Posted - 2009-02-13 : 09:16:36
Good response guys I'm impressed! It's a sliding 12-month period, always the last 12 months from the current date.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-13 : 09:34:39
[code]CREATE PROCEDURE dbo.usp_MyGridViewFeed
(
@WardID INT,
@CareCategoryID INT
)
AS

SET NOCOUNT ON

DECLARE @m00 DATETIME,
@m01 DATETIME,
@m02 DATETIME,
@m03 DATETIME,
@m04 DATETIME,
@m05 DATETIME,
@m06 DATETIME,
@m07 DATETIME,
@m08 DATETIME,
@m09 DATETIME,
@m10 DATETIME,
@m11 DATETIME,
@m12 DATETIME

SELECT @m00 = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18990201'),
@m01 = DATEADD(MONTH, 1, @m00),
@m02 = DATEADD(MONTH, 1, @m01),
@m03 = DATEADD(MONTH, 1, @m02),
@m04 = DATEADD(MONTH, 1, @m03),
@m05 = DATEADD(MONTH, 1, @m04),
@m06 = DATEADD(MONTH, 1, @m05),
@m07 = DATEADD(MONTH, 1, @m06),
@m08 = DATEADD(MONTH, 1, @m07),
@m09 = DATEADD(MONTH, 1, @m08),
@m10 = DATEADD(MONTH, 1, @m09),
@m11 = DATEADD(MONTH, 1, @m10),
@m12 = DATEADD(MONTH, 1, @m11)

SELECT ElementCode,
SUM(CASE WHEN SubmittedOn >= @m00 AND SubmittedOn < @m01 THEN Percentage ELSE 0 END) AS [11],
SUM(CASE WHEN SubmittedOn >= @m01 AND SubmittedOn < @m02 THEN Percentage ELSE 0 END) AS [10],
SUM(CASE WHEN SubmittedOn >= @m02 AND SubmittedOn < @m03 THEN Percentage ELSE 0 END) AS [09],
SUM(CASE WHEN SubmittedOn >= @m03 AND SubmittedOn < @m04 THEN Percentage ELSE 0 END) AS [08],
SUM(CASE WHEN SubmittedOn >= @m04 AND SubmittedOn < @m05 THEN Percentage ELSE 0 END) AS [07],
SUM(CASE WHEN SubmittedOn >= @m05 AND SubmittedOn < @m06 THEN Percentage ELSE 0 END) AS [06],
SUM(CASE WHEN SubmittedOn >= @m06 AND SubmittedOn < @m07 THEN Percentage ELSE 0 END) AS [05],
SUM(CASE WHEN SubmittedOn >= @m07 AND SubmittedOn < @m08 THEN Percentage ELSE 0 END) AS [04],
SUM(CASE WHEN SubmittedOn >= @m08 AND SubmittedOn < @m09 THEN Percentage ELSE 0 END) AS [03],
SUM(CASE WHEN SubmittedOn >= @m09 AND SubmittedOn < @m10 THEN Percentage ELSE 0 END) AS [02],
SUM(CASE WHEN SubmittedOn >= @m10 AND SubmittedOn < @m11 THEN Percentage ELSE 0 END) AS [01],
SUM(CASE WHEN SubmittedOn >= @m11 AND SubmittedOn < @m12 THEN Percentage ELSE 0 END) AS [00]
FROM dbo.CareElementData
WHERE SubmittedOn >= @00
AND SubmittedOn < @m12
AND WardID = @WardID
AND CareCategoryID = @CareCategoryID
GROUP BY ElementCode
ORDER BY ElementCode[/code]

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Bodestone
Starting Member

18 Posts
Posted - 2009-02-13 : 09:37:08
In that case you will have to get the number of the month offset rather than the number of the month itself. Something like this should do it:
[CODE]
SELECT elementCode,[1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12]
FROM (
SELECT elementCode,
Percentage,
row_number() over(partition by elementCode order by month(SubmittedOn)) AS monthOffset
FROM CareElementData
WHERE WardID=@wardID
AND CareCategoryID = @CareCategoryID
AND SubmittedOn >= dateadd(year,-1,getdate())
) c
PIVOT
(
sum(percentage) FOR [monthoffset] IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])
) p
[/CODE]
Go to Top of Page

zaz999
Starting Member

5 Posts
Posted - 2009-02-13 : 09:39:17
Fantastic! That worked a treat, thanks so much guys!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-02-13 : 09:41:28
[code]
CREATE PROCEDURE dbo.usp_MyGridViewFeed
(
@WardID INT,
@CareCategoryID INT
)
AS

SET NOCOUNT ON


SELECT ElementCode,
SUM(CASE WHEN Seq=1 THEN Percentage ELSE 0 END) AS [1],
SUM(CASE WHEN Seq=2 THEN Percentage ELSE 0 END) AS [2],
SUM(CASE WHEN Seq=3 THEN Percentage ELSE 0 END) AS [3],
SUM(CASE WHEN Seq=4 THEN Percentage ELSE 0 END) AS [4],
SUM(CASE WHEN Seq=5 THEN Percentage ELSE 0 END) AS [5],
SUM(CASE WHEN Seq=6 THEN Percentage ELSE 0 END) AS [6],
...
SUM(CASE WHEN Seq=12 THEN Percentage ELSE 0 END) AS [12],
FROM
(
SELECT ElementCode,
Percentage,
ROW_NUMBER() OVER (ORDER BY DATEPART(yyyy,SubmittedOn),DATEPART(mm,SubmittedOn) ) AS Seq
FROM dbo.CareElementData
WHERE SubmittedOn >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-12,0)
AND SubmittedOn < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)+1
AND WardID = @WardID
AND CareCategoryID = @CareCategoryID
)m
GROUP BY ElementCode
ORDER BY ElementCode
[/code]
Go to Top of Page

zaz999
Starting Member

5 Posts
Posted - 2009-03-10 : 07:28:59
Ok guys, the plot thickens! The code you gave me worked great but now I have the problem of getting the data for all Wards and averaging it if a WardID of zero is passed into the stored procedure. The code I am currently using is as follows:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Report_AnnualCareElementTEST]
(
	@WardID INT,
	@CareCategoryID INT
)
AS

SET NOCOUNT ON

DECLARE	@m00 DATETIME,
	@m01 DATETIME,
	@m02 DATETIME,
	@m03 DATETIME,
	@m04 DATETIME,
	@m05 DATETIME,
	@m06 DATETIME,
	@m07 DATETIME,
	@m08 DATETIME,
	@m09 DATETIME,
	@m10 DATETIME,
	@m11 DATETIME,
	@m12 DATETIME

SELECT	@m00 = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18990201'),
	@m01 = DATEADD(MONTH, 1, @m00),
	@m02 = DATEADD(MONTH, 1, @m01),
	@m03 = DATEADD(MONTH, 1, @m02),
	@m04 = DATEADD(MONTH, 1, @m03),
	@m05 = DATEADD(MONTH, 1, @m04),
	@m06 = DATEADD(MONTH, 1, @m05),
	@m07 = DATEADD(MONTH, 1, @m06),
	@m08 = DATEADD(MONTH, 1, @m07),
	@m09 = DATEADD(MONTH, 1, @m08),
	@m10 = DATEADD(MONTH, 1, @m09),
	@m11 = DATEADD(MONTH, 1, @m10),
	@m12 = DATEADD(MONTH, 1, @m11)

SELECT	ElementCode,
(SELECT [Name] FROM dbo.CareElement ce WHERE CareCategoryID = @CareCategoryID AND ce.ElementCode =  ced.ElementCode) as 'Element of Care',
		SUM(CASE WHEN SubmittedOn >= @m00 AND SubmittedOn < @m01 THEN Percentage ELSE 0 END)AS [11],
		SUM(CASE WHEN SubmittedOn >= @m01 AND SubmittedOn < @m02 THEN Percentage ELSE 0 END)AS [10],
		SUM(CASE WHEN SubmittedOn >= @m02 AND SubmittedOn < @m03 THEN Percentage ELSE 0 END)AS [09],
		SUM(CASE WHEN SubmittedOn >= @m03 AND SubmittedOn < @m04 THEN Percentage ELSE 0 END)AS [08],
		SUM(CASE WHEN SubmittedOn >= @m04 AND SubmittedOn < @m05 THEN Percentage ELSE 0 END)AS [07],
		SUM(CASE WHEN SubmittedOn >= @m05 AND SubmittedOn < @m06 THEN Percentage ELSE 0 END)AS [06],
		SUM(CASE WHEN SubmittedOn >= @m06 AND SubmittedOn < @m07 THEN Percentage ELSE 0 END)AS [05],
		SUM(CASE WHEN SubmittedOn >= @m07 AND SubmittedOn < @m08 THEN Percentage ELSE 0 END)AS [04],
		SUM(CASE WHEN SubmittedOn >= @m08 AND SubmittedOn < @m09 THEN Percentage ELSE 0 END)AS [03],
		SUM(CASE WHEN SubmittedOn >= @m09 AND SubmittedOn < @m10 THEN Percentage ELSE 0 END) AS [02],
		SUM(CASE WHEN SubmittedOn >= @m10 AND SubmittedOn < @m11 THEN Percentage ELSE 0 END) AS [01],
		SUM(CASE WHEN SubmittedOn >= @m11 AND SubmittedOn < @m12 THEN Percentage ELSE 0 END)AS [00]

FROM		dbo.CareElementData ced
WHERE		SubmittedOn >= @m00 
		AND SubmittedOn < @m12 
AND CareCategoryID = (CASE WHEN @CareCategoryID = 0 THEN CareCategoryID ELSE @CareCategoryID END)
AND WardID = (CASE WHEN @WardID = 0 THEN WardID ELSE @WardID END)
GROUP BY	ElementCode
ORDER BY	ElementCode




Below is the output table data for 2 wards which needs to be combined into one average table.

Ward 1


 Element of Care Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar 
a Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0   
b Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0 
c Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0  
d Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0  
e Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0  
f Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0  
g Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0




Ward 2


 Element of Care Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar 
a Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 90 0.0 0.0  
b Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 90 0.0 0.0   
c Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 90 0.0 0.0  
d Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 90 0.0 0.0




The result I currently get if I use the sql I have with a WardID of 0 is as follows.



Element of Care Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar 
a Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 190 80 0.0  
b Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 190 80 0.0  
c Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 190 80 0.0  
d Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 190 80 0.0  
e Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0   
f Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0   
g Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0



As you can see rows a-d contain the sum of the 2 tables for January when they should contain the average. I have tried changing the SQL SUM() to AVG() as well as a number of other things but this results in the following:



 Element of Care Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar 
a Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 63 26 0.0   
b Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 63 26 0.0   
c Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 63 26 0.0   
d Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 63 26 0.0   
e Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 50 40 0.0   
f Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 50 40 0.0   
g Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 50 40 0.0



The problem is that the values for each month in the average table can have any number of contributing values. For example, the value in the table above in cell 'd-Jan' may need to be the average of 2 values while a cell in e-Jan or d-Feb may be the average of any number of other values.

The correct table should be as follows:



 Element of Care Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar 
a Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 95 80 0.0   
b Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 95 80 0.0   
c Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 95 80 0.0   
d Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 95 80 0.0   
e Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0   
f Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0 
g Description... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100 80 0.0



I've spent a good 2 days on this now and I just can't crack it! If you think you can manage it then please let me know!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-10 : 07:38:20
Remember that AVERAGE includes zero as value. Change ELSE 0 to ELSE NULL if you use AVERAGE.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site