sql - 在 7 周内选择 7 天前

Question

@status [nvarchar](max),
@fromDate [datetime],
@toDate [datetime],
@companyId [int]
select  
ISNULL(SUM(rec.SubTotal), 0) AS SubTotal,
sto.Id AS StoreId,
    CAST(rec.CreatedDate AS DATE) as CreatedDate,
    sto.Name AS StoreName from Receipts rec
                        left join(
                            select  ReceiptId, SUM(Quantity) as Quantity from ReceiptDetails
                            group by ReceiptId
                        ) red on rec.Id = red.ReceiptId
                        left outer join(
                            select Id,Name from Stores
                            group by Id,Name
                        ) sto on rec.StoreId = sto.Id
                         where rec.CompanyId = @companyId
                            and rec.Status = @status
                            and rec.CreatedDate <= @todate
                            and rec.CreatedDate >=  @fromDate
group by sto.Id, sto.Name,CAST(rec.CreatedDate AS DATE)

这是我当前的查询 SQL，目前我通过 @todate 和 @fromdate 在 rangeDate 中选择每天的数据

现在我想按过去 7 周的日期按 CreatedDate 选择数据，例如当 @fromdate 是今天：2018-12-1 时，预期的数据将是

2018-10-20
2018-10-27
2018-11-3
2018-11-10
2018-12-17
2018-11-24
2018-12-1

我目前的数据

...
...
2018-11-29
2018-11-30
2018-12-1

我的意思是 6 周前的日期

标签： sqlsql-server

Answer 1

您可以将其创建为一个函数并传入周数，但这取决于您。

见下文：

declare @i as int
set @i = 7
select  
    ISNULL(SUM(rec.SubTotal), 0) AS SubTotal,
    sto.Id AS StoreId,
    case 
        when CAST(rec.CreatedDate as Date) between CAST(rec.CreatedDate AS DATE) -(7 * (@i)) and CAST(rec.CreatedDate AS DATE) -((7*(@i - 1)) + 1)
            then 
                Cast(CAST(rec.CreatedDate -(7*(@i)) AS DATE))
        when CAST(rec.CreatedDate as Date) between CAST(rec.CreatedDate AS DATE) -(7 * (@i-1)) and CAST(rec.CreatedDate AS DATE) -((7*(@i-2)) + 1)
            then 
                Cast(CAST(rec.CreatedDate AS DATE) -(7*(@i-1)) AS DATE)
        when CAST(rec.CreatedDate as Date) between CAST(rec.CreatedDate AS DATE) -(7 * (@i - 2)) and CAST(rec.CreatedDate AS DATE) -((7*(@i-3)) + 1)
            then 
                Cast(CAST(rec.CreatedDate AS DATE) -(7*(@i-2)) AS DATE)
        when CAST(rec.CreatedDate as Date) between CAST(rec.CreatedDate AS DATE) -(7 * (@i - 3)) and CAST(rec.CreatedDate AS DATE) -((7*(@i-4)) + 1)
            then 
                Cast(CAST(rec.CreatedDate AS DATE) -(7*(@i-3)) AS DATE)
        when CAST(rec.CreatedDate as Date) between CAST(rec.CreatedDate AS DATE) -(7 * (@i - 4)) and CAST(rec.CreatedDate AS DATE) -((7*(@i-5)) + 1)
            then 
                Cast(CAST(rec.CreatedDate AS DATE) -(7*(@i-4)) AS DATE)
        when CAST(rec.CreatedDate as Date) between CAST(rec.CreatedDate AS DATE) -(7 * (@i - 5)) and CAST(rec.CreatedDate AS DATE) -((7*(@i-6)) + 1)
            then 
                Cast(CAST(rec.CreatedDate AS DATE) -(7*(@i-5)) AS DATE)
        when CAST(rec.CreatedDate as Date) between CAST(rec.CreatedDate AS DATE) -(7 * (@i - 6)) and CAST(rec.CreatedDate AS DATE) -((7*(@i-7)) + 1)
            then 
                Cast(CAST(rec.CreatedDate AS DATE) -(7*(@i-6)) AS DATE)
        else ''
    end as CreatedDate,
    sto.Name AS StoreName 
from Receipts rec
                    left join(
                        select  ReceiptId, SUM(Quantity) as Quantity from ReceiptDetails
                        group by ReceiptId
                    ) red on rec.Id = red.ReceiptId
                    left outer join(
                        select Id,Name from Stores
                        group by Id,Name
                    ) sto on rec.StoreId = sto.Id
                     where rec.CompanyId = @companyId
                        and rec.Status = @status
                        and rec.CreatedDate <= @todate
                        and rec.CreatedDate >=  @fromDate
group by sto.Id, sto.Name,CAST(rec.CreatedDate AS DATE)