How to calculate difference in sets of SQL query results based on parameters
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')
HELP!!!!
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')
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]
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
THANKS so much.
calculatedifference (@date'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.