Solved

How to calculate difference in sets of SQL query results based on parameters

Posted on 2010-08-31
4
270 Views
Last Modified: 2012-05-10
I have a query (CTE) that produces calculated results based on two date parameters. How can i add to this query to calculate the difference in the results for the two dates that are returned.
e.g. on running the query i put 30/06/2010 and 31/05/2010 as the @date and @date1 parameters, how would i then get the query to caluclate the difference in the NEETAdj16-18 figure for those two months. I will need to calculate the differnec for all my number result rows by district (not% rows).
I am a beginner and a little out of my depth but i want to say something like:
e.g.
calculatedifference (@date'TotalNeetAdj16-18') - (@date1'TotalNeetAdj16-18')

HELP!!!!
WITH Base AS(



SELECT





     C.LeadLEA,C.RowStartDate, C.AspireID, C.Gender, I.TeenageMother, C.SENStatus, I.CareLeaver, I.[SupervisedYOT-Ever], C.[EET/NEETCategory], C.LearningCategory, 

                      DimPostcodes.[District Name]

FROM         DimCxsClientsIndCircs AS I RIGHT OUTER JOIN

                      DimCxsClients AS C INNER JOIN

                      DimPostcodes ON C.StatsPostcodeKey = DimPostcodes.PostcodeKey ON I.IndCircKey = C.IndCircKey

WHERE     ((((C.RowStartDate = CONVERT(datetime, @Date, 103))AND (C.Cohort = 'postcomped'))) or

		   (((C.RowStartDate = CONVERT(datetime, @Date1, 103))AND (C.Cohort = 'postcomped'))))

GROUP BY C.AspireID, C.Gender, C.SENStatus, I.TeenageMother, I.CareLeaver, I.[SupervisedYOT-Ever], C.Age, C.LeadLEA, C.[EET/NEETCategory], 

                      C.LearningCategory,DimPostcodes.[District Name],C.RowStartDate

HAVING      (C.Age BETWEEN 16 AND 18) AND (C.LeadLEA = 'Wiston') AND (DimPostcodes.[District Name] <> 'stoney')

)

 

, total as (

select LeadLEA,[District Name],Rowstartdate,



--SUM(Total) AS TotalCohort, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS TotalNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS TotalEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS TotalCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS TotalCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS TotalNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS TotalCurrencyNEETNA,  

SUM(CASE [EET/NEETCategory] WHEN 'Currency Not EET or NEET' THEN 1 ELSE 0 END) AS TotalCurrencyNotEETorNEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Not Known (NI)' THEN 1 ELSE 0 END) AS [TotalNotKnown(NI)], 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS TotalInLearning

from base

Group By leadLEA,[District Name],rowstartdate

)

 

 

, male  as (

select LeadLEA,[District Name],Rowstartdate,

 --sum(total) AS Totalmale, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS maleNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS maleEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS maleCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS maleCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS maleNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS maleCurrencyNEETNA,  

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS maleInLearning

from base

where gender = 'male'

Group By leadLEA,[District Name],rowstartdate

)

 

, female  as (

select LeadLEA,[District Name],Rowstartdate,

--sum(total) AS TotalFemale, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS FemaleNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS FemaleEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS FemaleCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS FemaleCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS FemaleNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS FemaleCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS FemaleInLearning

from base

where gender = 'female'

Group By leadLEA,[District Name],rowstartdate

)

 

 ,teenmum AS (  

select LeadLEA,[District Name],Rowstartdate, 

--SUM(total) AS TotalTeenMum,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS TMNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS TMEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS TMCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS TMCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS TMNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS TMCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS TMInLearning

FROM Base 

WHERE (TeenageMother = 'True')

GROUP BY LeadLEA, [District Name], Rowstartdate

)



 ,SEN AS (  

select LeadLEA, [District Name],Rowstartdate,

--SUM(total) AS totalSEN,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS SENNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS SENEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS SENCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS SENCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS SENNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS SENCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS SENInLearning

FROM Base 

WHERE SENstatus = 'Statemented'

GROUP BY leadLEA,[District Name],rowstartdate

)



,SAplus AS(

select leadLEA,[District Name],rowstartdate,

--SUM(total) AS totalSAplus,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS SAplusNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS SAplusEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS SAplusCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS SAplusCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS SAplusNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS SAplusCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS SAplusInLearning

FROM Base 

WHERE SENstatus = 'School Action +'

GROUP BY leadLEA,[District Name],rowstartdate

)

,SA AS(

select leadLEA,[District Name],rowstartdate,

--SUM(total) AS totalSA,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS SANEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS SAEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS SACurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS SACurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS SANEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS SACurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS SAInLearning

FROM Base 

WHERE SENstatus = 'School Action'

GROUP BY leadLEA,[District Name],rowstartdate

)

,CareLvr AS(

select leadLEA,[District Name],rowstartdate,

--SUM(total) AS totalCareLvr,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS CareLvrNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS CareLvrEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS CareLvrCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS CareLvrCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS CareLvrNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS CareLvrCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS CareLvrInLearning

FROM Base 

WHERE CareLeaver = 'True'

GROUP BY leadLEA,[District Name],rowstartdate

)



