I spend a lot of my time rewriting queries, so today I though I would document my process. We start with this query:
Select A.*
,1 as Vægtet
,'Betalende' as rtype
From
(
SELECT AA.DT, CASE WHEN AA.afdeling='HK/Hovedstaden' AND Fsektor = 'HK/Privat' THEN aa.Asektor else aa.afdeling END as afdeling, AA.KTG_ID, aa.nvn, aa.AFD_SEKTOR_ID, aa.Asektor, aa.Fsektor, aa.hovedopdeling, aa.ERHVERVSAKTIVE, aa.Betalende, aa.afd_sortering, aa.antal_medlemmer_1mdr, aa.antal_medlemmer_2mdr, aa.antal_medlemmer_ult, aa.blb_1mdr,
(SUM(aa.blb_1mdr) * (aa.antal_medlemmer_1mdr)) - (SUM(aa.blb_ult) * (aa.antal_medlemmer_ult))as kr
FROM
(
SELECT convert(nvarchar(10),max(a.dt),105) as DT, a.afdeling, a.KTG_ID, a.nvn, a.AFD_SEKTOR_ID, a.Asektor, a.Fsektor, CASE WHEN a.KTG_ID IN (1910,1914) THEN 'Fritidsjob u. 25 år' ELSE a.hovedopdeling END as hovedopdeling, a.ERHVERVSAKTIVE, a.Betalende,
CASE WHEN a.afdeling = 'HK/Hovedstaden' THEN 1 WHEN a.afdeling= 'HK/Midt' THEN 2 WHEN a.afdeling = 'HK/Sjælland' THEN 3 WHEN a.afdeling = 'HK/Sydjylland' THEN 4 WHEN a.afdeling = 'HK/Nordjylland' THEN 5 WHEN a.afdeling = 'HK/Midtvest' THEN 6 WHEN a.afdeling = 'HK/Østjylland' THEN 7 WHEN a.afdeling = 'HK/Post og Kommunikation' THEN 8 WHEN a.afdeling = 'HK/Trafik og Jernbane' THEN 9 ELSE 99 END as afd_sortering,
SUM(a.antal_medlemmer_1mdr) as antal_medlemmer_1mdr, SUM(a.antal_medlemmer_2mdr) as antal_medlemmer_2mdr, SUM(a.antal_medlemmer_ult) as antal_medlemmer_ult,
SUM(a.blb_1mdr) as blb_1mdr, SUM(a.blb_2mdr) as blb_2mdr, SUM(a.blb_ult) as blb_ult
FROM
(
SELECT a.DT, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, b.blb as blb_1mdr, 0 as blb_2mdr, 0 as blb_ult, COUNT(*) as antal_medlemmer_1mdr, 0 as antal_medlemmer_2mdr, 0 as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select flag, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where flag=1 and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by ktg_id, flag,afd_sektor_id) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id = b.afd_sektor_id --and a.FLAG=b.flag
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
where DT=(select max(dt) from lr..medlem) and a.MEDL_FORB=1 --and a.ERHVERVSAKTIVE=1
group by a.DT, a.KTG_ID, a.AAR, a.MAANED, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb
UNION ALL
SELECT a.dt, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, 0 as blb_1mdr, b.blb as blb_2mdr, 0 as blb_ult, 0 as antal_medlemmer_1mdr, COUNT(*) as antal_medlemmer_2mdr, 0 as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select dt, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where flag=2 and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by dt, ktg_id, afd_sektor_id ) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id = b.afd_sektor_id
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
where a.dt=(select dt from lr.dbo.tKalender_statistik where flag=1) and a.MEDL_FORB=1 -- and a.ERHVERVSAKTIVE=1
group by a.dt, a.KTG_ID, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE,a.AAR, a.MAANED, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb
UNION ALL
SELECT a.dt, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, 0 as blb_1mdr, 0 as blb_2mdr, b.blb as blb_ult, 0 as antal_medlemmer_1mdr, 0 as antal_medlemmer_2mdr, COUNT(*) as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select dt, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where dt = (SELECT Dateadd(dd,-1,DATEADD(MILLISECOND, -1, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)))) and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by dt, ktg_id, afd_sektor_id) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id = b.afd_sektor_id
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
where a.dt=(SELECT Dateadd(dd,-1,DATEADD(MILLISECOND, -1, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)))) and a.MEDL_FORB=1 -- and a.ERHVERVSAKTIVE=1
group by a.dt, a.KTG_ID, c.nvn, c.hovedopdeling,a.ERHVERVSAKTIVE ,a.AAR, a.MAANED, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb
) A
group by a.afdeling, a.KTG_ID, a.nvn, a.AFD_SEKTOR_ID, a.Asektor, a.Fsektor, CASE WHEN a.KTG_ID IN (1910,1914) THEN 'Fritidsjob u. 25 år' ELSE a.hovedopdeling END, a.ERHVERVSAKTIVE, a.Betalende,
CASE WHEN a.afdeling = 'HK/Hovedstaden' THEN 1 WHEN a.afdeling= 'HK/Midt' THEN 2 WHEN a.afdeling = 'HK/Sjælland' THEN 3 WHEN a.afdeling = 'HK/Sydjylland' THEN 4 WHEN a.afdeling = 'HK/Nordjylland' THEN 5 WHEN a.afdeling = 'HK/Midtvest' THEN 6 WHEN a.afdeling = 'HK/Østjylland' THEN 7 WHEN a.afdeling = 'HK/Post og Kommunikation' THEN 8 WHEN a.afdeling = 'HK/Trafik og Jernbane' THEN 9 ELSE 99 END
) AA
where aa.asektor <> '?' and aa.afdeling not in ('HK/Trafik og Jernbane', 'HK/Post og kommunikation')
GROUP BY AA.DT, AA.afdeling, AA.KTG_ID, aa.nvn,aa.AFD_SEKTOR_ID, aa.Asektor, aa.Fsektor, aa.hovedopdeling, aa.ERHVERVSAKTIVE, aa.Betalende, aa.afd_sortering, aa.antal_medlemmer_1mdr, aa.antal_medlemmer_2mdr, aa.antal_medlemmer_ult, aa.blb_1mdr
) A
--Cross join (SELECT Max([dt]) as dt FROM [LR].[dbo].[factKontigent_Blb]) B
--left join (SELECT [dt], [ktg_id], [afd_sektor_id], [Forbund],[Afdeling],[Sektor],[Fuldtid_Forbund],[Fuldtid_Afdeling],[Fuldtid_Sektor] FROM [LR].[dbo].[factKontigent_Blb] Where dt =(SELECT Max([dt]) as dt FROM [LR].[dbo].[factKontigent_Blb]))
--C on A.KTG_ID = C.ktg_id and A.AFD_SEKTOR_ID = C.[afd_sektor_id]
union all
Select A.DT ,CASE WHEN A.afdeling='HK/Hovedstaden' THEN a.Asektor else a.afdeling END as afdeling,A.KTG_ID,A.nvn ,A.AFD_SEKTOR_ID,A.Asektor,A.Fsektor,A.hovedopdeling,A.ERHVERVSAKTIVE,A.Betalende,A.afd_sortering
,Case isnull(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor],0) when 0 then 0 else (C.[Forbund]+C.[Afdeling]+C.[Sektor])/(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor]) end * A.antal_medlemmer_1mdr as antal_medlemmer_1mdr
,Case isnull(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor],0) when 0 then 0 else (C.[Forbund]+C.[Afdeling]+C.[Sektor])/(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor]) end * A.antal_medlemmer_2mdr as antal_medlemmer_2mdr
,Case isnull(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor],0) when 0 then 0 else (C.[Forbund]+C.[Afdeling]+C.[Sektor])/(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor]) end * A.antal_medlemmer_ult as antal_medlemmer_ult
,A.blb_1mdr
,A.kr
,Case isnull(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor],0) when 0 then 0 else (C.[Forbund]+C.[Afdeling]+C.[Sektor])/(C.[Fuldtid_Forbund]+C.[Fuldtid_Afdeling]+C.[Fuldtid_Sektor]) end as Vægtetmedlem
,'Fuldtidsvægtede' as rtype
From
(
SELECT AA.DT, AA.afdeling, AA.KTG_ID, aa.nvn, aa.AFD_SEKTOR_ID, aa.Asektor, aa.Fsektor, aa.hovedopdeling, aa.ERHVERVSAKTIVE, aa.Betalende, aa.afd_sortering, aa.antal_medlemmer_1mdr, aa.antal_medlemmer_2mdr, aa.antal_medlemmer_ult, aa.blb_1mdr,
(SUM(aa.blb_1mdr) * (aa.antal_medlemmer_1mdr)) - (SUM(aa.blb_ult) * (aa.antal_medlemmer_ult))as kr
FROM
(
SELECT convert(nvarchar(10),max(a.dt),105) as DT, a.afdeling, a.KTG_ID, a.nvn, a.AFD_SEKTOR_ID, a.Asektor, a.Fsektor, CASE WHEN a.KTG_ID IN (1910,1914) THEN 'Fritidsjob u. 25 år' ELSE a.hovedopdeling END as hovedopdeling, a.ERHVERVSAKTIVE, a.Betalende,
CASE WHEN a.afdeling = 'HK/Hovedstaden' THEN 1 WHEN a.afdeling= 'HK/Midt' THEN 2 WHEN a.afdeling = 'HK/Sjælland' THEN 3 WHEN a.afdeling = 'HK/Sydjylland' THEN 4 WHEN a.afdeling = 'HK/Nordjylland' THEN 5 WHEN a.afdeling = 'HK/Midtvest' THEN 6 WHEN a.afdeling = 'HK/Østjylland' THEN 7 WHEN a.afdeling = 'HK/Post og Kommunikation' THEN 8 WHEN a.afdeling = 'HK/Trafik og Jernbane' THEN 9 ELSE 99 END as afd_sortering,
SUM(a.antal_medlemmer_1mdr) as antal_medlemmer_1mdr, SUM(a.antal_medlemmer_2mdr) as antal_medlemmer_2mdr, SUM(a.antal_medlemmer_ult) as antal_medlemmer_ult,
SUM(a.blb_1mdr) as blb_1mdr, SUM(a.blb_2mdr) as blb_2mdr, SUM(a.blb_ult) as blb_ult
FROM
(
SELECT a.DT, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, b.blb as blb_1mdr, 0 as blb_2mdr, 0 as blb_ult, COUNT(*) as antal_medlemmer_1mdr, 0 as antal_medlemmer_2mdr, 0 as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select flag, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where flag=1 and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by ktg_id, flag,afd_sektor_id) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id = b.afd_sektor_id --and a.FLAG=b.flag
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
where DT=(select max(dt) from lr..medlem) and a.MEDL_FORB=1 --and a.ERHVERVSAKTIVE=1
group by a.DT, a.KTG_ID, a.AAR, a.MAANED, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb
UNION ALL
SELECT a.dt, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, 0 as blb_1mdr, b.blb as blb_2mdr, 0 as blb_ult, 0 as antal_medlemmer_1mdr, COUNT(*) as antal_medlemmer_2mdr, 0 as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select dt, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where flag=2 and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by dt, ktg_id, afd_sektor_id ) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id = b.afd_sektor_id
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
where a.dt=(select dt from lr.dbo.tKalender_statistik where flag=1) and a.MEDL_FORB=1 --and a.ERHVERVSAKTIVE=1
group by a.dt, a.KTG_ID, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE,a.AAR, a.MAANED, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb
UNION ALL
SELECT a.dt, a.AAR, a.MAANED, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, case when b.blb > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende, 0 as blb_1mdr, 0 as blb_2mdr, b.blb as blb_ult, 0 as antal_medlemmer_1mdr, 0 as antal_medlemmer_2mdr, COUNT(*) as antal_medlemmer_ult
from lr.dbo.Medlem a
left join (select dt, ktg_id, SUM(blb) as blb, afd_sektor_id from [LR].[dbo].[tKontingentBlb] where dt = (SELECT Dateadd(dd,-1,DATEADD(MILLISECOND, -1, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)))) and beskrivelse in ('Forbund', 'Afdeling','Sektor') group by dt, ktg_id, afd_sektor_id) b on a.KTG_ID=b.ktg_id and a.afd_sektor_id = b.afd_sektor_id
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
where a.dt=(SELECT Dateadd(dd,-1,DATEADD(MILLISECOND, -1, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)))) and a.MEDL_FORB=1 --and a.ERHVERVSAKTIVE=1
group by a.dt, a.KTG_ID, c.nvn, c.hovedopdeling,a.ERHVERVSAKTIVE ,a.AAR, a.MAANED, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, b.blb
) A
group by a.afdeling, a.KTG_ID, a.nvn, a.AFD_SEKTOR_ID, a.Asektor, a.Fsektor, CASE WHEN a.KTG_ID IN (1910,1914) THEN 'Fritidsjob u. 25 år' ELSE a.hovedopdeling END, a.ERHVERVSAKTIVE, a.Betalende,
CASE WHEN a.afdeling = 'HK/Hovedstaden' THEN 1 WHEN a.afdeling= 'HK/Midt' THEN 2 WHEN a.afdeling = 'HK/Sjælland' THEN 3 WHEN a.afdeling = 'HK/Sydjylland' THEN 4 WHEN a.afdeling = 'HK/Nordjylland' THEN 5 WHEN a.afdeling = 'HK/Midtvest' THEN 6 WHEN a.afdeling = 'HK/Østjylland' THEN 7 WHEN a.afdeling = 'HK/Post og Kommunikation' THEN 8 WHEN a.afdeling = 'HK/Trafik og Jernbane' THEN 9 ELSE 99 END
) AA
where aa.asektor <> '?' and aa.afdeling not in ('HK/Trafik og Jernbane', 'HK/Post og kommunikation')
GROUP BY AA.DT, AA.afdeling, AA.KTG_ID, aa.nvn,aa.AFD_SEKTOR_ID, aa.Asektor, aa.Fsektor, aa.hovedopdeling, aa.ERHVERVSAKTIVE, aa.Betalende, aa.afd_sortering, aa.antal_medlemmer_1mdr, aa.antal_medlemmer_2mdr, aa.antal_medlemmer_ult, aa.blb_1mdr
) A
--Cross join (SELECT Max([dt]) as dt FROM [LR].[dbo].[factKontigent_Blb]) B
left join (SELECT [dt], [ktg_id], [afd_sektor_id], [Forbund],[Afdeling],[Sektor],[Fuldtid_Forbund],[Fuldtid_Afdeling],[Fuldtid_Sektor] FROM [LR].[dbo].[factKontigent_Blb] Where dt =(SELECT Max([dt]) as dt FROM [LR].[dbo].[factKontigent_Blb]))
C on A.KTG_ID = C.ktg_id and A.AFD_SEKTOR_ID = C.[afd_sektor_id]
Where antal_medlemmer_1mdr>0 or antal_medlemmer_2mdr > 0 or antal_medlemmer_ult > 0
This query is central to a few dashboards but I needed to make some changes and I would also like it to load faster, currently it takes 39 seconds. I skimmed the entire query, but to make changes I just start from the top. Open a new query windows and make a copy so I can compare my changes query results to the current ones.Starting at the top the first 4 lines simply add two columns with fixed values "1, 'Betalende'", so I remove that outer query and add it to the first inner query starting at line 6. I also removed the mathching ending on line 75.
Next I looked at the many unioned queries starting at line 26, they seem very similar. Looking closer they select the same thing, except the last six values where they are used to sum one thing at a time, and then the outer queries collapses the rows using sum. It seems this would be better handled by a single select statement with conditional sums. I started writing my conditional sums, one thing that helped a lot is to always look at the "raw" data, that's just select * without the aggregates and looking at the rows. In the end i had constructed this:
SELECT max(a.dt)
, a.KTG_ID, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor, c.nvn, c.hovedopdeling
, a.ERHVERVSAKTIVE, case when sum(b.blb) > 0 THEN 'Betalende' ELSE 'Ikke betalende' END as Betalende
, sum( CASE WHEN flag = 0 THEN b.blb ELSE 0 END) as blb_1mdr
, sum( CASE WHEN flag = 1 THEN b.blb ELSE 0 END) as blb_2mdr
, sum( CASE WHEN a.Dt = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN b.blb ELSE 0 END) as blb_ult
, COUNT(DISTINCT CASE WHEN flag = 0 THEN PRS_ID ELSE NULL END) as antal_medlemmer_1mdr
, COUNT( DISTINCT CASE WHEN flag = 1 THEN PRS_ID ELSE NULL END) as antal_medlemmer_2mdr
, COUNT( DISTINCT CASE WHEN a.Dt = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN PRS_ID ELSE NULL END) as antal_medlemmer_ult
, CASE WHEN COUNT(distinct case when flag = 0 then Beskrivelse else null end) = 0 THEN 0 ELSE SUM( CASE WHEN FLAG = 0 THEN fuld_vægt ELSE 0 END)/COUNT(distinct case when flag = 0 then Beskrivelse else null end)END
, CASE WHEN COUNT(distinct case when flag = 1 then Beskrivelse else null end) = 0 THEN 0 ELSE SUM( CASE WHEN FLAG = 1 THEN fuld_vægt ELSE 0 END)/COUNT(distinct case when flag = 1 then Beskrivelse else null end) END
, CASE WHEN COUNT(distinct case when a.DT = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) then Beskrivelse else null end) = 0 THEN 0 ELSE SUM( CASE WHEN a.DT = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN fuld_vægt ELSE 0 END)/COUNT(distinct case when a.DT = convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) then Beskrivelse else null end) END
from lr.dbo.Medlem a
left join [LR].[dbo].[tKontingentBlb] b on a.KTG_ID=b.ktg_id and a.afd_sektor_id = b.afd_sektor_id and a.DT = b.dt
left join lr.dbo.tkontingent c on a.KTG_ID=c.ktg_id
left join lr.dbo.tAfdeling d on a.KRDS_ID=d.krds_id
left join lr.dbo.tAsektor e on a.AFD_SEKTOR_ID=e.afd_sektor_id
left join lr.dbo.tFsektor f on a.FORB_SEKTOR_ID=F.forb_sektor_id
OUTER APPLY (SELECT top 1 CASE WHEN sum([Fuldtid_Forbund]+[Fuldtid_Afdeling]+[Fuldtid_Sektor]) = 0 THEN 0 ELSE
sum(CAST([Forbund]+[Afdeling]+[Sektor] AS FLOAT)/([Fuldtid_Forbund]+[Fuldtid_Afdeling]+[Fuldtid_Sektor])) END as fuld_vægt
FROM [LR].[dbo].[factKontigent_Blb]
where ktg_id= A.KTG_ID and [afd_sektor_id]= A.AFD_SEKTOR_ID and
dt = a.DT ) ktg_fact
where (a.DT>=DATEADD(M,-1,GETDATE()) OR a.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105)) and a.MEDL_FORB=1 and beskrivelse in ('Forbund', 'Afdeling','Sektor') and a.dt = b.dt
group by a.KTG_ID,c.nvn, c.hovedopdeling, a.ERHVERVSAKTIVE, d.afdeling, a.AFD_SEKTOR_ID, e.Asektor, f.Fsektor,fuld_vægt
order by ktg_id,afd_sektor_id,afdeling
While this is much more compact, it's still not very readable and it turns out the wrong results. The summing simply is too complicated and too many strange constrains across the rows apply. Time to start over. In my second approach i first select the data i am looking for a into a temporary table and a CTE and the abuse the PIVOT command to get the summations as needed. Notice that the aggragation in my last select are necessary for the use of more than one PIVOT statement:
/*
Datagrundlag til medlemsdelen af det fælles dashboard
Author : Johan Sivertsen
==================================
Kontingentoplysninger: Samme som viewet factkontingentblb men her valgt kun for de tre relevante måneder
*/
SELECT Kontigent.*, Fuldtid.Forbund AS [Fuldtid_Forbund], Fuldtid.Afdeling AS [Fuldtid_Afdeling], Fuldtid.Sektor AS [Fuldtid_Sektor],
Fuldtid.[AkasseAdm] AS [Fuldtid_AkasseAdm] into #kont_fact
FROM (SELECT dt, aar, maaned, ktg_id, afd_sektor_id, sum(isnull([Forbund], 0)) AS [Forbund], sum(isnull([Afdeling], 0)) AS [Afdeling], sum(isnull([Sektor], 0))
AS [Sektor], Sum(isnull([AkasseAdm], 0)) AS [AkasseAdm]
FROM (SELECT Kontingent.dt, Kontingent.aar, Kontingent.maaned, Kontingent.ktg_id, Kontingent.afd_sektor_id, Kontingent.beskrivelse,
Kontingent.blb, fuldtid.fblb, CASE fuldtid.fblb WHEN 0 THEN 0 ELSE cast(Kontingent.blb AS FLOAT) / cast(fuldtid.fblb AS FLOAT)
END AS fa
FROM (SELECT tKontingentBlb_alle.aar, tKontingentBlb_alle.maaned, blb AS fblb, beskrivelse, afd_sektor_id
FROM dbo.tKontingentBlb_alle
WHERE ((tKontingentBlb_alle.ktg_id = 1110) AND beskrivelse IN ('Sektor', 'Afdeling', 'Forbund')) OR
((tKontingentBlb_alle.ktg_id = 2110) AND beskrivelse IN ('AkasseAdm'))
GROUP BY tKontingentBlb_alle.aar, tKontingentBlb_alle.maaned, tKontingentBlb_alle.blb, tKontingentBlb_alle.afd_sektor_id,
tKontingentBlb_alle.beskrivelse) AS fuldtid INNER JOIN
tKontingentBlb_alle AS Kontingent ON fuldtid.aar = Kontingent.aar AND fuldtid.maaned = Kontingent.maaned AND
fuldtid.afd_sektor_id = Kontingent.afd_sektor_id AND fuldtid.beskrivelse = Kontingent.beskrivelse) AS A PIVOT (max(blb) FOR
beskrivelse IN ([Forbund], [Afdeling], [Sektor], [AkasseAdm])) AS pvt
GROUP BY dt, aar, maaned, ktg_id, afd_sektor_id) AS Kontigent INNER JOIN
(SELECT dt, aar, maaned, ktg_id, afd_sektor_id, sum(isnull([Forbund], 0)) AS [Forbund], sum(isnull([Afdeling], 0)) AS [Afdeling], sum(isnull([Sektor],
0)) AS [Sektor], Sum(isnull([AkasseAdm], 0)) AS [AkasseAdm]
FROM (SELECT Kontingent.dt, Kontingent.aar, Kontingent.maaned, Kontingent.ktg_id, Kontingent.afd_sektor_id, Kontingent.beskrivelse,
Kontingent.blb, fuldtid.fblb, CASE fuldtid.fblb WHEN 0 THEN 0 ELSE cast(Kontingent.blb AS FLOAT) / cast(fuldtid.fblb AS FLOAT)
END AS fa
FROM (SELECT tKontingentBlb_alle.aar, tKontingentBlb_alle.maaned, blb AS fblb, beskrivelse, afd_sektor_id
FROM dbo.tKontingentBlb_alle
WHERE ((tKontingentBlb_alle.ktg_id = 1110) AND beskrivelse IN ('Sektor', 'Afdeling', 'Forbund')) OR
((tKontingentBlb_alle.ktg_id = 2110) AND beskrivelse IN ('AkasseAdm'))
GROUP BY tKontingentBlb_alle.aar, tKontingentBlb_alle.maaned, tKontingentBlb_alle.blb, tKontingentBlb_alle.afd_sektor_id,
tKontingentBlb_alle.beskrivelse) AS fuldtid INNER JOIN
tKontingentBlb_alle AS Kontingent ON fuldtid.aar = Kontingent.aar AND fuldtid.maaned = Kontingent.maaned AND
fuldtid.afd_sektor_id = Kontingent.afd_sektor_id AND fuldtid.beskrivelse = Kontingent.beskrivelse) AS A PIVOT (max(fblb) FOR
beskrivelse IN ([Forbund], [Afdeling], [Sektor], [AkasseAdm])) AS pvt
WHERE (DT>=DATEADD(M,-1,GETDATE()) OR DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105))
GROUP BY dt, aar, maaned, ktg_id, afd_sektor_id) AS Fuldtid ON Kontigent.dt = Fuldtid.dt AND Kontigent.ktg_id = Fuldtid.ktg_id AND
Kontigent.afd_sektor_id = Fuldtid.afd_sektor_id;
/*
====================== # Medlem ===============
Danner medlemsoplysninger - Bruger WITH da dette er den tungeste af de to midlertidige tabeller
*/
WITH medlem AS(
SELECT medlem.dt,medlem.prs_id, medlem.KTG_ID, afd.afdeling, medlem.AFD_SEKTOR_ID, Asek.Asektor, Fsek.Fsektor
FROM LR.dbo.medlem medlem
left join lr.dbo.tAfdeling afd on medlem.krds_id = afd.krds_id
left join lr.dbo.tAsektor Asek on medlem.afd_sektor_id = Asek.afd_sektor_id
left join lr.dbo.tFsektor Fsek on medlem.FORB_SEKTOR_ID = Fsek.forb_sektor_id
WHERE (medlem.DT>=DATEADD(M,-1,GETDATE()) OR medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105)) --medlemmer fra de to seneste md samt ult. sidste år
and medlem.MEDL_Forb = 1 ) --medlemmer af forbundet)
/*Det hele klistres sammen med en lækker triple PIVOT, den første pivot opbruger egentlig den midlertidige tabel,
men hvis man undlader at kalde den(SourceTable) ved navn virker det. Grupperingen er nødvendig da PIVOT ikke ved
at de tre "id" nøgler egentlig alle peger samme opdeling af dt.
*/
SELECT afdeling, CASE WHEN Fsektor = 'HK/Privat' and afdeling = 'HK/Hovedstaden' THEN Asektor ELSE afdeling END as Afdeling_Privat,Fsektor,ktg_id,nvn,hovedopdeling, ---row
isnull(MAX([0]),0) as antal_dm,
isnull(MAX([1]),0) as antal_sm,
isnull(MAX([2]),0) as antal_ult,
isnull(MAX([3]),0) as ktg_dm,
isnull(MAX([4]),0) as ktg_sm,
isnull(MAX([5]),0) as ktg_ult,
Coalesce(MAX([6]),MAX([7]),MAX([8])) as fuldktg_dm,
Coalesce(MAX([7]),MAX([6]),MAX([8])) as fuldktg_sm,
Coalesce(MAX([8]),MAX([7]),MAX([6])) as fuldktg_ult,
ISNULL(MAX([9]),0) as fb_dm,
ISNULL(MAX([10]),0) as fb_sm,
ISNULL(MAX([11]),0) as fb_ult,
Coalesce(MAX([12]),MAX([9]),1) as fb_fuld_dm, --The coalesce avoid deviding with 0
Coalesce(MAX([13]),MAX([10]),1) as fb_fuld_sm,
Coalesce(MAX([14]),MAX([11]),1) as fb_fuld_ult,
CASE WHEN MAX([3])>0 THEN 1 ELSE 0 END AS Betalende
FROM(
SELECT CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 2 WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 1 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 0 ELSE NULL END as antal_id,
CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 5 WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 4 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 3 ELSE NULL END as blb_id,
CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 8 WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 7 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 6 ELSE NULL END as fuld_id,
CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 11 WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 10 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 9 ELSE NULL END as fb_id,
CASE WHEN medlem.DT =convert(datetime,'31-12-'+CAST(YEAR(GETDATE())-1 AS varchar),105) THEN 14 WHEN MONTH(medlem.DT) = MONTH(GETDATE())-1 THEN 13 WHEN MONTH(medlem.dt)=MONTH(GETDATE()) THEN 12 ELSE NULL END as fb_fuld_id,
medlem.prs_id,medlem.afdeling,medlem.Asektor,medlem.Fsektor,medlem.KTG_ID,kont_description.nvn,kont_description.hovedopdeling,
forbund+kont.afdeling+sektor as beløb,
Fuldtid_forbund+fuldtid_afdeling+fuldtid_sektor as Fuldt_beløb,
Forbund as fb_beløb,
Fuldtid_Forbund as fb_fuldt_beløb
FROM medlem
Left join #kont_fact as kont on medlem.KTG_ID=kont.ktg_id and medlem.AFD_SEKTOR_ID = kont.afd_sektor_id and medlem.DT = kont.dt
Left join tKontingent as kont_description on medlem.KTG_ID = kont_description.ktg_id) AS SourceTable
PIVOT
(
COUNT(SourceTable.prs_id)
FOR antal_id IN([0],[1],[2])
) AS PivotTable
PIVOT
(
SUM(beløb)
FOR blb_id IN([3],[4],[5])
) AS PivotTable
PIVOT
(
SUM(Fuldt_beløb)
FOR fuld_id IN([6],[7],[8])
) AS PivotTable
PIVOT
(
SUM(fb_beløb)
FOR fb_id IN ([9],[10],[11])
) AS PivotTable
PIVOT
(
SUM(fb_fuldt_beløb)
FOR fb_fuld_id IN ([12],[13],[14])
) AS PivotTable
GROUP BY afdeling, CASE WHEN Fsektor = 'HK/Privat' and afdeling = 'HK/Hovedstaden' THEN Asektor ELSE afdeling END,Fsektor,ktg_id,nvn,hovedopdeling
drop table #kont_fact
While this query is far from elegant it is still more compact and readable than what we started with, but it requires an understanding of the PIVOT function, and how to abuse it to pivot multiple colums. This is achieved by constructing multiple ID's and using aggregation. Not pretty, but it works. Best of all it runs in around 14 seconds, mostly because it does not rely on the factKontingentblb view, but just selects the necessary subsection into a tmp. table.
No comments:
Post a Comment