Solved

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

Posted on 2010-08-31
4
280 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

726 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