sqlserver2008R2查询最近一周统计数据

WITH t AS (
        SELECT datename(weekday, getdate() - t1.number) AS weeknum
            , substring(convert(varchar, getdate() - t1.number, 120), 1, 11) AS daynum
        FROM (
            SELECT number
            FROM MASTER..spt_values
            WHERE number >= 1
                AND 7 >= number
        ) t1
    )
SELECT daynum, weeknum
    , (
        SELECT isnull(SUM(CAST(sale AS numeric(18, 2))), 0) AS sale
        FROM t_tanker
        WHERE substring(convert(varchar, date, 120), 1, 11) = daynum
    ) AS saleFROM t
GROUP BY daynum, weeknum
ORDER BY daynum ASC