November 1, 2017 at 10:49 am
Hello,
I'm hoping someone can help with this please.
I need to join the 2 below case statements into 1, they work fine independent of one another but when I have attempted to join them it does not work.
Part 1
CASE WHEN Activity_Active_8.RBLK IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31') ANDCWORK_ActualDate NOT BETWEEN '2001-01-01' AND '2050-01-01' THEN(SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays - SAP.NetworkMilestone.WPLAN_ActualDate_WorkingDays)- (DATEDIFF(day , Activity_Active_8.RBLK_ActiveDate ,SAP.NetworkMilestone.PDATE_ActualDate) - DATEDIFF(wk ,Activity_Active_8.RBLK_ActiveDate , SAP.NetworkMilestone.PDATE_ActualDate) * 2)WHEN Activity_Active_8.RBLK NOT IN ('21' , '22' , '23' , '24' , '25' , '26' ,'27' , '28' , '29' , '30' , '31') AND CWORK_ActualDate NOT BETWEEN '2000-01-01'AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.WPLAN_ActualDate_WorkingDays) ELSE 0 END
Part 2
CASE WHEN Activity_Active_8.RBLK IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31') ANDCWORK_ActualDate BETWEEN '2001-01-01' AND '2050-01-01' THEN(SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays - SAP.NetworkMilestone.CWORK_ActualDate_WorkingDays)- (DATEDIFF(day , Activity_Active_8.RBLK_ActiveDate ,SAP.NetworkMilestone.PDATE_ActualDate) - DATEDIFF(wk ,Activity_Active_8.RBLK_ActiveDate , SAP.NetworkMilestone.PDATE_ActualDate) * 2)WHEN Activity_Active_8.RBLK NOT IN ('21' , '22' , '23' , '24' , '25' , '26' ,'27' , '28' , '29' , '30' , '31') AND CWORK_ActualDate BETWEEN '2000-01-01' AND'2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.CWORK_ActualDate_WorkingDays) ELSE 0 END
Thank you,
Fred
November 1, 2017 at 11:04 am
Firstly, let's have something that's (marginally) more readable than a single line of text:
Having a bit of a guess, but maybe this?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 11:14 am
Hi Thom,
Basically it would be 3 when statements together like below.
CASE WHEN Activity_Active_8.RBLK IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31') ANDCWORK_ActualDate NOT BETWEEN '2001-01-01' AND '2050-01-01' THEN(SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.WPLAN_ActualDate_WorkingDays) - (DATEDIFF(day ,Activity_Active_8.RBLK_ActiveDate , SAP.NetworkMilestone.PDATE_ActualDate) -DATEDIFF(wk , Activity_Active_8.RBLK_ActiveDate ,SAP.NetworkMilestone.PDATE_ActualDate) * 2)
WHEN Activity_Active_8.RBLK NOT IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31') ANDCWORK_ActualDate NOT BETWEEN '2000-01-01' AND '2050-01-01' THEN(SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.WPLAN_ActualDate_WorkingDays)
WHENActivity_Active_8.RBLK IN ('21' , '22' , '23' , '24' , '25' , '26' , '27' ,'28' , '29' , '30' , '31') AND CWORK_ActualDate BETWEEN '2001-01-01' AND'2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.CWORK_ActualDate_WorkingDays) - (DATEDIFF(day , Activity_Active_8.RBLK_ActiveDate, SAP.NetworkMilestone.PDATE_ActualDate) - DATEDIFF(wk ,Activity_Active_8.RBLK_ActiveDate , SAP.NetworkMilestone.PDATE_ActualDate) * 2)
WHEN Activity_Active_8.RBLK NOT IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31') ANDCWORK_ActualDate BETWEEN '2000-01-01' AND '2050-01-01' THEN(SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.CWORK_ActualDate_WorkingDays) else 0 END
Fred
November 1, 2017 at 11:16 am
Sorry 4
November 1, 2017 at 11:30 am
Yes that is what I am attempting to get to work Thom, but it is not excepted.
If I put portions 1&2 or 3 and 4 together it will run but not all 4 together.
Fred
November 1, 2017 at 11:42 am
fred_c.major - Wednesday, November 1, 2017 11:30 AMYes that is what I am attempting to get to work Thom, but it is not excepted.
If I put portions 1&2 or 3 and 4 together it will run but not all 4 together.Fred
Care to share what you mean by won't work when all are together? If you are getting an error message, then provide the error message. If it isn't returning the expected results, say so.
If the latter, a little hard to trouble shoot with just a code snippet to work with unfortunately.
November 1, 2017 at 12:43 pm
Lynn Pettis - Wednesday, November 1, 2017 11:42 AMfred_c.major - Wednesday, November 1, 2017 11:30 AMYes that is what I am attempting to get to work Thom, but it is not excepted.
If I put portions 1&2 or 3 and 4 together it will run but not all 4 together.Fred
Care to share what you mean by won't work when all are together? If you are getting an error message, then provide the error message. If it isn't returning the expected results, say so.
If the latter, a little hard to trouble shoot with just a code snippet to work with unfortunately.
Lynn beat me to it. "Does not work" doesn't mean a lot.
"My car doesn't work." Can you tell me why based on just that statement? No. A far better statement would be: "My car isn't working properly. I can drive it but the engine keep over revving when I try to accelerate. Sometimes it'll be fine, but most of the time the RPM will go up by 1,000 - 3,000 before dropping again and the car starts to speed up. This happens more frequently in the higher gears, but it does still happen in 2nd and 3rd." The same logic applies here, the latter statement is much more informative (by the way, I really do have that problem with my car, so if someone does have any ideas PM me, as 3 garages have failed to fix/diagnose the problem so far..! -_-).
If it's the expected values that aren't being returned we're going to need some DDL and sample data here (in a consumable format), as well as what your would expect from your data. We'll then be able to trouble shoot further. if it's an error, we need that error (and DDL might be useful as well).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 1, 2017 at 1:30 pm
This was removed by the editor as SPAM
November 2, 2017 at 2:42 am
Thom A - Wednesday, November 1, 2017 12:43 PM... (by the way, I really do have that problem with my car, so if someone does have any ideas PM me, as 3 garages have failed to fix/diagnose the problem so far..! -_-)...
Oh Thom, without knowing the make and model??
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
November 2, 2017 at 2:45 am
ChrisM@Work - Thursday, November 2, 2017 2:42 AMThom A - Wednesday, November 1, 2017 12:43 PM... (by the way, I really do have that problem with my car, so if someone does have any ideas PM me, as 3 garages have failed to fix/diagnose the problem so far..! -_-)...Oh Thom, without knowing the make and model??
Ha! (Peugeot 207 2009 model) :haha:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 2, 2017 at 5:06 am
I did not realize I was in a comedy forum, lol.
Problem was solved by putting it together in chunks into query window one after the other instead of one chunk of code, strange.
Thank you for the jokes anyway.
Fred
November 2, 2017 at 5:10 am
fred_c.major - Thursday, November 2, 2017 5:06 AMI did not realize I was in a comedy forum, lol.
Problem was solved by putting it together in chunks into query window one after the other instead of one chunk of code, strange.
Thank you for the jokes anyway.
Fred
Fred, there are ways to make this kind of query much more readable - which translates to far easier to maintain.
If you share your query and confirm which version of SQL Server you're using, someone will show you how it's done.
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
November 2, 2017 at 8:58 am
fred_c.major - Thursday, November 2, 2017 5:06 AMI did not realize I was in a comedy forum, lol.
Problem was solved by putting it together in chunks into query window one after the other instead of one chunk of code, strange.
Thank you for the jokes anyway.
Fred
And understand that the tangents just make things fun, and sometimes you learn something new which is always good even if unrelated to the question at hand.
November 2, 2017 at 6:50 pm
Thom A - Wednesday, November 1, 2017 11:04 AMFirstly, let's have something that's (marginally) more readable than a single line of text:CASE WHEN Activity_Active_8.RBLK IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31')AND CWORK_ActualDate NOT BETWEEN '2001-01-01' AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays - SAP.NetworkMilestone.WPLAN_ActualDate_WorkingDays) -(DATEDIFF(day , Activity_Active_8.RBLK_ActiveDate ,SAP.NetworkMilestone.PDATE_ActualDate) -DATEDIFF(wk ,Activity_Active_8.RBLK_ActiveDate , SAP.NetworkMilestone.PDATE_ActualDate) * 2)WHEN Activity_Active_8.RBLK NOT IN ('21' , '22' , '23' , '24' , '25' , '26' ,'27' , '28' , '29' , '30' , '31')AND CWORK_ActualDate NOT BETWEEN '2000-01-01' AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.WPLAN_ActualDate_WorkingDays)ELSE 0ENDCASE WHEN Activity_Active_8.RBLK IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31')AND CWORK_ActualDate BETWEEN '2001-01-01' AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays - SAP.NetworkMilestone.CWORK_ActualDate_WorkingDays) -(DATEDIFF(day , Activity_Active_8.RBLK_ActiveDate ,SAP.NetworkMilestone.PDATE_ActualDate) -DATEDIFF(wk ,Activity_Active_8.RBLK_ActiveDate , SAP.NetworkMilestone.PDATE_ActualDate) * 2)WHEN Activity_Active_8.RBLK NOT IN ('21' , '22' , '23' , '24' , '25' , '26' ,'27' , '28' , '29' , '30' , '31')AND CWORK_ActualDate BETWEEN '2000-01-01' AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.CWORK_ActualDate_WorkingDays)ELSE 0ENDHaving a bit of a guess, but maybe this?
--WHEN 1CASE WHEN Activity_Active_8.RBLK IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31')AND CWORK_ActualDate NOT BETWEEN '2001-01-01' AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays - SAP.NetworkMilestone.WPLAN_ActualDate_WorkingDays) -(DATEDIFF(day , Activity_Active_8.RBLK_ActiveDate ,SAP.NetworkMilestone.PDATE_ActualDate) -DATEDIFF(wk ,Activity_Active_8.RBLK_ActiveDate , SAP.NetworkMilestone.PDATE_ActualDate) * 2)--WHEN 2WHEN Activity_Active_8.RBLK NOT IN ('21' , '22' , '23' , '24' , '25' , '26' ,'27' , '28' , '29' , '30' , '31')AND CWORK_ActualDate NOT BETWEEN '2000-01-01' AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.WPLAN_ActualDate_WorkingDays)--WHEN 3WHEN Activity_Active_8.RBLK IN ('21' ,'22' , '23' , '24' , '25' , '26' , '27' , '28' , '29' , '30' , '31')AND CWORK_ActualDate BETWEEN '2001-01-01' AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays - SAP.NetworkMilestone.CWORK_ActualDate_WorkingDays) -(DATEDIFF(day , Activity_Active_8.RBLK_ActiveDate ,SAP.NetworkMilestone.PDATE_ActualDate) -DATEDIFF(wk ,Activity_Active_8.RBLK_ActiveDate , SAP.NetworkMilestone.PDATE_ActualDate) * 2)--WHEN 4WHEN Activity_Active_8.RBLK NOT IN ('21' , '22' , '23' , '24' , '25' , '26' ,'27' , '28' , '29' , '30' , '31')AND CWORK_ActualDate BETWEEN '2000-01-01' AND '2050-01-01' THEN (SAP.NetworkMilestone.PDATE_ActualDate_WorkingDays -SAP.NetworkMilestone.CWORK_ActualDate_WorkingDays)ELSE 0END
Aw dude! How did you post that! It's perfect and you can select the code from it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2017 at 2:09 am
Jeff Moden - Thursday, November 2, 2017 6:50 PMAw dude! How did you post that! It's perfect and you can select the code from it.
I assume you're not being sarcastic? 🙂
I've taken to using Visual Studio Code when pasting large amounts of code onto SSC now, or when at home (as SSMS isn't on Linux). It pastes the code into the text box (if you click the Cancel button on the pop-up about formatting) as if it it's in <pre> tags and keeps the formatting.
Considering that when I paste the SSC removes a bounch of whitespace and makes things look really odd, it just makes it easier (although quotes don't seem to like it), as I can just add the whitespace back in easier, etc.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply