SQL按照日期范围统计数据，填补缺失日期

按天统计订单数量及金额（查询6月7号-7月6号之间的订单量）

select
    Times,
    count(Id) as Quantity,
    sum(ActualTotalMoney) as TotalMoney
from
(
    select
        a.dt AS Times,
        --ISNULL(b.Id, '0') AS Id,
        b.Id,
        ISNULL(b.ActualTotalMoney,'0') as ActualTotalMoney
    from
        (
            select
                dateadd(d, number, '2019-06-07') dt
            from
                master..spt_values
            where
                type = 'p' and dateadd(dd, number, '2019-06-07') <= '2019-07-06'
                
        ) a 
    left join dbo.Orders b on DateDiff(dd,b.CreateTime,a.dt)=0
) c
group by Times

按月统计订单数量及金额（查询1月-7月之间的订单量）

select
    Times,
    count(Id) as Quantity,
    sum(ActualTotalMoney) as TotalMoney
from
(
    select
        a.dt AS Times,
        --ISNULL(b.Id, '0') AS Id,
        b.Id,
        ISNULL(b.ActualTotalMoney,'0') as ActualTotalMoney
    from
        (
            select
                dateadd(mm, number, '2019-01-01') dt
            from
                master..spt_values
            where
                type = 'p' and dateadd(mm, number, '2019-01-01') <= '2019-07-01'
        ) a 
    left join dbo.Orders b on DateDiff(mm,b.CreateTime,a.dt)=0
) c
group by Times