March 15, 2017 at 12:17 pm
I'm modifying a few queries to make them more efficient and have had success using the following technique.
Instead of manually changing the dates each time I run this, I've just used DATEADD and am now subtracting days.
It works great on every query except for one and I'm trying to figure out why it gives me different counts when everything is identical except for the statements below.
A report is run every Wednesday using dates from the Monday of the week prior to the following Sunday.
As an example, today is 3/15/17, so the dates I am using are 3/6 - 3/12
This is what I started with:
(DateAttribute >= CONVERT(DATETIME, '2017-03-06 00:00:00', 102)) AND (DateAttribute <= CONVERT(DATETIME, '2017-03-12 00:00:00', 102))
I've changed it to:
(DateAttribute BETWEEN DATEADD(DAY,-10,GETDATE()) AND DATEADD(DAY,-3,GETDATE()))
Doing this, the counts it gives me are different.
Is there something in the convert or dateadd that I am missing?
March 15, 2017 at 12:22 pm
1) You didn't strip the time.
2) You need to use < the ending day, rather than <=
For example:
I've changed it to:
(DateAttribute >= DATEADD(DAY,-10,CAST(GETDATE() AS date)) AND DateAttribute < DATEADD(DAY,-3,CAST(GETDATE() AS date))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 15, 2017 at 12:42 pm
The dates don't match either. One has 7 days and the other one only 6.
March 15, 2017 at 4:31 pm
I get that, but that led me to change this one instance and when I set it for 9 days it worked like a charm.
I'm still unclear as to why the others work with 10 days, but if it works for now, I am fine.
March 22, 2017 at 8:42 am
I think you will see a performance gain if you variablize this instead of doing it inline every time.
The way you have this set up it will run the date conversion against every record.
Do the Date conversion once at the beginning then use your variable(s) in the where clause.
DECLARE @BeginDate DATE = DATEADD(DAY,-10,CAST(GETDATE() AS date)),
@EndDate DATE = DATEADD(DAY,-3,CAST(GETDATE() AS date));
SELECT *
FROM Blah
WHERE DateAttribute >= @BeginDate AND DateAttribute < @EndDate
Regards,
Matt
March 22, 2017 at 11:57 am
how about doing something like this?
DECLARE
@BeginDate DATETIME = DATEADD(wk, DATEDIFF(wk, 0, GetDate())-1, 0),
@EndDate DATETIME = DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0);
...
WHERE (DateAttribute >= @BeginDate) AND (DateAttribute < @EndDate)
March 22, 2017 at 12:03 pm
Matt Simmons - Wednesday, March 22, 2017 8:42 AMI think you will see a performance gain if you variablize this instead of doing it inline every time.
The way you have this set up it will run the date conversion against every record.
Do the Date conversion once at the beginning then use your variable(s) in the where clause.
DECLARE @BeginDate DATE = DATEADD(DAY,-10,CAST(GETDATE() AS date)),
@EndDate DATE = DATEADD(DAY,-3,CAST(GETDATE() AS date));
SELECT *
FROM Blah
WHERE DateAttribute >= @BeginDate AND DateAttribute < @EndDate
Matt, this makes way too much sense..
Thank you for that.
I've also commented out places for actual dates using this in case they decide to ever utilize those.
March 22, 2017 at 12:05 pm
Chris Harshman - Wednesday, March 22, 2017 11:57 AMhow about doing something like this?
DECLARE
@BeginDate DATETIME = DATEADD(wk, DATEDIFF(wk, 0, GetDate())-1, 0),
@EndDate DATETIME = DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0);
...
WHERE (DateAttribute >= @BeginDate) AND (DateAttribute < @EndDate)
Based upon the peculiarity of the initial dates and ranges this doesn't quite work, but I appreciate it.
March 23, 2017 at 3:02 am
elzool - Wednesday, March 22, 2017 12:05 PMChris Harshman - Wednesday, March 22, 2017 11:57 AMhow about doing something like this?
DECLARE
@BeginDate DATETIME = DATEADD(wk, DATEDIFF(wk, 0, GetDate())-1, 0),
@EndDate DATETIME = DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0);
...
WHERE (DateAttribute >= @BeginDate) AND (DateAttribute < @EndDate)Based upon the peculiarity of the initial dates and ranges this doesn't quite work, but I appreciate it.
This will work:
-- For a few values of GETDATE(), calculate the previoussunday as end of week
-- and the monday prior to that as start of week.
-- if you run this on a sunday, then beware that the endof week is the same day
SELECT
[GetDate()],
[CalculatedStartDate]= DATEADD(DAY,DATEDIFF(DAY,6,[GetDate()])/7*7,0), -- always monday
[CalculatedEndDate]= DATEADD(DAY,6+DATEDIFF(DAY,6,[GetDate()])/7*7,0) -- always the followingsunday
FROM (
VALUES
('2017-03-11 10:00:00.000'),
('2017-03-12 10:00:00.000'),
('2017-03-13 10:00:00.000'),
('2017-03-14 10:00:00.000'),
('2017-03-15 10:00:00.000'),
('2017-03-16 10:00:00.000'),
('2017-03-17 10:00:00.000'),
('2017-03-18 10:00:00.000'),
('2017-03-19 10:00:00.000'),
('2017-03-20 10:00:00.000'),
('2017-03-21 10:00:00.000'),
('2017-03-22 10:00:00.000'),
('2017-03-23 10:00:00.000'),
('2017-03-24 10:00:00.000'),
('2017-03-25 10:00:00.000')
) d ([GetDate()])
[/code]
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply