如何从sqlserver中的用户输入日期获取最后5周的数据 [英] How to get last 5Weeks of data from User Input date in sqlserver

问题描述

我无法按周计数，即7天的发货订单为一个星期，请按以下方式检查我的查询:

Hi,

I am not able to get count in weekwise i.e 7 days shipped orders to be one week, please check my query as follows:

Declare @CurrentDate nvarchar(50);
SET @CurrentDate='2012-07-25'

BEGIN 

;WITH DateList AS
(
SELECT  DATEADD(WEEK, 0, CONVERT(DATETIME, @CurrentDate)) AS CreateDateTime, 1 AS Cnter
UNION ALL
SELECT  DATEADD(WEEK, -1, CreateDateTime), DateList.Cnter + 1
FROM DateList
WHERE DateList.Cnter  < 5
)
SELECT  DateList.CreateDateTime AS ShipWeek, COALESCE(Temp.TotalCount, 0) AS TotalCount
FROM DateList
LEFT JOIN  (
SELECT  COUNT(Id) TotalCount
,DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CreatedDateTime),'19000101') AS ShipWeek
FROM ShipmentDetail 
WHERE CreatedDateTime
BETWEEN DATEADD(DAY,-30,@CurrentDate) AND @CurrentDate
GROUP BY  DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CreatedDateTime),'19000101') 
) Temp
ON CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.ShipWeek
END

结果表如下:

ShipWeek TotalCount

2012-07-25 00:00:00.000 0
2012-07-18 00:00:00.000 0
2012-07-11 00:00:00.000 0
2012-07-04 00:00:00.000 0
2012-06-27 00:00:00.000 0
请给我确切的解决方法.

谢谢与问候，

Raghu.

Result Table is as follows:

ShipWeek TotalCount

2012-07-25 00:00:00.000 0
2012-07-18 00:00:00.000 0
2012-07-11 00:00:00.000 0
2012-07-04 00:00:00.000 0
2012-06-27 00:00:00.000 0
please give me the exact solution.

Thanks&Regards,

Raghu.

推荐答案

您的查询中发生的是该联接未导致任何匹配.

在您的示例中，日期列表显示星期三，其中临时子查询以星期一为结果.您可以通过将@CurrentDate更改为星期一来验证这一点.例如:

What happens in your query is that the join doesn''t result in any matches.

In your sample the DateList shows wednesdays, where the temp subquery results in mondays. You can verify this by changing the @CurrentDate into a monday. For example:

SET @CurrentDate='2012-07-23'

您需要做的就是将DateList也转换为星期一.像这样:

What you need to do is, that you convert your DateList to mondays as well. Like this:

WITH DateList AS
(
SELECT  DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, '19000101',@CurrentDate), '19000101')) AS CreateDateTime, 1 AS Cnter
UNION ALL
SELECT  DATEADD(WEEK, -1, CreateDateTime), DateList.Cnter + 1
FROM DateList
WHERE DateList.Cnter  < 5
)

我无法完全测试该解决方案，因为我没有可用的数据.但这应该可行.


除了此解决方案之外，您可能还会看到其他内容.在临时子查询中，您具有以下where-clause

I couldn''t fully tests the solution, because I don''t have data available. But this should work.


Aside from this solution there might be another thing you might look at. In the temp subquery you have the following where-clause

WHERE CreatedDateTime
BETWEEN DATEADD(DAY,-30,@CurrentDate) AND @CurrentDate

您选择30天.但是在您的日期列表中，您有5周，即35天.这可能会导致最旧的一周中的数据不完整.但这实际上取决于您要通过此查询实现的目标.同样，当您想使用此查询来获取历史数据时，您可能希望将where子句更改为以下内容，以确保覆盖整个最年轻的一周(联接将过滤掉不需要的一周).

You select 30 days. But in your DateList you have 5 weeks, which is 35 days. This might result in incomplete data in the oldest week. But that actually depends on the objective you want to achieve with this query. Also when you want to use this query to get historical data, you might want to change the where clause into the following to ensure that the whole youngest week is covered (the join would filter the week you don''t need).

WHERE CreatedDateTime
BETWEEN DATEADD(DAY,-35,@CurrentDate) AND DATEADD(DAY,7,@CurrentDate)

这篇关于如何从sqlserver中的用户输入日期获取最后5周的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！