Return data between two dates in the past

Question

Return data between two dates in the past

SandyTucker

SSC Enthusiast

Points: 175
More actions
June 6, 2015 at 8:50 am

#308900

Hello - does anyone know why the statement "where WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0' is successful, but "WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE())) AND (DATEADD(DD, -3, GETDATE()))" is an invalid statement? I've tried a lot of different syntax variations of this statement and cannot get to work between two lookback dates. Thanks for any clues!
Sandy Tucker

Viewing 11 posts - 1 through 10 (of 10 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

SandyTucker (6/6/2015)
Hello - does anyone know why the statement "where WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0' is successful, but "WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE())) AND (DATEADD(DD, -3, GETDATE()))" is an invalid statement? I've tried a lot of different syntax variations of this statement and cannot get to work between two lookback dates. Thanks for any clues!

You have a problem with parentheses. Try:

WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE()) AND DATEADD(DD, -3, GETDATE()))

Better yet, here's an alternative that should perform better. To avoid having SQL calculating the DATEADDs for each row, try:

DECLARE @StartDate DATE

DECLARE @EndDate DATE

SET @StartDate = DATEADD(DD, -16, GETDATE())

SET @EndDate = DATEADD(DD, -3, GETDATE())

... Your query ...

WHERE WD.WRKD_WORK_DATE BETWEEN @StartDate AND @EndDate

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SandyTucker SSC Enthusiast Points: 175 More actions · Answer 2

Hi Alvin - thanks very much. I will try your alternate suggestion as for some reason correcting parens still is throwing an invalid statement failure.

Cheers!

Sandy Tucker

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

Hmmm....that WHERE clause you said threw a syntax error works for me. I'd guess there's something else in the statement that's causing some issues.

Alvin's should also work if you remove the outermost parentheses (the outermost parentheses wrap the entire AND construct, so only one expression is being passed to BETWEEN).

You should be fine if, as indicated by the column name, that column is a DATE datatype, but if it's DATETIME, and you might ever have time information included, be wary of using BETWEEN. See http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx for a good explanation of that.

I hope this helps.

Cheers!

SandyTucker SSC Enthusiast Points: 175 More actions · Answer 4

Thanks Jacob! Here is the whole statement below. This statement throws an error, but if I end it with just "WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -16, GETDATE()) AND GETDATE()) = 0" It works fine. I'm obviously not very good with SQL, still learning! See anything in the whole code that is throwing off the end?

SELECT E.EMP_NAME, E.EMP_ID, E.EMP_FULLNAME [EMPLOYEE NAME], E.EMP_VAL7, E.EMP_VAL2, E.EMP_VAL5, E.EMP_VAL8, E.EMP_VAL9

FROM EMPLOYEE E, PAY_GROUP PG

WHERE E.PAYGRP_ID = PG.PAYGRP_ID AND

E.EMP_TERMINATION_DATE = '01/01/3000' AND

E.EMP_HIRE_DATE <= GETDATE() AND

e.emp_val11 = 'N' AND

(SELECT COALESCE(SUM(WD.WRKD_MINUTES),0)

FROM WORK_SUMMARY WS, WORK_DETAIL WD

WHERE E.EMP_ID = WS.EMP_ID AND

WS.WRKS_ID = WD.WRKS_ID AND

WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -16, GETDATE()) AND DATEADD(DD, -3, GETDATE())

Sandy Tucker

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

Just from a quick glance, it looks like in the one that doesn't work the opening parenthesis for the subquery doesn't have a corresponding closing parenthesis (as a hint of that, note that both versions of the query end with two closing parentheses, but one of them ends with an additional DATEADD function, which would add 1 closing parenthesis)

If that parenthesis were added, along with the condition you're wanting to evaluate (I'm assuming the same =0 as with the other one), then it should work.

So this should be fine:

SELECT E.EMP_NAME, E.EMP_ID, E.EMP_FULLNAME [EMPLOYEE NAME], E.EMP_VAL7, E.EMP_VAL2, E.EMP_VAL5, E.EMP_VAL8, E.EMP_VAL9

FROM EMPLOYEE E, PAY_GROUP PG

WHERE E.PAYGRP_ID = PG.PAYGRP_ID AND

E.EMP_TERMINATION_DATE = '01/01/3000' AND

E.EMP_HIRE_DATE <= GETDATE() AND

e.emp_val11 = 'N' AND

(SELECT COALESCE(SUM(WD.WRKD_MINUTES),0)

FROM WORK_SUMMARY WS, WORK_DETAIL WD

WHERE E.EMP_ID = WS.EMP_ID AND

WS.WRKS_ID = WD.WRKS_ID AND

WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -16, GETDATE()) AND DATEADD(DD, -3, GETDATE()))=0

Cheers!

SandyTucker SSC Enthusiast Points: 175 More actions · Answer 6

That worked! Actually I had something like that earlier but didn't realize I still needed the =0 at the end.

Thank you so much both of you, I can't tell you how much this helped me!

Sandy Tucker

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Alvin Ramard (6/6/2015)
SandyTucker (6/6/2015)
Hello - does anyone know why the statement "where WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0' is successful, but "WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE())) AND (DATEADD(DD, -3, GETDATE()))" is an invalid statement? I've tried a lot of different syntax variations of this statement and cannot get to work between two lookback dates. Thanks for any clues!
You have a problem with parentheses. Try:
WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE()) AND DATEADD(DD, -3, GETDATE()))
Better yet, here's an alternative that should perform better. To avoid having SQL calculating the DATEADDs for each row, try:
DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @StartDate = DATEADD(DD, -16, GETDATE())
SET @EndDate = DATEADD(DD, -3, GETDATE())
... Your query ...
WHERE WD.WRKD_WORK_DATE BETWEEN @StartDate AND @EndDate

Actually, the DATEADDs in the snippet below are only calculated once for the query. SQL Server is smart enough to realize that each of the DATEADD functions will return a constant value.

WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE()) AND DATEADD(DD, -3, GETDATE()))

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

Lynn Pettis (6/7/2015)
Alvin Ramard (6/6/2015)
SandyTucker (6/6/2015)
Hello - does anyone know why the statement "where WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0' is successful, but "WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE())) AND (DATEADD(DD, -3, GETDATE()))" is an invalid statement? I've tried a lot of different syntax variations of this statement and cannot get to work between two lookback dates. Thanks for any clues!
You have a problem with parentheses. Try:
WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE()) AND DATEADD(DD, -3, GETDATE()))
Better yet, here's an alternative that should perform better. To avoid having SQL calculating the DATEADDs for each row, try:
DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @StartDate = DATEADD(DD, -16, GETDATE())
SET @EndDate = DATEADD(DD, -3, GETDATE())
... Your query ...
WHERE WD.WRKD_WORK_DATE BETWEEN @StartDate AND @EndDate
Actually, the DATEADDs in the snippet below are only calculated once for the query. SQL Server is smart enough to realize that each of the DATEADD functions will return a constant value.
WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE()) AND DATEADD(DD, -3, GETDATE()))

I thought, after I posted my reply that it might be the case, but I didn't know for sure.

Thanks Lynn

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SandyTucker SSC Enthusiast Points: 175 More actions · Answer 9

Thanks everyone for your help. It was all very helpful and much appreciated. No refunds needed 😉

Sandy Tucker

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

SandyTucker (6/6/2015)
That worked! Actually I had something like that earlier but didn't realize I still needed the =0 at the end.
Thank you so much both of you, I can't tell you how much this helped me!

A little formatting (and correction of old-style joins) goes a long way:

SELECT

E.EMP_NAME,

E.EMP_ID,

E.EMP_FULLNAME [EMPLOYEE NAME],

E.EMP_VAL7,

E.EMP_VAL2,

E.EMP_VAL5,

E.EMP_VAL8,

E.EMP_VAL9

FROM EMPLOYEE E

INNER JOIN PAY_GROUP PG

ON E.PAYGRP_ID = PG.PAYGRP_ID

WHERE E.EMP_TERMINATION_DATE = '01/01/3000'

AND E.EMP_HIRE_DATE <= GETDATE()

AND e.emp_val11 = 'N'

AND (

SELECT

COALESCE(SUM(WD.WRKD_MINUTES),0)

FROM WORK_SUMMARY WS

INNER JOIN WORK_DETAIL WD

ON WS.WRKS_ID = WD.WRKS_ID

WHERE E.EMP_ID = WS.EMP_ID -- outer reference

AND WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -16, GETDATE()) AND DATEADD(DD, -3, GETDATE())

) = 0

^{“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