考虑到周末，如何在SQL服务器中获取昨天的数据 [英] How do I get yesterday data in SQL server considering weekends

问题描述

我的活动是从表格中为昨天的数据生成报告。如果今天是星期一，我必须为周六和周日生成报告。如果今天是星期一的第三周，我必须生成周五，周六和周五的报告。星期天。



我的尝试：



- 昨天

My activity is to generate report from a table for yesterday data. If today is Monday I have to generate report for Saturday and Sunday. If today is third week Monday I have to generate report for Friday, Saturday & Sunday.

What I have tried:

--Yesterday

select convert(varchar(8),GETDATE()-1,112)

推荐答案

我创建了以下测试数据来演示您需要做什么 - 它只是一系列日期，其中一个数字代表一些数据

I created the following test data to demonstrate what you need to do - it's just a series of dates with a number to represent some data

create table #test
(
	datadate date,
	data int
)
declare @startdate date = '2018-05-01'
declare @enddate date = getdate()


;WITH q AS
    (
    SELECT  @startdate AS d1, 1 as d2
    UNION ALL
    SELECT  dateadd(d, 1, d1) , d2 + 1 
    FROM    q
    WHERE dateadd(d, 1, d1) < @enddate
    )
INSERT INTO #test
SELECT  d1, d2
FROM    q

获取昨天的数据很简单：

To get "yesterday's" data is simple:

SELECT * FROM #test
WHERE datadate = DATEADD(d, -1, cast(getdate() as date))

CAST 只是为了摆脱 getdate（）的时间元素，否则不会返回任何数据。



To得到周数我正在使用内置 DATEPART 函数，参数为周。例如。

The CAST is just to get rid of the time element of getdate() otherwise no data is returned.

To get the week number I'm using the built in DATEPART function with a parameter of week. E.g.

select * FROM #test
WHERE (DATEPART(week,datadate)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, datadate), 0))) + 1 = 3

要确定一周的哪一天，我不会使用星期一等字样，因为这取决于语言。我打算在一周内使用当天的索引或数字。但这取决于你设置的 @@ DATEFIRST LukeH at 这篇文章 [ ^ ]提供了一个很好的解决方案：

To determine what day of the week it is I'm not going to use the word "Monday" etc as that is language dependent. I'm going to use the index or number of the day within the week. But this depends on your setting of @@DATEFIRSTLukeH at this post[^] provides a nice solution:

select *, DATENAME(dw, datadate), DATEPART(dw, datadate) FROM #test 
WHERE ((DATEPART(dw, datadate) + @@DATEFIRST) % 7) NOT IN (0, 1)

现在你需要将它们放在一起。您可以在 WHERE 子句中使用 CASE ，但我不是粉丝。在下面的示例中，我使用CTE（公用表表达式）首先计算出我想要包含的天数，然后在一个简单的 BETWEEN > WHERE 子句。您应该在下面的代码中用 getdate（）替换 @testdate ：

Now you need to put it all together. You could use CASE in the WHERE clause, but I'm not a fan. In my example below I use a CTE (Common Table Expression) to first work out how many days back I want to include and then use BETWEEN in a simple WHERE clause. You should substitute getdate() for @testdate in the code below:

declare @testdate date = '2018-06-12' -- '2018-05-7' -- Monday -- monday of 3rd week '2018-06-11' -- tuesday '2018-06-12'
;with q1 as 
(
	select datadate, data
	,daysback = CASE WHEN ((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2 AND (DATEPART(week,@testdate)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, @testdate), 0))) + 1 = 3 THEN
		-- Monday of 3rd week  so we want Fri, Sat, Sun ... this date minus 3 days
		-3
	WHEN ((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2  THEN
		-- any other monday so we want Sat and Sun
		-2
	ELSE
		-- any other day so just yesterday
		-1 
	END 
	from #test
)
SELECT *
from q1
WHERE datadate BETWEEN dateadd(d, daysback, @testdate) AND dateadd(d, -1, @testdate)

[ OP评论后编辑]

（任何人认为本月第3个星期一与第3周的星期一相同，需要考虑如何定义一个月的第一周 - 是它是从特定日期开始的第一个整周 - 通常是星期日或星期一，或者是一个月的第1到第7个月，不管是哪一天）



要获得第3个星期本月的星期一，而不是第3周的星期一，然后在 CASE

WHEN ((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2 AND  ceiling(DAY(@testdate) / 7.0) = 3 THEN
		-3

说明：我们讨论过

((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2

将找到星期一。可以通过分解来解释额外的位：

will find "Mondays". The extra bit can be explained by breaking it down:

DAY(@testdate)

将返回该月的某天，例如今天18日，2018年6月18日。将它除以一周中的天数可以指示它在哪一周。例如18 / 7.0给出2.571428。注意7 .0 ，否则18/7给出答案2.您可以将2.571428解释为我们本月已经有2个整个星期一，现在我们已经进入下一个星期一了。因此，通过添加 ceiling ，我们可以确定下一个实际上是第3个星期一（本月）。

好吧，这可能不是最好的解释，但希望你能看到发生的事情。



作为一个旁边（和一个信用），这是改编自一个帖子作者Lynn Pettis在她的博客上有很多方便的东西来自沙漠的SQL Musings [ ^ ]在SQL Server Central。

is going to return the day of the month, e.g. today 18 for 18-Jun-2018. Dividing that by the number of days in a week gives you an indication of which week it is in. For example 18 / 7.0 gives 2.571428. Note the 7.0, otherwise 18/7 gives the answer 2. You could interpret that 2.571428 as "we've already had 2 whole Mondays this month and now we're part way into the next one". So by adding ceiling we can determine that "the next one" is actually the 3rd Monday (for this month).
Ok, that's probably not the best explanation but hopefully you can see what is happening.

As an aside (and a credit), this was adapted from a post by Lynn Pettis who has lots of handy stuff on her blog SQL Musings from the Desert[^] at SQL Server Central.

谢谢大家。最后我得到了我的解决方案。

Thank you all. Finally I got my solution.

Declare @StartDate datetime,@EndDate datetime
set @EndDate=GetDate()-1 

select case when ((datepart(D,@EndDate-1)-1)/7+1) not in (2) and (datename(dw,@EndDate-1))='Saturday' then @EndDate-1 
			when ((datepart(D,@EndDate-1)-1)/7+1) in (2) and (datename(dw,@EndDate-1))='Saturday' then @EndDate-2 Else @EndDate End StartD,@EndDate EndD

这篇关于考虑到周末，如何在SQL服务器中获取昨天的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！