传入参数：

@Type：类型，是哪一种报表，有year,month,day三种

@Time:时间 

根据Type参数分别获取Time的月份数据，日期数据

declare @Type nvarchar(20) ='year';    declare @Time DateTime =getdate(); SELECT  distinct    case @Type when 'year' then format(dateadd(mm,number,@Time),'MM月')        when 'month' then format(dateadd(dd,number,@Time),'dd日')        else format(dateadd(dd,number,@Time),'dd日')         end DT    FROM master..spt_values        WHERE type='P' and  ((@Type='day' and number<1) or (@Type!='day' and number<50))

年的时候显示12个月

月的时候显示该月的天数

日的时候就显示该天

注意上面的日的时候，进行判断，number<1

先查出这个，然后与数据库中表left join

declare @Type nvarchar(20) ='month';    declare @Time DateTime =getdate();  select        case @Type when 'year' then format(CreateTime,'MM月')        when 'month' then format(CreateTime,'dd日')        else format(CreateTime,'dd日')         end DT2,Sum   (      isnull(ElectricalLaborHour,0)+      isnull(ElectricalParts,0)+      isnull(SheetSprayLaborHour,0)+      isnull(SheetSprayParts,0)+      isnull(SheetSprayTransLaborHour,0)+      isnull(OilChangeLaborHour,0)+      isnull(OilChangeParts,0)+      isnull(WarrantyLaborHour,0)+      isnull(WarrantyParts,0)+      isnull(WarrantyTransLaborHour,0)+      isnull(InternalElectricalLaborHour,0)+      isnull(InternalParts,0)+      isnull(InternalSheetSprayLaborHour,0)) as Total from T_DMSMaintenance where IsDelete=0 and        ((@Type='year' and datepart(yyyy,CreateTime)=datepart(yyyy,@Time)) or   (@Type='month' and format(CreateTime,'yyyy年MM月')=format(@Time,'yyyy年MM月')) or  (@Type='day' and format(CreateTime,'yyyy-MM-dd')=format(@Time,'yyyy-MM-dd')))   group by  case @Type when 'year' then format(CreateTime,'MM月')        when 'month' then format(CreateTime,'dd日')        else format(CreateTime,'dd日') end

看上面的查询条件

or,and联合使用，并且根据具体的Type参数进行分组

然后再将两个表进行连接

declare @Type nvarchar(20) ='month';  declare @Time DateTime =getdate();     select DT,Total from (SELECT  distinct    case @Type when 'year' then format(dateadd(mm,number,@Time),'MM月')        when 'month' then format(dateadd(dd,number,@Time),'dd日')        else format(dateadd(dd,number,@Time),'dd日')         end DT    FROM master..spt_values        WHERE type='P' and  ((@Type='day' and number<1) or (@Type!='day' and number<50))) as T1 left join   (select        case @Type when 'year' then format(CreateTime,'MM月')        when 'month' then format(CreateTime,'dd日')        else format(CreateTime,'dd日')         end DT2,Sum   (      isnull(ElectricalLaborHour,0)+      isnull(ElectricalParts,0)+      isnull(SheetSprayLaborHour,0)+      isnull(SheetSprayParts,0)+      isnull(SheetSprayTransLaborHour,0)+      isnull(OilChangeLaborHour,0)+      isnull(OilChangeParts,0)+      isnull(WarrantyLaborHour,0)+      isnull(WarrantyParts,0)+      isnull(WarrantyTransLaborHour,0)+      isnull(InternalElectricalLaborHour,0)+      isnull(InternalParts,0)+      isnull(InternalSheetSprayLaborHour,0)) as Total from T_DMSMaintenance where IsDelete=0 and        ((@Type='year' and datepart(yyyy,CreateTime)=datepart(yyyy,@Time)) or   (@Type='month' and format(CreateTime,'yyyy年MM月')=format(@Time,'yyyy年MM月')) or  (@Type='day' and format(CreateTime,'yyyy-MM-dd')=format(@Time,'yyyy-MM-dd')))   group by  case @Type when 'year' then format(CreateTime,'MM月')        when 'month' then format(CreateTime,'dd日')        else format(CreateTime,'dd日') end) as T2 on T1.DT=T2.DT2

 

 

 

例子：

--报表类型，年报，月报，日报    declare @Type nvarchar(20) ='month';     --需要查看的报表的时间，如果是年报就2016-01-01 月报就2016-08-01，因为2016-08不能转换成日期格式，只有年的话就可以的    declare @Time DateTime =cast('2016-02-24' as DateTime);    --销售员ID    declare @SalesID nvarchar(500)='F248611C-CB39-4806-919E-71DCD085D208';    --公司ID    declare @CompanyID nvarchar(500)='C04B3AE6-1866-4490-9D73-FAB0DB121F12';    --按报表类型查询出x轴数据with ta as (   SELECT  distinct    case @Type        when 'year' then format(dateadd(mm,number,@Time),'MM月')        when 'month' then format(dateadd(dd,number,@Time),'dd日')        else format(dateadd(hh,number,@Time),'HH时')        end DT    FROM master..spt_values        WHERE type='P' and  ((@Type='day' and number<25) or (@Type!='day' and number<50))        ),        --具体查询的数据信息        tb as (          select CreateTime as Time from T_OrderInfo where OrderBargainType=1 and  CustomerID in (select CustomerID from T_CustomerInfo where CompanyID=@CompanyID and (@SalesID=''        or cast(SalesID as nvarchar(500))= @SalesID))        ),        --按照报表类型进行转换        tc as (        select  case @Type        when 'year' then format(Time,'MM月')        when 'month' then format (Time,'dd日')        else format(Time,'HH时') end  Time from tb where            (@Type='year' and  datepart(year,Time)=datepart(year,@Time)) or          (@Type='month' and  convert(varchar(7),Time,120)=convert(varchar(7),@Time,120)) or           (@Type='day' and  convert(varchar(10),Time,120)=convert(varchar(10),@Time,120))           )         --最后join        select ta.DT,isnull(b.Num,0) as Num from ta left join (select Time,Num=Count(1) from tc group by Time) b on ta.DT=b.Time