October 6, 2009 at 9:01 am
Using the last example I had - which I am assuming is the one that you are using for JLOutcome, it should be the same as this:
if NA1=Appt1 then NA1
if CompletedDate = Appt1 then CompVisit1
if Appt1>= Today then Appt1Pending
Else UnknAppt1
CASE WHEN ASV.[1st_NA] IS NOT NULL THEN 'NA1'
WHEN DATEADD(day, DATEDIFF(day, 0, ASV.[completion_date]), 0) =
DATEADD(day, DATEDIFF(day, 0,
(CASE WHEN ASV.[1st_NA] IS NULL THEN ASV.[booking_date]
WHEN ASV.[1st_NA] < ASV.[booking_date] THEN ASV.[1st_NA]
ELSE ASV.[booking_date]
END), 0) THEN 'CompVisit1'
WHEN (CASE WHEN ASV.[1st_NA] IS NULL THEN ASV.[booking_date]
WHEN ASV.[1st_NA] < ASV.[booking_date] THEN ASV.[1st_NA]
ELSE ASV.[booking_date]
END) >= GETDATE() THEN 'Appt1Pending'
ELSE 'UnknAppt1'
END AS [Appt1Outcome],
So, now you need the following logic:
if Appt1Outcome = CompVisit1 then NULL
if NA2=Appt2 then NA2
if CompletedDate = Appt2 then CompVisit2
if Appt2>= Today then Appt2Pending
Else UnknAppt2
With the above, you build the case statement as:
CASE WHEN {calculation where Appt1Outcome = CompVisit1} THEN NULL
WHEN {NA2 column = {calculation to determine appt2}} THEN 'NA2'
WHEN {completed date = {calculation to determine appt2}} THEN 'CompVisit2'
WHEN {calculation to determine appt2 >= GETDATE()} THEN 'Appt2Pending'
ELSE 'UnknAppt2'
END AS Appt2OutCome
If you need to AND any of the criteria - put that AND inside the WHEN clause of the check you need it in.
Also, when checking a column for NULL there is no reason to use DATEADD. Simplifies that checking so we can easily build the check as:
CASE WHEN ASV.[1st_NA] IS NULL AND ASV.[2nd_NA] IS NOT NULL THEN 'NA2'
WHEN...
END AS Appt2Outcome
Let me know if this helps
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 7, 2009 at 2:44 pm
I've had a look now at what you suggested and have now got a picture of Appts 🙂
Thanks for all the help, we eventually got there 😀
Can you recomend any books that would be good read and let me get to grips with more things in SQL Server. I've started to look at articles and things on this site which is good
Thanks again.
Jez
November 17, 2009 at 7:07 am
By using this SQL I had done with your help, it seemed to be fine until I been looking at some data closer, I have found that the Appt3Outcome doesnt quite do what it should be.
This is what I want to achieve
If Appt1 = CompDate then NULL
If Appt2 = CompDate then NULL
If NA3 = Appt3 then NA3
If Appt3 = CompDate then Appt3Comp
If Appt3 > TodayDate then Appt3Pending
If NA3 IS NULL AND Appt3 IS NULL then NULL
ELSE UnknownAppt3
CASE
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[1st_na]), 0) IS NULL AND DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0) IS NULL AND DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) IS NULL THEN NULL
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) IS NOT NULL THEN 'NA3'
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[completion_date]), 0) =
(CASE WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0) IS NULL THEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0)
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0) < DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0) THEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0)
ELSE DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0)
END) THEN NULL
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[completion_date]), 0) <>
(CASE WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) IS NULL THEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0)
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) < DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0) THEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0)
ELSE DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0)
END) THEN NULL
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[completion_date]), 0) =
(CASE WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) IS NULL THEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0)
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) < DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0) THEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0)
ELSE DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0)
END) THEN 'CompVisit3'
WHEN (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) IS NULL THEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0)
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) < DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0) THEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0)
ELSE DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[booking_date]), 0)
END) >= GETDATE() THEN 'Appt3Pending'
ELSE 'UnknAppt3'
END AS [Appt3Outcome],
Attached is the full SQL
November 17, 2009 at 11:16 am
I am going to need some clarification on what is not working here. How are the results not as expected and what have you found to be the problem?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply