sqlserver查询七天的数据个数

with t as (
        select datename(weekday,getdate()- t1.number) weeknum
        ,substring(convert(varchar,getdate()- t1.number,120),1,11) daynum
        from (select number  from MASTER..spt_values WHERE TYPE='P' AND number >=0 and 6 >= number  ) t1 )
        select weeknum,daynum,

        (select count(*) from WorkSheetData where substring(convert(varchar,BillingDate,120),1,11)=daynum) as workCreateNumber,
        (select count(*) from WorkSheetData where substring(convert(varchar,BillingDate,120),1,11)=daynum AND CompleteStatus = '0') workCompleteNumber,
        (select count(*) from ProjectData where substring(convert(varchar,ProjectApprovalDate,120),1,11)=daynum) as projectCreateNumber,
        (select count(*) from ProjectData where substring(convert(varchar,ProjectApprovalDate,120),1,11)=daynum AND CompleteStatus = '0') as projectCompleteNumber
        from t
        ORDER BY daynum asc