Solved

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

Posted on 2010-08-31
4
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Finding Datasets/StoredProcedures 4 53
SQL Rewrite without the NULLIF 4 33
Find unused columns in a table 12 92
SQL query 7 49
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

737 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