sql server 根据选择的2个日期值，显示以日、周、月为单位的数据

 

两个日期值为：30天以内按日显示

    --折线数据--订餐数量和消费数量  30天以内按日显示
    select   CONVERT(NVARCHAR(10), DATEPART(DD, DinnerTime)) + '日' as DinnerTime,   
            case when sum(Number) is Null then 0 else sum(Number) end as Number , sum(Count) as Count 
    from MO_Orders o where 1 = 1 
    and ( DinnerTime >= '2017-02-01' and  DinnerTime <= '2017-02-28' )  
    group by DinnerTime  

结果如下:

 

两个日期值为：大于30天小于90天按周显示

  --折线数据--订餐数量和消费数量  大于30天小于90天按周显示 
 select '第'+CONVERT(NVARCHAR(2),DinnerTime)+'周' as DinnerTime,sum(Number) as Number,sum(Count) as Count 
 from 
 (  SELECT datepart(week,DinnerTime) AS DinnerTime,
     case when sum(Number) is Null then 0 else sum(Number) end as Number , sum(Count) as Count 
     FROM MO_Orders o 
     WHERE   ( DinnerTime >= '2017-01-01' and  DinnerTime <= '2017-02-28' )       
     GROUP BY DinnerTime 
 ) m 
 group by DinnerTime 
 order by DinnerTime

结果如下图：

 

两个日期值为：大于90天按月显示

 --折线数据--订餐数量和消费数量  大于90天按月显示 
 select  CONVERT(NVARCHAR(2),DinnerTime)+'月' as DinnerTim,sum(Number) as Number,sum(Count) as Count 
 from 
 (  
    SELECT datepart(month,DinnerTime) AS DinnerTime,
     case when sum(Number) is Null then 0 else sum(Number) end as Number , sum(Count) as Count 
     FROM MO_Orders o 
     WHERE   ( DinnerTime >= '2016-10-01' and  DinnerTime <= '2017-02-28' )       
     GROUP BY DinnerTime 
 ) m 
 group by DinnerTime 
 order by DinnerTime

结果如下：