October 2, 2009 at 1:56 pm
jez.lisle (10/2/2009)
Okay, can the NA dates be less than the booking date? Are the NA dates NULL or empty strings if they don't have a date?
Yes the NA dates could be less than the booking date, you could find that any of the NA date be less than the booking date, allthough in theory it should only either NA1 or NA2 that would be more likely less than the booking date
The NA dates are also NULL if they dont have a date input.
Thanks for the help so far, I'm going to have a look at this and understand it 🙂
Well then, the question becomes:
If NA1 date is less than booking date - what do you want for Appt1?
If NA1 date is greater than booking date - what do you want for Appt1?
If NA1 date is NULL - use booking date...
If NA1 equals booking date - use either one...
Written out using CASE - it would be:
CASE WHEN asv.[1st_NA] IS NULL THEN booking_date
WHEN asv.[1st_NA] < asv.booking_date THEN ????
WHEN asv.[1st_NA] > asv.booking_date THEN ????
ELSE asv.[1st_NA]
END AS Appt1Date
Once we have this down - we can then use the same logic to determine the Outcome.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 2, 2009 at 2:52 pm
Sorry its complex 🙂
Well then, the question becomes:
If NA1 date is less than booking date - what do you want for Appt1?
If NA1 date is greater than booking date - what do you want for Appt1?
If NA1 date is NULL - use booking date...
If NA1 equals booking date - use either one...
If NA1 is less than booking date then Appt1 is NA1 date
If NA1 is greater than booking date -system shouldnt allow that to happen
If NA1 is NULL then use booking date
and yes if both match then use either
October 2, 2009 at 3:47 pm
Well, you just about wrote it 😉
If NA1 is less than booking date then Appt1 is NA1 date
If NA1 is greater than booking date -system shouldnt allow that to happen
If NA1 is NULL then use booking date
and yes if both match then use either
Here it is using CASE:
CASE WHEN apv.[1st_NA] IS NULL THEN apv.booking_date
WHEN apv.[1st_NA] < apv.booking_date THEN apv.[1st_NA]
WHEN apv.[1st_NA] > apv.booking_date THEN '19000101' -- this shouldn't happen
ELSE apv.booking_date
END AS Appt1Date
You can use the same pattern to determine the other appointment dates. Now, we need to determine the outcome - but, before we do that what are the rules that we will use?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 4, 2009 at 2:56 pm
I've been looking at this and when I run the query looking at the NA1's I get 19000101 as a result when the NA dates are actually less than the Booking Date, which find a bit odd as we said that 19000101 should only appear when the NA1 dates is greater than the booking date.
LatestNextApptDate Appt1Date NA1
01/08/2009 19000101 31/07/2009
Why would this be?
My next questions is when I've been looking at the NA2 to create the Appt2Date.
In an If statement you can have an IF AND statement can you use something similar on CASE. As I need to check different things when creating the Appt2Date
If NA1 is null and NA2 is null then null
If NA2 is less than booking date then Appt2 is NA2 date
If NA2 is greater than booking date then 19000101
and if they both match use either
October 4, 2009 at 3:35 pm
If your date is not null, but is actually an empty string - the empty string is evaluated as a 0 which is 19000101. Not sure that is the problem you have here. Do your date columns contain the time portion? If so, then you have to take that into consideration also.
Based upon the logic, we should only see 19000101 if the value in the column apv.[1st_NA] is greater than the value in apv.booking_date. Two ways this can happen - first, if booking_date is midnight time (e.g. 2009-08-24 00:00:00.000), and the [1st_NA column has time (e.g. 2009-08-24 01:01:01.000) - that is greater than your booking date. If that is possible, change the calculation to:
WHEN DATEADD(day, DATEDIFF(day, 0, apv.[1st_NA]), 0) > apv.booking_date THEN '19000101'
The second possibility is that these columns are not datetime columns. If they are not datetime columns, the comparison is going to be done using the rules for that data type which could cause some rather unusual results.
As for whether or not you can use AND in CASE - yes, you can use any valid boolean expression. So, we could have:
CASE WHEN apv.[1st_NA] IS NULL AND apv.[2nd_NA] IS NULL THEN apv.booking_date
WHEN apv.[2nd_NA] < apv.booking_date THEN apv.[2nd_NA]
WHEN apv.[2nd_NA] > apv.booking_date THEN '19000101' -- this shouldn't happen
ELSE apv.booking_date
END AS Appt2Date
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 4, 2009 at 3:53 pm
If your date is not null, but is actually an empty string - the empty string is evaluated as a 0 which is 19000101. Not sure that is the problem you have here. Do your date columns contain the time portion? If so, then you have to take that into consideration also.
Based upon the logic, we should only see 19000101 if the value in the column apv.[1st_NA] is greater than the value in apv.booking_date. Two ways this can happen - first, if booking_date is midnight time (e.g. 2009-08-24 00:00:00.000), and the [1st_NA column has time (e.g. 2009-08-24 01:01:01.000) - that is greater than your booking date. If that is possible, change the calculation to:
WHEN DATEADD(day, DATEDIFF(day, 0, apv.[1st_NA]), 0) > apv.booking_date THEN '19000101'
The second possibility is that these columns are not datetime columns. If they are not datetime columns, the comparison is going to be done using the rules for that data type which could cause some rather unusual results.
No these columns are both DATETIME fields. I thought about the getting rid of the time section before and used CONVERT (VARCHAR(20), ASV.[booking_date], 103) as I'm not interested in the time of the Appts is what you have used above better and a safer option for separating out the Date from DATETIME?
Thanks for the info about CASE AND 🙂 I'll look at that now
October 4, 2009 at 4:54 pm
Using CONVERT to a VARCHAR(20) will not remove the time portion. And, yes - the method I show is one of the fastest methods for zeroing out the time portion of a datetime column.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 5, 2009 at 7:44 am
Ok brilliant, now I have created the Appointment picture 😀
Thanks for the help in doing that!!
This below is now the query I have, I now need to understand how I can create an ApptOutcome field using results that are in the fields I have created.
Appt1Outcome
How can I write a case that does this
if NA1=Appt1 then NA1
if CompletedDate = Appt1 then CompVisit1
if Appt1>= Today then Appt1Pending
Else UnknAppt1
Appt2Outcome
if Appt1Outcome = CompVisit1 then NULL
if NA2=Appt2 then NA2
if CompletedDate = Appt2 then CompVisit2
if Appt2>= Today then Appt2Pending
Else UnknAppt2
Appt3Outcome
if Appt1Outcome = CompVisit1 then NULL
if Appt1Outcome = CompVisit2 then NULL
if NA3=Appt3 then NA3
if CompletedDate = Appt3 then CompVisit3
if Appt3>= Today then Appt3Pending
Else UnknAppt3
SELECT ASV.[prop_seql] AS [UPRN],
QASV.[status] AS [Status],
QASVS.[status] AS [SubStatus],
DATEADD(day, DATEDIFF(day, 0, ASV.[booking_date]), 0) AS [LatestNextApptDate],
CASE
WHEN ASV.[1st_NA] IS NULL THEN ASV.[booking_date]
WHEN ASV.[1st_NA] < ASV.[booking_date] THEN ASV.[1st_NA]
WHEN ASV.[1st_NA] > ASV.[booking_date] THEN '19000101'
ELSE ASV.[booking_date]
END AS Appt1Date,
DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[1st_na]), 0) AS [NA1],
CASE
WHEN ASV.[1st_NA] IS NULL AND ASV.[2nd_NA] IS NULL THEN NULL
WHEN ASV.[2nd_NA] < ASV.[booking_date] THEN ASV.[2nd_NA]
WHEN ASV.[2nd_NA] > ASV.[booking_date] THEN '19000101'
ELSE ASV.[booking_date]
END AS [Appt2Date],
DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0) AS [NA2],
CASE
WHEN ASV.[1st_na] IS NULL AND ASV.[2nd_na] IS NULL AND ASV.[3rd_na] IS NULL THEN NULL
WHEN ASV.[2nd_na] IS NULL AND ASV.[3rd_na] IS NULL THEN NULL
WHEN ASV.[3rd_na] < ASV.[booking_date] THEN ASV.[3rd_na]
WHEN ASV.[3rd_na] > ASV.[booking_date] THEN '19000101'
ELSE ASV.[booking_date]
END AS [Appt3Date],
DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[3rd_na]), 0) AS [NA3],
DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[completion_date]), 0) AS [CompletedDate],
C.[name_short] AS [Contract],
'ASV' AS [JobType]
FROM dbo.contracts C
RIGHT OUTER JOIN dbo.property P
RIGHT OUTER JOIN dbo.servicing_jobs ASV
LEFT OUTER JOIN dbo.quicklist_service_status QASV ON ASV.[status] = QASV.[id]
LEFT OUTER JOIN dbo.quicklist_service_status_sub QASVS ON ASV.[status_sub] = QASVS.[id]
ON P.[prop_seql] = ASV.[prop_seql] ON C.[id] = P.[contract_id]
WHERE ASV.[booking_date] > '20090901'
AND C.[name_short] NOT LIKE 'Test'+'%'
AND NOT QASV.[status] = 'Cancelled'
AND NOT QASV.[status] = 'Void'
ORDER BY QASV.[status], QASVS.[status], ASV.[booking_date]
October 5, 2009 at 7:59 am
I don't have time right at this moment to write it all up - but take a look at the previous query we worked on. The rules appear to be the same - so the logic in the other query can be applied the same as this one.
Basically, you have to repeat the logic in each case expression that will determine whether or not the completed date equals the appointment date.
I'll take another look at this later today.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 5, 2009 at 8:42 am
No worries, I'll have a go myself and comeback to you when I get stuck
Thanks.
October 5, 2009 at 9:35 am
From looking at the CASE my main problem I dont understand is how I can use a field that I have built using a previous CASE Statement in my new CASE Statement to create another field
CASE
WHEN ASV.[1st_na] IS NOT NULL THEN 'NA1'
WHEN ASV.[completion_date] = [Appt1Date] THEN 'CompVisit1'
WHEN [Appt1Date] >= GETDATE() THEN 'Appt1Pending'
ELSE 'UnknAppt1'
END AS [Appt1Outcome],
as it will error like below
Msg 207, Level 16, State 1, Line 14
Invalid column name 'Appt1Date'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'Appt1Date'.
I thought about a nested CASE like this and it seems to have worked on the face of it. Is this a way to go?
CASE
WHEN ASV.[1st_na] IS NOT NULL THEN 'NA1'
WHEN ASV.[completion_date] = CASE
WHEN ASV.[1st_NA] IS NULL THEN ASV.[booking_date]
WHEN ASV.[1st_NA] < ASV.[booking_date] THEN ASV.[1st_NA]
WHEN ASV.[1st_NA] > ASV.[booking_date] THEN '19000101'
ELSE ASV.[booking_date]
END 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]
WHEN ASV.[1st_NA] > ASV.[booking_date] THEN '19000101'
ELSE ASV.[booking_date]
END >= GETDATE() THEN 'Appt1Pending'
ELSE 'UnknAppt1'
END AS [Appt1Outcome],
Further investigation shows that when the Appt1 date matches the Comp date it shows as UnknAppt1 which is incorrect
October 5, 2009 at 9:58 am
jez.lisle (10/5/2009)
CASE
WHEN ASV.[1st_na] IS NOT NULL THEN 'NA1'
WHEN ASV.[completion_date] = CASE
WHEN ASV.[1st_NA] IS NULL THEN ASV.[booking_date]
WHEN ASV.[1st_NA] < ASV.[booking_date] THEN ASV.[1st_NA]
WHEN ASV.[1st_NA] > ASV.[booking_date] THEN '19000101'
ELSE ASV.[booking_date]
END 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]
WHEN ASV.[1st_NA] > ASV.[booking_date] THEN '19000101'
ELSE ASV.[booking_date]
END >= GETDATE() THEN 'Appt1Pending'
ELSE 'UnknAppt1'
END AS [Appt1Outcome],
Further investigation shows that when the Appt1 date matches the Comp date it shows as UnknAppt1 which is incorrect
Yes, you cannot reference a derived column in the select list in SQL because it hasn't been created yet. The nested CASE is the correct way to approach it - but I am not sure we need everything.
You can probably remove the check for 19000101 - because if you have that case, then you don't really have an appointment date for that NA.
There is a way you can do this so you can use your columns - lookup in Books Online on Common Table Expressions (CTE).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 5, 2009 at 3:28 pm
I've been looking over the (CTE) Common Table Expressions sites on google. I understand some of it, but struggling to see stuff about using it within CASE Statements...
Anyway I have tried the CASE again from earlier and seemed to have rectifyed the issue I had in the previous thread where it didnt match the completion date to the appointment date, taking out the check for 19000101 resolved an issue and they match.
I then started to look at the same again for Appt2Outcome and wrote the following. For some reason the query doesnt like the syntax around the AND on this line END AND DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0) IS NULL THEN ''
Where have I gone wrong?
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 THEN NULL
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 AND DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0) IS NULL THEN ''
WHEN DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[2nd_na]), 0) IS NOT NULL THEN 'NA2'
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 'CompVisit2'
WHEN
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 >= GETDATE() THEN 'Appt2Pending'
ELSE 'UnknAppt2'
END AS [Appt2Outcome],
October 5, 2009 at 4:50 pm
Okay, as far as using CTE's - that is something you can look at in the future. It is just a way of 'naming' a query and using the named query. For example, we could have a CTE like:
;WITH myCTE (MyID, MyCalc)
AS (SELECT ID, CASE WHEN ID > 1000 THEN 'MyCalculation' END AS MyCalc FROM MyTable)
SELECT ID, CASE WHEN MyCalc = 'MyCalculation' THEN 'Matched' END AS MyOtherCalc
FROM MyTable t1
JOIN myCTE t2 ON t2.ID = t1.ID;
This is the same as using a derived table - as in:
SELECT ID, CASE WHEN MyCalc = 'MyCalculation' THEN 'Matched' END AS MyOtherCalc
FROM MyTable t1
JOIN (SELECT ID, CASE WHEN ID > 1000 THEN 'MyCalculation' END AS MyCalc FROM MyTable) AS t2 ON t2.ID = t1.ID;
With that said, the problem you have in your attempt is that END is used to END a CASE expression. Where you have put the END is not where you actually want to END the CASE expression and that is why you are getting the error.
Let's go back to the original statement:
CASE WHEN ASV.[1st_na] IS NOT NULL THEN 'NA1'
WHEN ASV.[completion_date] = [Appt1Date] THEN 'CompVisit1'
WHEN [Appt1Date] >= GETDATE() THEN 'Appt1Pending'
ELSE 'UnknAppt1'
END AS [Appt1Outcome],
In the above statement, we need to replace [Appt1Date] with the calculation to derive the appointment 1 date. So, we should end up with:
CASE WHEN ASV.[1st_na] IS NOT NULL THEN 'NA1'
WHEN ASV.[completion_date] =
({calculation to derive [Appt1Date]}) THEN 'CompVisit1'
WHEN ({calculation to derive [Appt1Date]}) >= GETDATE() THEN 'Appt1Pending'
ELSE 'UnknAppt1'
END AS [Appt1Outcome],
So, we should be able to just do this:
CASE WHEN ASV.[1st_na] IS NOT NULL THEN 'NA1'
WHEN ASV.[completion_date] =
(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) 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],
Now, the problem we might run into is the fact that we have time included in our date columns. If we have time included, then we have to zero out the time to be able to compare them - or we have to use a range comparison. So, zero out each one of the date columns for the comparison in the above and we end up with:
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],
I am still not sure we need to zero out the dates - but I cannot be sure without sample data that actually shows what we are comparing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 6, 2009 at 4:23 am
If I use just this ASV.[1st_NA] in the query then from the results in the example attachement you can see that it doesnt show same results as when we use DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[1st_NA]), 0)
JWOutcome is using ASV.[1st_NA]
JLOutcome uses DATEADD(DAY, DATEDIFF(DAY, 0, ASV.[1st_NA]), 0)
From this though how can I use a CASE AND so that I can compare 2 fields that arent proper fields like in the previous thread Posted Yesterday @ 10:28:12 PM
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply