Thursday, August 28, 2014

4 Select Queries into one statement

UNION them together with UNION ALL:



SELECT 1 as ds, Count(DISTINCT[Form Number]) cnt
FROM [dbo].[NEWVIEW]
WHERE [Cohort Desc]IN( 'SSW' )
AND [Episode Start]BETWEEN'2014-06-01'AND'2014-06-30'
UNION ALL

SELECT 2 as ds, Count(DISTINCT[Form Number])asConversions
FROM [dbo].[NEWVIEW]
WHERE [Cohort Desc]IN( 'SSW' )
AND [Episode Start]BETWEEN'2014-06-01'AND'2014-06-30'
AND [Start Type Desc] = 'Continuing/Conversion'
UNION ALL
SELECT 3 as ds, Count(DISTINCT[Form Number])asAchievements
FROM [dbo].[NEWVIEW]
WHERE [Cohort Desc]IN( 'SSW' )
AND [Episode End]BETWEEN'2014-06-01'AND'2014-06-30'
UNION ALL
SELECT 4 as ds, Count (DISTINCT[Form Number])asOutofFunding
FROM [dbo].[NEWVIEW]
WHERE [Cohort Desc]IN( 'SSW' )
AND [Expected End] >= Dateadd(mm, Datediff(mm, 0, Getdate()), 0)
AND [Expected End] < Dateadd(mm, Datediff(mm, 0, Getdate()) + 1, 0)


No comments:

Post a Comment