,YngOff AS(

select leadLEA,[District Name],rowstartdate,

--SUM(total) AS totalYngOff,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS YngOffNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS YngOffEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS YngOffCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS YngOffCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS YngOffNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS YngOffCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS YngOffInLearning

FROM Base 

WHERE  [SupervisedYOT-Ever]= 'True'

Group By leadLEA,[District Name],rowstartdate

)



, [TotalCohort16-18] AS(

Select total.LeadLEA,[District Name],Rowstartdate, 

SUM((TotalNEETAvail + TotalNEETNA) + (0.08 * TotalCurrencyEET) + (0.58 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA))) AS [Total16-18NEETAdj], 

SUM((TotalEET + 0.92 * TotalCurrencyEET) + 0.42 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA)) AS [Total16-18EETAdj], 

SUM (((TotalNEETAvail + TotalNEETNA) + (0.08 * TotalCurrencyEET) + (0.58 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA))) +((TotalEET + 0.92 * TotalCurrencyEET) + 0.42 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA)))As [TotalCohort16-18]

From total

Group By leadLEA,[District Name],rowstartdate

)



, [maleCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((maleNEETAvail + maleNEETNA) + (0.08 * maleCurrencyEET) + (0.58 * (maleCurrencyNEETAvail + maleCurrencyNEETNA))) AS [male16-18NEETAdj], 

SUM((maleEET + 0.92 * maleCurrencyEET) + 0.42 * (maleCurrencyNEETAvail + maleCurrencyNEETNA)) AS [male16-18EETAdj], 

SUM (((maleNEETAvail + maleNEETNA) + (0.08 * maleCurrencyEET) + (0.58 * (maleCurrencyNEETAvail + maleCurrencyNEETNA))) +((maleEET + 0.92 * maleCurrencyEET) + 0.42 * (maleCurrencyNEETAvail + maleCurrencyNEETNA)))As [maleCohort16-18]

From male

Group By leadLEA,[District Name],rowstartdate

)



, [femaleCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((femaleNEETAvail + femaleNEETNA) + (0.08 * femaleCurrencyEET) + (0.58 * (femaleCurrencyNEETAvail + femaleCurrencyNEETNA))) AS [female16-18NEETAdj], 

SUM((femaleEET + 0.92 * femaleCurrencyEET) + 0.42 * (femaleCurrencyNEETAvail + femaleCurrencyNEETNA)) AS [female16-18EETAdj], 

SUM (((femaleNEETAvail + femaleNEETNA) + (0.08 * femaleCurrencyEET) + (0.58 * (femaleCurrencyNEETAvail + femaleCurrencyNEETNA))) +((femaleEET + 0.92 * femaleCurrencyEET) + 0.42 * (femaleCurrencyNEETAvail + femaleCurrencyNEETNA)))As [femaleCohort16-18]

From female

Group By leadLEA,[District Name],rowstartdate

)



, [TMCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((TMNEETAvail + TMNEETNA) + (0.08 * TMCurrencyEET) + (0.58 * (TMCurrencyNEETAvail + TMCurrencyNEETNA))) AS [TM16-18NEETAdj], 

SUM((TMEET + 0.92 * TMCurrencyEET) + 0.42 * (TMCurrencyNEETAvail + TMCurrencyNEETNA)) AS [TM16-18EETAdj], 

SUM (((TMNEETAvail + TMNEETNA) + (0.08 * TMCurrencyEET) + (0.58 * (TMCurrencyNEETAvail + TMCurrencyNEETNA))) +((TMEET + 0.92 * TMCurrencyEET) + 0.42 * (TMCurrencyNEETAvail + TMCurrencyNEETNA)))As [TMCohort16-18]

From Teenmum

Group By leadLEA,[District Name],rowstartdate

)



, [SENCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((SENNEETAvail + SENNEETNA) + (0.08 * SENCurrencyEET) + (0.58 * (SENCurrencyNEETAvail + SENCurrencyNEETNA))) AS [SEN16-18NEETAdj], 

SUM((SENEET + 0.92 * SENCurrencyEET) + 0.42 * (SENCurrencyNEETAvail + SENCurrencyNEETNA)) AS [SEN16-18EETAdj], 

SUM (((SENNEETAvail + SENNEETNA) + (0.08 * SENCurrencyEET) + (0.58 * (SENCurrencyNEETAvail + SENCurrencyNEETNA))) +((SENEET + 0.92 * SENCurrencyEET) + 0.42 * (SENCurrencyNEETAvail + SENCurrencyNEETNA)))As [SENCohort16-18]

From SEN

Group By leadLEA,[District Name],rowstartdate

)



, [SAplusCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((SAplusNEETAvail + SAplusNEETNA) + (0.08 * SAplusCurrencyEET) + (0.58 * (SAplusCurrencyNEETAvail + SAplusCurrencyNEETNA))) AS [SAplus16-18NEETAdj], 

SUM((SAplusEET + 0.92 * SAplusCurrencyEET) + 0.42 * (SAplusCurrencyNEETAvail + SAplusCurrencyNEETNA)) AS [SAplus16-18EETAdj], 

SUM (((SAplusNEETAvail + SAplusNEETNA) + (0.08 * SAplusCurrencyEET) + (0.58 * (SAplusCurrencyNEETAvail + SAplusCurrencyNEETNA))) +((SAplusEET + 0.92 * SAplusCurrencyEET) + 0.42 * (SAplusCurrencyNEETAvail + SAplusCurrencyNEETNA)))As [SAplusCohort16-18]

From SAplus

Group By leadLEA,[District Name],rowstartdate

)



