I am running this query, and to get the calculation that I need, I use a table value parameter to house some of the data. Scanning roughly 50K rows to create the table value parameter then performing the calculation to return one single row of 24 results the query is completed in roughly 5 seconds, but I am sure there is a more efficient way to achieve the same end result.
How could this query be changed to produce better speed?
Declare @Help Table (employee varchar(500), saletotal float, taxamt float, saledate date)
INSERT INTO @Help
SELECT
ZT1.employeename,
SUM(NULLIF(ZT1.[saletotal],0)) AS saletotal,
0 As taxamt,
saledate As saledate
FROM fs1 AS ZT1
GROUP BY ZT1.employeeName, ZT1.saledate
UNION ALL
Select
t1.employeename,
0 As saletotal,
SUM(NULLIF(t1.txamt,0)) As taxamt,
saledate as saledate
FROM freethree t1
Group By t1.employeeName, t1.saledate
Select
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Jan99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '01' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Jan00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '02' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Feb99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '02' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Feb00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '03' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Mar99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '03' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Mar00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '04' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Apr99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '04' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Apr00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '05' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As May99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '05' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As May00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '06' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Jun99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '06' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Jun00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '07' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Jul99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '07' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Jul00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '08' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Aug99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '08' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Aug00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '09' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Sep99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '09' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Sep00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '10' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Oct99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '10' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Oct00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '11' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Nov99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '11' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Nov00,
ROUND(SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '12' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Dec99,
ROUND(SUM(case when YEAR(saledate) = '2000' AND MONTH(saledate) = '12' THEN COALESCE(saletotal,0)-COALESCE(taxamt,0) End)
/SUM(case when YEAR(saledate) = '1999' AND MONTH(saledate) = '01' THEN NULLIF(COALESCE(saletotal,0),0) end)*100,0) As Dec00
FROM @HELP