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))
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
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
@@DATEFIRST
LukeH 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)
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'
;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
-3
WHEN ((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2 THEN
-2
ELSE
-1
END
from #test
)
SELECT *
from q1
WHERE datadate BETWEEN dateadd(d, daysback, @testdate) AND dateadd(d, -1, @testdate)
[Edit after OP comment]
(Anyone thinking that the 3rd Monday of the month is the same as the Monday of the 3rd week are the same needs to consider how you define the first week of a month - is it the first whole week starting on a specific day - usually Sunday or Monday, or is it the 1st to 7th of a month regardless of day)
To have the 3rd Monday of the month, rather than the Monday of the 3rd week then use this in the
CASE
WHEN ((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2 AND ceiling(DAY(@testdate) / 7.0) = 3 THEN
-3
Explanation: We discussed that
((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2
will find "Mondays". The extra bit can be explained by breaking it down:
DAY(@testdate)
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.