, [SACohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((SANEETAvail + SANEETNA) + (0.08 * SACurrencyEET) + (0.58 * (SACurrencyNEETAvail + SACurrencyNEETNA))) AS [SA16-18NEETAdj], 

SUM((SAEET + 0.92 * SACurrencyEET) + 0.42 * (SACurrencyNEETAvail + SACurrencyNEETNA)) AS [SA16-18EETAdj], 

SUM (((SANEETAvail + SANEETNA) + (0.08 * SACurrencyEET) + (0.58 * (SACurrencyNEETAvail + SACurrencyNEETNA))) +((SAEET + 0.92 * SACurrencyEET) + 0.42 * (SACurrencyNEETAvail + SACurrencyNEETNA)))As [SACohort16-18]

From SA

Group By leadLEA,[District Name],rowstartdate

)



, [CareLvrCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((CareLvrNEETAvail + CareLvrNEETNA) + (0.08 * CareLvrCurrencyEET) + (0.58 * (CareLvrCurrencyNEETAvail + CareLvrCurrencyNEETNA))) AS [CareLvr16-18NEETAdj], 

SUM((CareLvrEET + 0.92 * CareLvrCurrencyEET) + 0.42 * (CareLvrCurrencyNEETAvail + CareLvrCurrencyNEETNA)) AS [CareLvr16-18EETAdj], 

SUM (((CareLvrNEETAvail + CareLvrNEETNA) + (0.08 * CareLvrCurrencyEET) + (0.58 * (CareLvrCurrencyNEETAvail + CareLvrCurrencyNEETNA))) +((CareLvrEET + 0.92 * CareLvrCurrencyEET) + 0.42 * (CareLvrCurrencyNEETAvail + CareLvrCurrencyNEETNA)))As [CareLvrCohort16-18]

From CareLvr

Group By leadLEA,[District Name],rowstartdate

)



, [YngOffCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((YngOffNEETAvail + YngOffNEETNA) + (0.08 * YngOffCurrencyEET) + (0.58 * (YngOffCurrencyNEETAvail + YngOffCurrencyNEETNA))) AS [YngOff16-18NEETAdj], 

SUM((YngOffEET + 0.92 * YngOffCurrencyEET) + 0.42 * (YngOffCurrencyNEETAvail + YngOffCurrencyNEETNA)) AS [YngOff16-18EETAdj], 

SUM (((YngOffNEETAvail + YngOffNEETNA) + (0.08 * YngOffCurrencyEET) + (0.58 * (YngOffCurrencyNEETAvail + YngOffCurrencyNEETNA))) +((YngOffEET + 0.92 * YngOffCurrencyEET) + 0.42 * (YngOffCurrencyNEETAvail + YngOffCurrencyNEETNA)))As [YngOffCohort16-18]

From YngOff

Group By leadLEA,[District Name],rowstartdate

)



SELECT     

[TotalCohort16-18].Leadlea,

[TotalCohort16-18].[District Name],

[TotalCohort16-18].[RowStartDate],

[TotalCohort16-18].[Total16-18NEETAdj],

[TotalCohort16-18].[Total16-18EETAdj], 

[TotalCohort16-18].[TotalCohort16-18], 

[TotalCohort16-18].[Total16-18NEETAdj]/[TotalCohort16-18] AS NEETAdj, 

[maleCohort16-18].[male16-18NEETAdj],

([maleCohort16-18].[male16-18NEETAdj]/[maleCohort16-18].[maleCohort16-18]) As maleNEETAdj,

[femaleCohort16-18].[female16-18NEETAdj],

([femaleCohort16-18].[female16-18NEETAdj]/[femaleCohort16-18].[femaleCohort16-18]) As femaleNEETAdj,

[TMCohort16-18].[TM16-18NEETAdj],

([TMCohort16-18].[TM16-18NEETAdj]/[TMCohort16-18].[TMCohort16-18]) As TMNEETAdj,

[SENCohort16-18].[SEN16-18NEETAdj],

([SENCohort16-18].[SEN16-18NEETAdj]/[SENCohort16-18].[SENCohort16-18]) As SENNEETAdj,

[SAplusCohort16-18].[SAplus16-18NEETAdj],

([SAplusCohort16-18].[SAplus16-18NEETAdj]/[SAplusCohort16-18].[SAplusCohort16-18]) As SAplusNEETAdj,

[SACohort16-18].[SA16-18NEETAdj],

([SACohort16-18].[SA16-18NEETAdj]/[SACohort16-18].[SACohort16-18]) As SANEETAdj,

[CareLvrCohort16-18].[CareLvr16-18NEETAdj],

