June 6, 2015 at 8:50 am
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
June 6, 2015 at 9:31 am
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
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]
June 6, 2015 at 10:24 am
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
June 6, 2015 at 1:42 pm
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!
June 6, 2015 at 2:56 pm
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
June 6, 2015 at 4:05 pm
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!
June 6, 2015 at 5:44 pm
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
June 7, 2015 at 4:01 pm
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()))
June 8, 2015 at 7:17 am
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
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]
June 9, 2015 at 4:36 am
Thanks everyone for your help. It was all very helpful and much appreciated. No refunds needed ๐
Sandy Tucker
June 9, 2015 at 4:55 am
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
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply