Creating Filters like Previous Week, from Tuesday to Monday

Question

Creating Filters like Previous Week, from Tuesday to Monday

kyagi.jo

SSCrazy

Points: 2559
More actions
May 16, 2018 at 3:49 am

#345203

Hi,
If I needed to create some kind of filter that would get the data between the previous week's Tuesday, to the next Monday of this week, then I have the current way of it being done, which gets the date ranges:

SELECT * from DimTime where
date_value BETWEEN DATEADD(day, -6, DATEADD(DAY, ((DATEPART(dw, GETDATE()) -1) % 7) * -1, CAST(FLOOR(CAST(GETDATE() as float)) as datetime)))
AND
DATEADD(DAY, ((DATEPART(dw, GETDATE()) -1) % 7) * -1, CAST(FLOOR(CAST(GETDATE() as float)) as datetime))
This gets the dates that I want, although my concern is that this method of creating the dates doesn't look to be Sargable and starts to revert to index scans rather than seeks, screwing up query speed when I use the two date ranges in a more complex query.
Is there a way for dynamic date ranges like this to become Sargable? I have thought about making a table that I update every day which precalculates the values for these kinds of date ranges, so I can just join onto those tables, which does increase the query speed, but I want to be optimising this at query time if possible.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

kyagi.jo - Wednesday, May 16, 2018 3:49 AM
Hi,
If I needed to create some kind of filter that would get the data between the previous week's Tuesday, to the next Monday of this week, then I have the current way of it being done, which gets the date ranges:

SELECT * from DimTime where
date_value BETWEEN DATEADD(day, -6, DATEADD(DAY, ((DATEPART(dw, GETDATE()) -1) % 7) * -1, CAST(FLOOR(CAST(GETDATE() as float)) as datetime)))
AND
DATEADD(DAY, ((DATEPART(dw, GETDATE()) -1) % 7) * -1, CAST(FLOOR(CAST(GETDATE() as float)) as datetime))
This gets the dates that I want, although my concern is that this method of creating the dates doesn't look to be Sargable and starts to revert to index scans rather than seeks, screwing up query speed when I use the two date ranges in a more complex query.
Is there a way for dynamic date ranges like this to become Sargable? I have thought about making a table that I update every day which precalculates the values for these kinds of date ranges, so I can just join onto those tables, which does increase the query speed, but I want to be optimising this at query time if possible.

It's fine insofar as SARGability goes because the column being filtered, date_value , is not encapsulated in a formula.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

There are simpler ways of calculating your date range:

SELECT

Today,

[Weekday] = datename(weekday,Today),

[RangeStart 1] = DATEADD(DAY,1+DATEDIFF(DAY,7,Today)/7*7,0), -- tuesday last week (rolls on monday)

[RangeEnd 1] = DATEADD(DAY,DATEDIFF(DAY,0,Today)/7*7,0), -- monday this week (rolls on monday)

[RangeStart 2] = DATEADD(DAY,1+DATEDIFF(DAY,8,Today)/7*7,0), -- tuesday last week (rolls on tuesday)

[RangeEnd 2] = DATEADD(DAY,DATEDIFF(DAY,1,Today)/7*7,0), -- monday this week (rolls on tuesday)

[RangeStart 3] = DATEADD(DAY,DATEDIFF(DAY,6,Today)/7*7,0), -- monday last week (rolls on sunday)

[RangeEnd 3] = DATEADD(DAY,-1+DATEDIFF(DAY,-1,Today)/7*7,0), -- sunday this week (rolls on sunday)

[OriginalRangeStart] = DATEADD(day, -6, DATEADD(DAY, ((DATEPART(dw, Today) -1) % 7) * -1, CAST(FLOOR(CAST(Today as float)) as datetime))), -- monday last week (rolls on sunday)

[OriginalRangeEnd] = DATEADD(DAY, ((DATEPART(dw, Today) -1) % 7) * -1, CAST(FLOOR(CAST(Today as float)) as datetime)) -- sunday this week (rolls on sunday)

FROM ( -- sample data

SELECT CAST(GETDATE()-n AS DATETIME)

FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) d (n)

) d (Today)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kyagi.jo SSCrazy Points: 2559 More actions · Answer 3

Thanks, the simplified way of calculating the date range produced a much faster query plan than the originally stated way!

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

kyagi.jo - Wednesday, May 16, 2018 8:16 AM
Thanks, the simplified way of calculating the date range produced a much faster query plan than the originally stated way!

What did you use to measure the performance with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kyagi.jo SSCrazy Points: 2559 More actions · Answer 5

I was using SET STATISTICS IO ON, along with reading through the query plan in SQL Sentry Plan Explorer.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

kyagi.jo - Wednesday, May 16, 2018 8:45 AM
I was using SET STATISTICS IO ON, along with reading through the query plan in SQL Sentry Plan Explorer.

Thanks for the feedback.

Query plans don't actually give you "actual" performance comparisons because even the "actual" execution plan is filled with estimates and the occasional misgiving. In the past, I've pitted 2 queries that do the same thing against each other where the plan for the first query came out with a % of batch as "0%" and the second came out as "100 %". When you run it for duration and cpu usage, the exact opposite was true.

Since you're running SET STATISTICS IO ON, could you post the results of both queries and the queries themselves because I'm having a hard time believing that there would be any difference between the two insofar as IO goes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)