Remove table variable from query

Question

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

Answer 1

There are a few things that come to mind...

1... SQL Server doesn't create statics for table valued parameters or table variables... It simply assumes a single row. It will, however, create them for temp tables. Using a temp table gives the optimizer a better chance of coming up with an appropriate execution plan. It will also allow you to create a clustered index on saledate.

2... This is just "YEAR(saledate) = '1999' AND MONTH(saledate) = '01'" is just painful. You're converting parts of a date to INTs and then comparing the INTs to strings. A more reasonable approach would be to simply do a DATEADD/DATEDIFF to set each value (see code below).

3... Since you're only doing calculations on values that have a saledate in the years on 1999 & 2000, add where clauses to your union query.

4... Better yet, get rid of the UNION ALL and simply pre-aggregate and do a join. That, combined with dumping the employeename altogether (you aren't using it in the final query), will allow to roll this all the way up to just 24 rows of data before you get to the temp table.

Something along these lines...

IF OBJECT_ID('tempdb..#Help', 'U') IS NOT NULL
DROP TABLE #Help;

CREATE TABLE #Help (
    saledate DATE NOT NULL PRIMARY KEY CLUSTERED,
    saletotal MONEY NULL, 
    taxamt MONEY NULL 
    );
INSERT #Help (saledate, saletotal, taxamt)
WITH 
    cte_sales AS (
        SELECT 
            sd.saledate,
            saletotal = SUM(ZT1.saletotal), 
        FROM 
            fs1 AS ZT1
            CROSS APPLY ( VALUES (DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01',  ZT1.saledate), '1900-01-01')) ) sd (saledate)
        WHERE 
            ZT1.saledate >= '19990101'
            AND saledate < '20010101'
        GROUP BY 
            ZT1.employeeName, 
            sd.saledate
        ),
    cte_taxes AS (
        SELECT 
            sd.saledate,
            taxamt = SUM(t1.txamt)
        FROM 
            freethree t1
            CROSS APPLY ( VALUES (DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01',  h.saledate), '1900-01-01')) ) sd (saledate)
        WHERE
            t1.saledate >= '19990101'
            t1 saledate < '20010101'
        GROUP BY 
            sd.saledate
        )
    SELECT 
        saledate = ISNULL(s.saledate = t.saledate)
        s.saletotal,
        t.taxamt
    FROM
        cte_sales s
        FULL JOIN cte_taxes t
            ON s.saledate = t.saledate;

DECLARE @Jan99Sales = SELECT h.saledate FROM #Help h WHERE h.saledate = '19990101'; -- no nee to 

SELECT      
    Jan99 = ROUND(CASE WHEN h.saledate = '19990101' THEN h.taxamt / NULLIF(h.saletotal, 0) * 100), 0),
    Jan00 = ROUND(CASE WHEN h.saledate = '20000101' THEN h.taxamt / @Jan99Sales * 100), 0),
    Feb99 = ROUND(CASE WHEN h.saledate = '19990102' THEN h.taxamt / NULLIF(h.saletotal, 0) * 100), 0),
    Feb00 = ROUND(CASE WHEN h.saledate = '20000102' THEN h.taxamt / @Jan99Sales * 100), 0),
    -- continue the pattenr...
FROM
    #Help h
ORDER BY 
    h.saledate;