Case When [CareLvrCohort16-18].[CareLvr16-18NEETAdj]=0 then 0 else([CareLvrCohort16-18].[CareLvr16-18NEETAdj]/[CareLvrCohort16-18].[CareLvrCohort16-18]) End As CareLvrNEETAdj,

[YngOffCohort16-18].[YngOff16-18NEETAdj],

([YngOffCohort16-18].[YngOff16-18NEETAdj]/[YngOffCohort16-18].[YngOffCohort16-18]) As YngOffNEETAdj



FROM         

[TotalCohort16-18]

left outer join [maleCohort16-18] ON [TotalCohort16-18].[District Name]=[maleCohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[maleCohort16-18].[RowStartDate]

left outer join [femaleCohort16-18] ON [TotalCohort16-18].[District Name]=[femaleCohort16-18].[District Name] 

								  and [TotalCohort16-18].[RowStartDate]=[femaleCohort16-18].[RowStartDate]

left outer join [TMCohort16-18] ON [TotalCohort16-18].[District Name]=[TMCohort16-18].[District Name] 

								  and [TotalCohort16-18].[RowStartDate]=[TMCohort16-18].[RowStartDate]

left outer join [SENCohort16-18] ON [TotalCohort16-18].[District Name]=[SENCohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[SENCohort16-18].[RowStartDate]

left outer join [SAplusCohort16-18] ON [TotalCohort16-18].[District Name]=[SAplusCohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[SAplusCohort16-18].[RowStartDate]

left outer join [SACohort16-18] ON [TotalCohort16-18].[District Name]=[SACohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[SACohort16-18].[RowStartDate]

left outer join [CareLvrCohort16-18] ON [TotalCohort16-18].[District Name]=[CareLvrCohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[CareLvrCohort16-18].[RowStartDate]

left outer join [YngOffCohort16-18] ON [TotalCohort16-18].[District Name]=[YngOffCohort16-18].[District Name]

								and [TotalCohort16-18].[RowStartDate]=[YngOffCohort16-18].[RowStartDate]

GROUP BY   

[TotalCohort16-18].Leadlea,

[TotalCohort16-18].[District Name],

[TotalCohort16-18].[RowStartDate],

[TotalCohort16-18].[Total16-18NEETAdj],

[TotalCohort16-18].[Total16-18EETAdj], 

[TotalCohort16-18].[TotalCohort16-18],

[maleCohort16-18].[male16-18NEETAdj],

[maleCohort16-18].[maleCohort16-18],

[femaleCohort16-18].[female16-18NEETAdj],

[femaleCohort16-18].[femaleCohort16-18],

[TMCohort16-18].[TM16-18NEETAdj],

[TMCohort16-18].[TMCohort16-18],

[SENCohort16-18].[SEN16-18NEETAdj],

[SENCohort16-18].[SENCohort16-18],

[SAplusCohort16-18].[SAplus16-18NEETAdj],

[SAplusCohort16-18].[SAplusCohort16-18],

[SACohort16-18].[SA16-18NEETAdj],

[SACohort16-18].[SACohort16-18],

[CareLvrCohort16-18].[CareLvr16-18NEETAdj],

[CareLvrCohort16-18].[CareLvrCohort16-18],

[YngOffCohort16-18].[YngOff16-18NEETAdj],

[YngOffCohort16-18].[YngOffCohort16-18]

Open in new window

0
Comment
Question by:BelizeMelanie
  • 2
  • 2
4 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 33567855
you're question is not clear. Can you please try rephrasing your question? For example, when you say:
calculatedifference (@date'TotalNeetAdj16-18') - (@date1'TotalNeetAdj16-18')
What do you mean? you want to calculate the difference from two columns (I don't know which ones) if the rows are between the @date and @date1 period?

Also please use the exact column names that you want to use for the calculation. I could not find the columns TotalNeetAdj16-18 nor the NEETAdj16-18 . Please be specific.
 
0
 

Author Comment

by:BelizeMelanie
ID: 33569024
My apologies for not making my question clear.

My set of queries has two date parameters, @date and @date1. This is because the result needs to show this month compared to last month or this month this year compared to the same last year, etc. It needs to have flexible dates and only two dates are ever compared.

My query produces the results for the two dates with the correct counts grouped by leadlea, district and rowstartdate (rowstartdates prompted by the inputted parameters).

I have attached a spreasheet with the first tab showing the result set, on this occasion @date was 30/04/2009 and @date1 was 30/04/2010. The second tab show the required calculation.

So my current query result gives me rows for each leadlea, district and the two dates with all of the relevant counts.

Where i have counts, e.g. Total16-18NEETAdj i need to calculate the difference in this field for each of the 2 months (as per the two parameters).
So in this example, the number difference between the first date result and the second date result.

I am trying to go from this:
Leadlea      District Name         RowStartDate      Total16-18NEETAdj
wiston      Carply         30/04/2009                           242.84
wiston      Carply         30/04/2010                           218.64

TO THIS:
Leadlea           Wiston            
District Name           Carpley            
RowStartDate      30/04/2009      30/04/2010      CalcDiff
Total16-18NEETAdj      242.84      218.64      24.2
male16-18NEETAdj      141.06      110.04      31.02
female16-18NEETAdj      101.78      108.6      -6.82
TM16-18NEETAdj      26.48      42.32      -15.84
(it doesnt have to be laid out this way as long as it gives me a field which has the difference in numbers for the two dates).

I sincerely hope I have explained this better and would be grateful of any help you may be able to offer.

QueryResult.xls
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33577195
Ok, so try the below:
WITH Base AS(



SELECT





     C.LeadLEA,C.RowStartDate, C.AspireID, C.Gender, I.TeenageMother, C.SENStatus, I.CareLeaver, I.[SupervisedYOT-Ever], C.[EET/NEETCategory], C.LearningCategory, 

                      DimPostcodes.[District Name]

FROM         DimCxsClientsIndCircs AS I RIGHT OUTER JOIN

                      DimCxsClients AS C INNER JOIN

                      DimPostcodes ON C.StatsPostcodeKey = DimPostcodes.PostcodeKey ON I.IndCircKey = C.IndCircKey

WHERE     ((((C.RowStartDate = CONVERT(datetime, @Date, 103))AND (C.Cohort = 'postcomped'))) or

		   (((C.RowStartDate = CONVERT(datetime, @Date1, 103))AND (C.Cohort = 'postcomped'))))

GROUP BY C.AspireID, C.Gender, C.SENStatus, I.TeenageMother, I.CareLeaver, I.[SupervisedYOT-Ever], C.Age, C.LeadLEA, C.[EET/NEETCategory], 

                      C.LearningCategory,DimPostcodes.[District Name],C.RowStartDate

HAVING      (C.Age BETWEEN 16 AND 18) AND (C.LeadLEA = 'Wiston') AND (DimPostcodes.[District Name] <> 'stoney')

)

 

, total as (

select LeadLEA,[District Name],Rowstartdate,



--SUM(Total) AS TotalCohort, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS TotalNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS TotalEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS TotalCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS TotalCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS TotalNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS TotalCurrencyNEETNA,  

SUM(CASE [EET/NEETCategory] WHEN 'Currency Not EET or NEET' THEN 1 ELSE 0 END) AS TotalCurrencyNotEETorNEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Not Known (NI)' THEN 1 ELSE 0 END) AS [TotalNotKnown(NI)], 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS TotalInLearning

from base

Group By leadLEA,[District Name],rowstartdate

)

 

 

, male  as (

select LeadLEA,[District Name],Rowstartdate,

 --sum(total) AS Totalmale, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS maleNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS maleEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS maleCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS maleCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS maleNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS maleCurrencyNEETNA,  

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS maleInLearning

from base

where gender = 'male'

Group By leadLEA,[District Name],rowstartdate

)

 

, female  as (

select LeadLEA,[District Name],Rowstartdate,

--sum(total) AS TotalFemale, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS FemaleNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS FemaleEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS FemaleCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS FemaleCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS FemaleNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS FemaleCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS FemaleInLearning

from base

where gender = 'female'

Group By leadLEA,[District Name],rowstartdate

)

 

 ,teenmum AS (  

select LeadLEA,[District Name],Rowstartdate, 

--SUM(total) AS TotalTeenMum,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS TMNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS TMEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS TMCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS TMCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS TMNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS TMCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS TMInLearning

FROM Base 

WHERE (TeenageMother = 'True')

GROUP BY LeadLEA, [District Name], Rowstartdate

)



 ,SEN AS (  

select LeadLEA, [District Name],Rowstartdate,

--SUM(total) AS totalSEN,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS SENNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS SENEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS SENCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS SENCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS SENNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS SENCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS SENInLearning

FROM Base 

WHERE SENstatus = 'Statemented'

GROUP BY leadLEA,[District Name],rowstartdate

)



,SAplus AS(

select leadLEA,[District Name],rowstartdate,

--SUM(total) AS totalSAplus,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS SAplusNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS SAplusEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS SAplusCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS SAplusCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS SAplusNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS SAplusCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS SAplusInLearning

FROM Base 

WHERE SENstatus = 'School Action +'

GROUP BY leadLEA,[District Name],rowstartdate

)

,SA AS(

select leadLEA,[District Name],rowstartdate,

--SUM(total) AS totalSA,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS SANEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS SAEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS SACurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS SACurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS SANEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS SACurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS SAInLearning

FROM Base 

WHERE SENstatus = 'School Action'

GROUP BY leadLEA,[District Name],rowstartdate

)

,CareLvr AS(

select leadLEA,[District Name],rowstartdate,

--SUM(total) AS totalCareLvr,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS CareLvrNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS CareLvrEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS CareLvrCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS CareLvrCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS CareLvrNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS CareLvrCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS CareLvrInLearning

FROM Base 

WHERE CareLeaver = 'True'

GROUP BY leadLEA,[District Name],rowstartdate

)



,YngOff AS(

select leadLEA,[District Name],rowstartdate,

--SUM(total) AS totalYngOff,

SUM(CASE [EET/NEETCategory] WHEN 'NEET' THEN 1 ELSE 0 END) AS YngOffNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'EET' THEN 1 ELSE 0 END) AS YngOffEET, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET' THEN 1 ELSE 0 END) AS YngOffCurrencyNEETAvail, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency EET' THEN 1 ELSE 0 END) AS YngOffCurrencyEET, 

SUM(CASE [EET/NEETCategory] WHEN 'NEET NA' THEN 1 ELSE 0 END) AS YngOffNEETNA, 

SUM(CASE [EET/NEETCategory] WHEN 'Currency NEET NA' THEN 1 ELSE 0 END) AS YngOffCurrencyNEETNA, 

SUM(CASE [LearningCategory] WHEN 'In Learning' THEN 1 ELSE 0 END) AS YngOffInLearning

FROM Base 

WHERE  [SupervisedYOT-Ever]= 'True'

Group By leadLEA,[District Name],rowstartdate

)



, [TotalCohort16-18] AS(

Select total.LeadLEA,[District Name],Rowstartdate, 

SUM((TotalNEETAvail + TotalNEETNA) + (0.08 * TotalCurrencyEET) + (0.58 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA))) AS [Total16-18NEETAdj], 

SUM((TotalEET + 0.92 * TotalCurrencyEET) + 0.42 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA)) AS [Total16-18EETAdj], 

SUM (((TotalNEETAvail + TotalNEETNA) + (0.08 * TotalCurrencyEET) + (0.58 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA))) +((TotalEET + 0.92 * TotalCurrencyEET) + 0.42 * (TotalCurrencyNEETAvail + TotalCurrencyNEETNA)))As [TotalCohort16-18]

From total

Group By leadLEA,[District Name],rowstartdate

)



, [maleCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((maleNEETAvail + maleNEETNA) + (0.08 * maleCurrencyEET) + (0.58 * (maleCurrencyNEETAvail + maleCurrencyNEETNA))) AS [male16-18NEETAdj], 

SUM((maleEET + 0.92 * maleCurrencyEET) + 0.42 * (maleCurrencyNEETAvail + maleCurrencyNEETNA)) AS [male16-18EETAdj], 

SUM (((maleNEETAvail + maleNEETNA) + (0.08 * maleCurrencyEET) + (0.58 * (maleCurrencyNEETAvail + maleCurrencyNEETNA))) +((maleEET + 0.92 * maleCurrencyEET) + 0.42 * (maleCurrencyNEETAvail + maleCurrencyNEETNA)))As [maleCohort16-18]

From male

Group By leadLEA,[District Name],rowstartdate

)



, [femaleCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((femaleNEETAvail + femaleNEETNA) + (0.08 * femaleCurrencyEET) + (0.58 * (femaleCurrencyNEETAvail + femaleCurrencyNEETNA))) AS [female16-18NEETAdj], 

SUM((femaleEET + 0.92 * femaleCurrencyEET) + 0.42 * (femaleCurrencyNEETAvail + femaleCurrencyNEETNA)) AS [female16-18EETAdj], 

SUM (((femaleNEETAvail + femaleNEETNA) + (0.08 * femaleCurrencyEET) + (0.58 * (femaleCurrencyNEETAvail + femaleCurrencyNEETNA))) +((femaleEET + 0.92 * femaleCurrencyEET) + 0.42 * (femaleCurrencyNEETAvail + femaleCurrencyNEETNA)))As [femaleCohort16-18]

From female

Group By leadLEA,[District Name],rowstartdate

)



, [TMCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((TMNEETAvail + TMNEETNA) + (0.08 * TMCurrencyEET) + (0.58 * (TMCurrencyNEETAvail + TMCurrencyNEETNA))) AS [TM16-18NEETAdj], 

SUM((TMEET + 0.92 * TMCurrencyEET) + 0.42 * (TMCurrencyNEETAvail + TMCurrencyNEETNA)) AS [TM16-18EETAdj], 

SUM (((TMNEETAvail + TMNEETNA) + (0.08 * TMCurrencyEET) + (0.58 * (TMCurrencyNEETAvail + TMCurrencyNEETNA))) +((TMEET + 0.92 * TMCurrencyEET) + 0.42 * (TMCurrencyNEETAvail + TMCurrencyNEETNA)))As [TMCohort16-18]

From Teenmum

Group By leadLEA,[District Name],rowstartdate

)



, [SENCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((SENNEETAvail + SENNEETNA) + (0.08 * SENCurrencyEET) + (0.58 * (SENCurrencyNEETAvail + SENCurrencyNEETNA))) AS [SEN16-18NEETAdj], 

SUM((SENEET + 0.92 * SENCurrencyEET) + 0.42 * (SENCurrencyNEETAvail + SENCurrencyNEETNA)) AS [SEN16-18EETAdj], 

SUM (((SENNEETAvail + SENNEETNA) + (0.08 * SENCurrencyEET) + (0.58 * (SENCurrencyNEETAvail + SENCurrencyNEETNA))) +((SENEET + 0.92 * SENCurrencyEET) + 0.42 * (SENCurrencyNEETAvail + SENCurrencyNEETNA)))As [SENCohort16-18]

From SEN

Group By leadLEA,[District Name],rowstartdate

)



, [SAplusCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((SAplusNEETAvail + SAplusNEETNA) + (0.08 * SAplusCurrencyEET) + (0.58 * (SAplusCurrencyNEETAvail + SAplusCurrencyNEETNA))) AS [SAplus16-18NEETAdj], 

SUM((SAplusEET + 0.92 * SAplusCurrencyEET) + 0.42 * (SAplusCurrencyNEETAvail + SAplusCurrencyNEETNA)) AS [SAplus16-18EETAdj], 

SUM (((SAplusNEETAvail + SAplusNEETNA) + (0.08 * SAplusCurrencyEET) + (0.58 * (SAplusCurrencyNEETAvail + SAplusCurrencyNEETNA))) +((SAplusEET + 0.92 * SAplusCurrencyEET) + 0.42 * (SAplusCurrencyNEETAvail + SAplusCurrencyNEETNA)))As [SAplusCohort16-18]

From SAplus

Group By leadLEA,[District Name],rowstartdate

)



, [SACohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((SANEETAvail + SANEETNA) + (0.08 * SACurrencyEET) + (0.58 * (SACurrencyNEETAvail + SACurrencyNEETNA))) AS [SA16-18NEETAdj], 

SUM((SAEET + 0.92 * SACurrencyEET) + 0.42 * (SACurrencyNEETAvail + SACurrencyNEETNA)) AS [SA16-18EETAdj], 

SUM (((SANEETAvail + SANEETNA) + (0.08 * SACurrencyEET) + (0.58 * (SACurrencyNEETAvail + SACurrencyNEETNA))) +((SAEET + 0.92 * SACurrencyEET) + 0.42 * (SACurrencyNEETAvail + SACurrencyNEETNA)))As [SACohort16-18]

From SA

Group By leadLEA,[District Name],rowstartdate

)



, [CareLvrCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((CareLvrNEETAvail + CareLvrNEETNA) + (0.08 * CareLvrCurrencyEET) + (0.58 * (CareLvrCurrencyNEETAvail + CareLvrCurrencyNEETNA))) AS [CareLvr16-18NEETAdj], 

SUM((CareLvrEET + 0.92 * CareLvrCurrencyEET) + 0.42 * (CareLvrCurrencyNEETAvail + CareLvrCurrencyNEETNA)) AS [CareLvr16-18EETAdj], 

SUM (((CareLvrNEETAvail + CareLvrNEETNA) + (0.08 * CareLvrCurrencyEET) + (0.58 * (CareLvrCurrencyNEETAvail + CareLvrCurrencyNEETNA))) +((CareLvrEET + 0.92 * CareLvrCurrencyEET) + 0.42 * (CareLvrCurrencyNEETAvail + CareLvrCurrencyNEETNA)))As [CareLvrCohort16-18]

From CareLvr

Group By leadLEA,[District Name],rowstartdate

)



, [YngOffCohort16-18] AS(

Select LeadLEA,[District Name],Rowstartdate, 

SUM((YngOffNEETAvail + YngOffNEETNA) + (0.08 * YngOffCurrencyEET) + (0.58 * (YngOffCurrencyNEETAvail + YngOffCurrencyNEETNA))) AS [YngOff16-18NEETAdj], 

SUM((YngOffEET + 0.92 * YngOffCurrencyEET) + 0.42 * (YngOffCurrencyNEETAvail + YngOffCurrencyNEETNA)) AS [YngOff16-18EETAdj], 

SUM (((YngOffNEETAvail + YngOffNEETNA) + (0.08 * YngOffCurrencyEET) + (0.58 * (YngOffCurrencyNEETAvail + YngOffCurrencyNEETNA))) +((YngOffEET + 0.92 * YngOffCurrencyEET) + 0.42 * (YngOffCurrencyNEETAvail + YngOffCurrencyNEETNA)))As [YngOffCohort16-18]

From YngOff

Group By leadLEA,[District Name],rowstartdate

)



, CTE as (

SELECT     

[TotalCohort16-18].Leadlea,

[TotalCohort16-18].[District Name],

[TotalCohort16-18].[RowStartDate],

[TotalCohort16-18].[Total16-18NEETAdj],

[TotalCohort16-18].[Total16-18EETAdj], 

[TotalCohort16-18].[TotalCohort16-18], 

[TotalCohort16-18].[Total16-18NEETAdj]/[TotalCohort16-18] AS NEETAdj, 

[maleCohort16-18].[male16-18NEETAdj],

([maleCohort16-18].[male16-18NEETAdj]/[maleCohort16-18].[maleCohort16-18]) As maleNEETAdj,

[femaleCohort16-18].[female16-18NEETAdj],

([femaleCohort16-18].[female16-18NEETAdj]/[femaleCohort16-18].[femaleCohort16-18]) As femaleNEETAdj,

[TMCohort16-18].[TM16-18NEETAdj],

([TMCohort16-18].[TM16-18NEETAdj]/[TMCohort16-18].[TMCohort16-18]) As TMNEETAdj,

[SENCohort16-18].[SEN16-18NEETAdj],

([SENCohort16-18].[SEN16-18NEETAdj]/[SENCohort16-18].[SENCohort16-18]) As SENNEETAdj,

[SAplusCohort16-18].[SAplus16-18NEETAdj],

([SAplusCohort16-18].[SAplus16-18NEETAdj]/[SAplusCohort16-18].[SAplusCohort16-18]) As SAplusNEETAdj,

[SACohort16-18].[SA16-18NEETAdj],

([SACohort16-18].[SA16-18NEETAdj]/[SACohort16-18].[SACohort16-18]) As SANEETAdj,

[CareLvrCohort16-18].[CareLvr16-18NEETAdj],

Case When [CareLvrCohort16-18].[CareLvr16-18NEETAdj]=0 then 0 else([CareLvrCohort16-18].[CareLvr16-18NEETAdj]/[CareLvrCohort16-18].[CareLvrCohort16-18]) End As CareLvrNEETAdj,

[YngOffCohort16-18].[YngOff16-18NEETAdj],

([YngOffCohort16-18].[YngOff16-18NEETAdj]/[YngOffCohort16-18].[YngOffCohort16-18]) As YngOffNEETAdj



FROM         

[TotalCohort16-18]

left outer join [maleCohort16-18] ON [TotalCohort16-18].[District Name]=[maleCohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[maleCohort16-18].[RowStartDate]

left outer join [femaleCohort16-18] ON [TotalCohort16-18].[District Name]=[femaleCohort16-18].[District Name] 

								  and [TotalCohort16-18].[RowStartDate]=[femaleCohort16-18].[RowStartDate]

left outer join [TMCohort16-18] ON [TotalCohort16-18].[District Name]=[TMCohort16-18].[District Name] 

								  and [TotalCohort16-18].[RowStartDate]=[TMCohort16-18].[RowStartDate]

left outer join [SENCohort16-18] ON [TotalCohort16-18].[District Name]=[SENCohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[SENCohort16-18].[RowStartDate]

left outer join [SAplusCohort16-18] ON [TotalCohort16-18].[District Name]=[SAplusCohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[SAplusCohort16-18].[RowStartDate]

left outer join [SACohort16-18] ON [TotalCohort16-18].[District Name]=[SACohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[SACohort16-18].[RowStartDate]

left outer join [CareLvrCohort16-18] ON [TotalCohort16-18].[District Name]=[CareLvrCohort16-18].[District Name]

								  and [TotalCohort16-18].[RowStartDate]=[CareLvrCohort16-18].[RowStartDate]

left outer join [YngOffCohort16-18] ON [TotalCohort16-18].[District Name]=[YngOffCohort16-18].[District Name]

								and [TotalCohort16-18].[RowStartDate]=[YngOffCohort16-18].[RowStartDate]

GROUP BY   

[TotalCohort16-18].Leadlea,

[TotalCohort16-18].[District Name],

[TotalCohort16-18].[RowStartDate],

[TotalCohort16-18].[Total16-18NEETAdj],

[TotalCohort16-18].[Total16-18EETAdj], 

[TotalCohort16-18].[TotalCohort16-18],

[maleCohort16-18].[male16-18NEETAdj],

[maleCohort16-18].[maleCohort16-18],

[femaleCohort16-18].[female16-18NEETAdj],

[femaleCohort16-18].[femaleCohort16-18],

[TMCohort16-18].[TM16-18NEETAdj],

[TMCohort16-18].[TMCohort16-18],

[SENCohort16-18].[SEN16-18NEETAdj],

[SENCohort16-18].[SENCohort16-18],

[SAplusCohort16-18].[SAplus16-18NEETAdj],

[SAplusCohort16-18].[SAplusCohort16-18],

[SACohort16-18].[SA16-18NEETAdj],

[SACohort16-18].[SACohort16-18],

[CareLvrCohort16-18].[CareLvr16-18NEETAdj],

[CareLvrCohort16-18].[CareLvrCohort16-18],

[YngOffCohort16-18].[YngOff16-18NEETAdj],

[YngOffCohort16-18].[YngOffCohort16-18]

)



select 

	a.Leadlea,

	a.[District Name],

	a.[RowStartDate],

	b.[RowStartDate] as [EndDate],

	a.[Total16-18NEETAdj],

	b.[Total16-18NEETAdj] as [Total16-18NEETAdj - end],

	a.[Total16-18NEETAdj] - b.[Total16-18NEETAdj] as calcdiff,



	a.[male16-18NEETAdj],

	b.[male16-18NEETAdj] as [male16-18NEETAdj - end],

	a.[male16-18NEETAdj] - 	b.[male16-18NEETAdj] as malecalcdiff,



	a.[female16-18NEETAdj],

	b.[female16-18NEETAdj] as [female16-18NEETAdj - end],

	a.[female16-18NEETAdj] - b.[female16-18NEETAdj] as femalecalcdiff,



	... and so on (please complete in the same way)...





from CTE a

inner join CTE b on a.Leadlea = b.Leadlea and a.[District Name] = b.[DistrictName] and a.RowStartDate = @date and b.RowStartDate = @date1

Open in new window

0
 

Author Closing Comment

by:BelizeMelanie
ID: 33610201
Brilliant, i have got that working. I got errors about the parameters being in twice so i removed them from the very first query and only used them at the end and it works fine.
THANKS so much.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now