January 29, 2018 at 2:12 pm
First, thank you for reading this.
Next, I'm using SQL Server Management Studio 2012.
Lastly, I'm very new to all of this and know only the basics. But I have searched high and low and I've been unable to find something that shows me how to accomplish the following.
Here's my query (note...it does NOT work as-is)
SELECT
ContractID
, ContractStatusID
, ContractStatusDate
FROM LeaseData.dbo.vContractsAll
WHERE
NumOfAssets > 0
AND Lessor NOT IN (51,54,55,56,57,59)
AND ContractStatusID = '17'
AND CASE DATEPART(DW, CONVERT(DATE, GETDATE())) = '2'
THEN CONVERT(DATE, ContractStatusDate) = DATEADD(DAY, -4, CONVERT(DATE, GETDATE()))
ELSE CONVERT(DATE, ContractStatusDate) = DATEADD(DAY, -2, CONVERT(DATE, GETDATE()))
END
I'm only trying to say IF Day of Week is 2 (i.e. Monday), then results should pull for ContractStatusDate = Today-4, if Day of Week is <> 2, then pull ContractStatusDate = Today-2. Seems very simple, but it's not working and is telling me "Incorrect syntax near '='" on line 10. I'm used to Excel and was hoping there was a simple IF ___ Then ____ Else ____, but I do not believe IF THEN ELSE works in SQL?
January 29, 2018 at 2:37 pm
karlw - Monday, January 29, 2018 2:12 PMFirst, thank you for reading this.
Next, I'm using SQL Server Management Studio 2012.
Lastly, I'm very new to all of this and know only the basics. But I have searched high and low and I've been unable to find something that shows me how to accomplish the following.Here's my query (note...it does NOT work as-is)
SELECT
ContractID
, ContractStatusID
, ContractStatusDate
FROM LeaseData.dbo.vContractsAll
WHERE
NumOfAssets > 0
AND Lessor NOT IN (51,54,55,56,57,59)
AND ContractStatusID = '17'
AND CASE DATEPART(DW, CONVERT(DATE, GETDATE())) = '2'
THEN CONVERT(DATE, ContractStatusDate) = DATEADD(DAY, -4, CONVERT(DATE, GETDATE()))
ELSE CONVERT(DATE, ContractStatusDate) = DATEADD(DAY, -2, CONVERT(DATE, GETDATE()))
END
I'm only trying to say IF Day of Week is 2 (i.e. Monday), then results should pull for ContractStatusDate = Today-4, if Day of Week is <> 2, then pull ContractStatusDate = Today-2. Seems very simple, but it's not working and is telling me "Incorrect syntax near '='" on line 10. I'm used to Excel and was hoping there was a simple IF ___ Then ____ Else ____, but I do not believe IF THEN ELSE works in SQL?
A CASE Expression cannot return a Boolean value. You can rewrite this asAND CONVERT(DATE, ContractStatusDate) =
CASE DATEPART(DW, CONVERT(DATE, GETDATE())) = '2'
THEN DATEADD(DAY, -4, CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, -2, CONVERT(DATE, GETDATE()))
END
You could also move the rest of the shared elements outside of the CASE expression to get the following:
AND CONVERT(DATE, ContractStatusDate) = DATEADD(DAY, CASE WHEN DATEPART(DW, CONVERT(DATE, GETDATE())) = '2' THEN -4 ELSE -2 END, CONVERT(DATE, GETDATE()))
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 29, 2018 at 2:59 pm
Thank you Drew for the quick response.
I tried your first suggestion and I'm still getting the following error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '='.
Any suggestions on how to correct this?
Your 2nd suggestion however does work. I'm just curious how to get the first suggestion to work.
January 29, 2018 at 3:06 pm
It's missing the WHEN right after the CASE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 29, 2018 at 3:08 pm
drew.allen - Monday, January 29, 2018 3:06 PMIt's missing the WHEN right after the CASE.Drew
Thank you!!!! Both work perfectly.
January 30, 2018 at 9:07 am
Drew,
I ultimately ended up with the following for the CASE statement part:
AND CONVERT(DATE, c.ContractStatusDate) =
CASE WHEN DATEPART(DW, CONVERT(DATE, GETDATE())) = '3'
THEN DATEADD(DAY, -4, CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, -2, CONVERT(DATE, GETDATE()))
END
But I need to change the -4 line above to instead be "between -2 and -4"...do you have any idea how to do that? I need it to pull where the ContractStatusDate matches Friday, Saturday or Sunday when Today is "Tuesday" (i.e. 3). The -2 line is good still for when DatePart Weekday is NOT 3.
(Note: I had to change the CASE WHEN DATEPART(DW, CONVERT(DATE, GETDATE()))) = 2 to =3 because I originally thought I needed to pull for Monday's but I actually need Tuesday).
January 30, 2018 at 12:53 pm
karlw - Tuesday, January 30, 2018 9:07 AMDrew,
I ultimately ended up with the following for the CASE statement part:
AND CONVERT(DATE, c.ContractStatusDate) =
CASE WHEN DATEPART(DW, CONVERT(DATE, GETDATE())) = '3'
THEN DATEADD(DAY, -4, CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, -2, CONVERT(DATE, GETDATE()))
END
But I need to change the -4 line above to instead be "between -2 and -4"...do you have any idea how to do that? I need it to pull where the ContractStatusDate matches Friday, Saturday or Sunday when Today is "Tuesday" (i.e. 3). The -2 line is good still for when DatePart Weekday is NOT 3.
(Note: I had to change the CASE WHEN DATEPART(DW, CONVERT(DATE, GETDATE()))) = 2 to =3 because I originally thought I needed to pull for Monday's but I actually need Tuesday).
First, a BETWEEN must be in order. -4 comes before -2, not after, so BETWEEN -2 and -4 is the empty set.
Second, a CASE expression can only return a single value, so you can't return three values in place of the single -4 value.
Also, you will be getting Friday's data on both Sunday and Tuesday and Saturday's data on both Monday and Tuesday. Do you really want that?
This actually would probably be easier to do with a CROSS APPLY instead of a CASE expression.
SELECT
ContractID
, ContractStatusID
, ContractStatusDate
FROM LeaseData.dbo.vContractsAll
CROSS APPLY(
SELECT 1 AS InclBit
WHERE DATEPART(DW, CONVERT(DATE, GETDATE())) = '2'
AND CONVERT(DATE, ContractStatusDate) BETWEEN DATEADD(DAY, -4, CONVERT(DATE, GETDATE())) AND DATEADD(DAY, -3, CONVERT(DATE, GETDATE()))
/* The -2 option is included in the other subset. */
UNION ALL
SELECT 1 AS InclBit
WHERE CONVERT(DATE, ContractStatusDate) = DATEADD(DAY, -2, CONVERT(DATE, GETDATE()))
) i
WHERE NumOfAssets > 0
AND Lessor NOT IN (51,54,55,56,57,59)
AND ContractStatusID = '17'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2018 at 3:07 pm
Thank you Drew. I think I follow what your saying and asking and the possible issues/flaws in my logic.
I'm attaching a more detailed file that explains the daily flow as well as what I'm calling Report #1 (which is already in place and not this thread) and Report #2 which is what I'm trying to accomplish here.
Do you believe that your Cross Apply will handle the attached calendar/sequence of events?
(I already see that my last row doesn't make any sense. I'm really struggling with how to make this logic work all of the time)
January 30, 2018 at 3:16 pm
Here's an updated spreadsheet/calendar. I think this is the correct logic. Just don't know how to make it work (unless you know the Cross Apply thing will work for this)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply