选择7天前7周
选择7天前7周
提问于 2018-12-01 02:33:40
@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和@ data来选择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周前的那个日子
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-12-01 03:22:39
您可以将其创建为一个函数，并传递周数，但这取决于您。
见下文：
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)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53567287
复制相关文章
相似问题
腾讯云开发者
