September 29, 2009 at 5:24 am
I am trying to write this code below from Access into SQL Server, but my problem is that I have IIf statements in the code, I think I need to use CASE Statements to conver the code.
How can I write complex CASE to convert the IIF statement below?
SELECT dbo_address.uprn AS HRN, dbo_service.completion_date AS CompletedDate, dbo_service.appointment_1 AS Appt1, dbo_service.no_access_1 AS NA1, IIf([NA1]=[Appt1],"NA1",IIf([NA1]=#1/1/2001#,"NA1",IIf([CompletedDate]=[Appt1],"CompVisit1","UnknAppt1"))) AS Appt1Outcome, dbo_service.appointment_2 AS Appt2, dbo_service.no_access_2 AS NA2, IIf([Appt1Outcome]="CompVisit1","CompOnVisit1",IIf([NA2]=[Appt2],"NA2",IIf([NA2]=#1/1/2001#,"NA2",IIf([CompletedDate]=[Appt2],"CompVisit2",IIf([Appt2]>=Now(),"Appt2Pending","UnknAppt2"))))) AS Appt2Outcome, dbo_service.appointment_3 AS Appt3, dbo_service.no_access_3 AS NA3, IIf([Appt1Outcome]="CompVisit1","CompOnVisit1",IIf([Appt2Outcome]="CompVisit2","CompOnVisit2",IIf([NA3]=[Appt3],"NA3",IIf([NA3]=#1/1/2001#,"NA3",IIf([CompletedDate]=[Appt3],"CompVisit3",IIf([Appt3]>=Now(),"Appt3Pending","UnknAppt3")))))) AS Appt3Outcome, dbo_service.appointment_4 AS Appt4, dbo_service.no_access_4 AS NA4, IIf([Appt1Outcome]="CompVisit1","CompOnVisit1",IIf([Appt2Outcome]="CompVisit2","CompOnVisit2",IIf([Appt3Outcome]="CompVisit3","CompOnVisit3",IIf([NA4]=[Appt4],"NA4",IIf([NA4]=#1/1/2001#,"NA4",IIf([CompletedDate]=[Appt4],"CompVisit4",IIf([Appt4]>=Now(),"Appt4Pending","UnknAppt4"))))))) AS Appt4Outcome, dbo_service.appointment_5 AS Appt5, dbo_service.no_access_5 AS NA5, IIf([Appt1Outcome]="CompVisit1","CompOnVisit1",IIf([Appt2Outcome]="CompVisit2","CompOnVisit2",IIf([Appt3Outcome]="CompVisit3","CompOnVisit3",IIf([Appt4Outcome]="CompVisit4","CompOnVisit4",IIf([NA5]=[Appt5],"NA5",IIf([NA5]=#1/1/2001#,"NA5",IIf([CompletedDate]=[Appt5],"CompVisit5",IIf([Appt5]>=Now(),"Appt5Pending","UnknAppt5")))))))) AS Appt5Outcome, dbo_service.forced_date AS FE, IIf([Appt1Outcome]="CompVisit1","CompOnVisit1",IIf([Appt2Outcome]="CompVisit2","CompOnVisit2",IIf([Appt3Outcome]="CompVisit3","CompOnVisit3",IIf([Appt4Outcome]="CompVisit4","CompOnVisit4",IIf([Appt5Outcome]="CompVisit5","CompOnVisit5",IIf([CompletedDate]=[FE],"CompFE","UnknFE")))))) AS FEOutcome, dbo_service.engineer_id AS EngineerID, dbo_engineer.display_name AS Engineer, dbo_client.name_short AS ContractName, DatePart("ww",[completion_date]) AS Week, DatePart("m",[completion_date]) AS [Month]
FROM ((dbo_address INNER JOIN dbo_service ON dbo_address.id = dbo_service.address_id) INNER JOIN dbo_engineer ON dbo_service.engineer_id = dbo_engineer.id) INNER JOIN dbo_client ON dbo_address.client_id = dbo_client.id
WHERE (((dbo_service.appointment_1)>#4/1/2009#));
September 29, 2009 at 5:35 am
In general, IIF(X,Y,Z) equates to CASE WHEN X THEN Y ELSE Z END
IIF(X,Y,IIF(A,B,C)) would equate to CASE WHEN X THEN Y ELSE CASE WHEN A THEN B ELSE C END END
Hope that helps.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 7:03 am
Thanks for that, I'll give it a go 🙂
September 29, 2009 at 2:23 pm
I have tried the CASE Statment and this is what I come up with for part of it, but have an error I cant get around....
Error :
Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'THEN'.
This is my CASE, where have I gone wrong?
CASE
WHEN CONVERT(VARCHAR(20), ASV.[2nd_na], 103) =
CASE
WHEN CONVERT(VARCHAR(20), ASV.[2nd_na], 103) =
CONVERT(VARCHAR(20), ASV.[booking_date], 103) THEN
CONVERT(VARCHAR(20), ASV.[booking_date], 103)
ELSE CONVERT(VARCHAR(20), ASV.[2nd_na], 103)
END THEN 'NA2'
WHEN CONVERT(VARCHAR(20), ASV.[2nd_na], 103) >=
CONVERT(VARCHAR(20), GETDATE(), 103) THEN 'Appt2Pending'
END THEN 'UnknAppt2'
CASE
WHEN CONVERT(VARCHAR(20), ASV.[2nd_na], 103) =
CONVERT(VARCHAR(20), ASV.[completion_date], 103) THEN 'CompVisit2'
END AS 'Appt2Outcome',
September 29, 2009 at 3:49 pm
I'm sorry i've looked at it for a few minutes now and I can't work out what it is you're trying to do - probably cos i'm not feeling 100%. I'll look again if I remember. Sorry.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 4:15 pm
I reformatted the code a bit, so I could really see what is going on...
Select dbo_address.uprn As HRN
,dbo_service.completion_date As CompletedDate
,dbo_service.appointment_1 As Appt1
,dbo_service.no_access_1 As NA1
-- ,IIf(
-- [NA1] = [Appt1]
-- ,"NA1"
-- ,IIf(
-- [NA1] = #1 / 1 / 2001#
-- ,"NA1"
-- ,IIf([CompletedDate] = [Appt1] ,"CompVisit1" ,"UnknAppt1")
-- )
-- ) As Appt1Outcome
,Case When dbo_service.no_access_1 = dbo_service.appointment_1 Then 'NA1'
When dbo_service.no_access_1 = '20010101' Then 'NA1'
When dbo_service.completion_date = dbo_service.appointment_1 Then 'CompVisit1'
Else 'UnknAppt1'
End As Appt1Outcome
A couple of things you need to be aware of. First, in T-SQL you cannot utilize an alias in the same SELECT statement. If you want to use the alias, you have to use either a derived table, view or CTE where the alias is defined. In your case - that really isn't necessary.
The second thing is that the CASE expression stops processing as soon as it has a true expression. So, we don't have to embed them the same way you would using IIF.
In the above, the logic is:
If no_access_1 equals appointment_date_1 then return NA1
else if no_access_1 equals '20010101' then return NA1
else if completion_date equals appointment_date_1 then return 'CompVisit1'
else return 'UnknAppt1'
I'll look at the others and see if I can figure them out also.
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
September 29, 2009 at 4:37 pm
Okay, I worked through each one and modified the expression - here is the result:
Select ad.uprn As HRN
,sv.completion_date As CompletedDate
,sv.appointment_1 As Appt1
,sv.no_access_1 As NA1
--,IIf(
-- [NA1] = [Appt1]
-- ,"NA1"
-- ,IIf(
-- [NA1] = #1 / 1 / 2001#
-- ,"NA1"
-- ,IIf([CompletedDate] = [Appt1] ,"CompVisit1" ,"UnknAppt1")
-- )
-- ) As Appt1Outcome
,Case When sv.no_access_1 = sv.appointment_1 Then 'NA1'
When sv.no_access_1 = '20010101' Then 'NA1'
When sv.completion_date = sv.appointment_1 Then 'CompVisit1'
Else 'UnknAppt1'
End As Appt1Outcome
,sv.appointment_2 As Appt2
,sv.no_access_2 As NA2
--,IIf(
-- [Appt1Outcome] = "CompVisit1"
-- ,"CompOnVisit1"
-- ,IIf(
-- [NA2] = [Appt2]
-- ,"NA2"
-- ,IIf(
-- [NA2] = #1 / 1 / 2001#
-- ,"NA2"
-- ,IIf(
-- [CompletedDate] = [Appt2]
-- ,"CompVisit2"
-- ,IIf([Appt2] >= Now() ,"Appt2Pending" ,"UnknAppt2")
-- )
-- )
-- )
-- ) As Appt2Outcome
,Case When sv.completion_date = sv.appointment_1 Then 'CompOnVisit1'
When sv.no_access_2 = sv.appointment_2 Then 'NA2'
When sv.no_access_2 = '20010101' Then 'NA2'
When sv.completion_date = sv.appointment_2 Then 'CompVisit2'
When sv.appointment_2 >= getdate() Then 'Appt2Pending'
Else 'UnknAppt2'
End As Appt2Outcome
,sv.appointment_3 As Appt3
,sv.no_access_3 As NA3
--,IIf(
-- [Appt1Outcome] = "CompVisit1"
-- ,"CompOnVisit1"
-- ,IIf(
-- [Appt2Outcome] = "CompVisit2"
-- ,"CompOnVisit2"
-- ,IIf(
-- [NA3] = [Appt3]
-- ,"NA3"
-- ,IIf(
-- [NA3] = #1 / 1 / 2001#
-- ,"NA3"
-- ,IIf(
-- [CompletedDate] = [Appt3]
-- ,"CompVisit3"
-- ,IIf([Appt3] >= Now() ,"Appt3Pending" ,"UnknAppt3")
-- )
-- )
-- )
-- )
-- ) As Appt3Outcome
,Case When sv.completion_date = sv.appointment_1 Then 'CompOnVisit1'
When sv.completion_date = sv.appointment_2 Then 'CompOnVisit2'
When sv.no_access_3 = sv.appointment_3 Then 'NA3'
When sv.no_access_3 = '20010101' Then 'NA3'
When sv.completion_date = sv.appointment_3 Then 'CompVisit3'
When sv.appointment_3 >= getdate() Then 'Appt3Pending'
Else 'UnknAppt3'
End As Appt3Outcome
,sv.appointment_4 As Appt4
,sv.no_access_4 As NA4
--,IIf(
-- [Appt1Outcome] = "CompVisit1"
-- ,"CompOnVisit1"
-- ,IIf(
-- [Appt2Outcome] = "CompVisit2"
-- ,"CompOnVisit2"
-- ,IIf(
-- [Appt3Outcome] = "CompVisit3"
-- ,"CompOnVisit3"
-- ,IIf(
-- [NA4] = [Appt4]
-- ,"NA4"
-- ,IIf(
-- [NA4] = #1 / 1 / 2001#
-- ,"NA4"
-- ,IIf(
-- [CompletedDate] = [Appt4]
-- ,"CompVisit4"
-- ,IIf([Appt4] >= Now() ,"Appt4Pending" ,"UnknAppt4")
-- )
-- )
-- )
-- )
-- )
-- ) As Appt4Outcome
,Case When sv.completion_date = sv.appointment_1 Then 'CompOnVisit1'
When sv.completion_date = sv.appointment_2 Then 'CompOnVisit2'
When sv.completion_date = sv.appointment_3 Then 'CompOnVisit3'
When sv.no_access_4 = sv.appointment_4 Then 'NA4'
When sv.no_access_4 = '20010101' Then 'NA4'
When sv.completion_date = sv.appointment_4 Then 'CompVisit4'
When sv.appointment_4 >= getdate() Then 'Appt4Pending'
Else 'UnknAppt4'
End As Appt4Outcome
,sv.appointment_5 As Appt5
,sv.no_access_5 As NA5
--,IIf(
-- [Appt1Outcome] = "CompVisit1"
-- ,"CompOnVisit1"
-- ,IIf(
-- [Appt2Outcome] = "CompVisit2"
-- ,"CompOnVisit2"
-- ,IIf(
-- [Appt3Outcome] = "CompVisit3"
-- ,"CompOnVisit3"
-- ,IIf(
-- [Appt4Outcome] = "CompVisit4"
-- ,"CompOnVisit4"
-- ,IIf(
-- [NA5] = [Appt5]
-- ,"NA5"
-- ,IIf(
-- [NA5] = #1 / 1 / 2001#
-- ,"NA5"
-- ,IIf(
-- [CompletedDate] = [Appt5]
-- ,"CompVisit5"
-- ,IIf([Appt5] >= Now() ,"Appt5Pending" ,"UnknAppt5")
-- )
-- )
-- )
-- )
-- )
-- )
-- ) As Appt5Outcome
,Case When sv.completion_date = sv.appointment_1 Then 'CompOnVisit1'
When sv.completion_date = sv.appointment_2 Then 'CompOnVisit2'
When sv.completion_date = sv.appointment_3 Then 'CompOnVisit3'
When sv.completion_date = sv.appointment_4 Then 'CompOnVisit4'
When sv.no_access_5 = sv.appointment_5 Then 'NA5'
When sv.no_access_5 = '20010101' Then 'NA5'
When sv.completion_date = sv.appointment_5 Then 'CompVisit5'
When sv.appointment_5 >= getdate() Then 'Appt5Pending'
Else 'UnknAppt5'
End As Appt5Outcome
,sv.forced_date As FE
--,IIf(
-- [Appt1Outcome] = "CompVisit1"
-- ,"CompOnVisit1"
-- ,IIf(
-- [Appt2Outcome] = "CompVisit2"
-- ,"CompOnVisit2"
-- ,IIf(
-- [Appt3Outcome] = "CompVisit3"
-- ,"CompOnVisit3"
-- ,IIf(
-- [Appt4Outcome] = "CompVisit4"
-- ,"CompOnVisit4"
-- ,IIf(
-- [Appt5Outcome] = "CompVisit5"
-- ,"CompOnVisit5"
-- ,IIf([CompletedDate] = [FE] ,"CompFE" ,"UnknFE")
-- )
-- )
-- )
-- )
-- ) As FEOutcome
,Case When sv.completion_date = sv.appointment_1 Then 'CompOnVisit1'
When sv.completion_date = sv.appointment_2 Then 'CompOnVisit2'
When sv.completion_date = sv.appointment_3 Then 'CompOnVisit3'
When sv.completion_date = sv.appointment_4 Then 'CompOnVisit4'
When sv.completion_date = sv.appointment_5 Then 'CompOnVisit5'
When sv.completion_date = sv.forced_date Then 'CompFE'
Else 'UnknFE'
End As FEOutcome
,sv.engineer_id As EngineerID
,en.display_name As Engineer
,cl.name_short As ContractName
,datepart(week, sv.completion_date) As Week -- verify this, not sure I got the right one
,datepart(month, sv.completion_date) As [Month]
From dbo_address ad
Inner Join dbo_service sv On sv.address_id = ad.id
Inner Join dbo_engineer en On en.id = sv.engineer_id
Inner Join dbo_client cl On cl.id = ad.client_id
Where sv.appointment_1 > '20090401';
I added aliases to your tables and used the aliases to reference the columns. It simplifies the code a bit (at least for me). Review what I have done with the above and post back any and all questions you have about what was done here.
Oh, yeah - one more thing, dates are not delimited in T-SQL with the # - and you really should use an unambigous format (which is what I used in the above).
Edit: fixed issues with datepart, don't need to put the style in quotes.
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
September 29, 2009 at 5:08 pm
Nice job sir. 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 29, 2009 at 5:26 pm
Thanks Matt - now we'll see if this works for the OP.
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
September 30, 2009 at 4:03 am
Thanks for this 😀
Sorry for the delay in reply, only just had chance to look at this.
I'm going to read and understand what it does now.
Thanks again 😀
October 2, 2009 at 3:41 am
I am looking at another database now and trying to put together something similar to what was done earlier in this thread, the only problem is that this database is badly designed.
I have a table with only 1 appointment date (booking_date) and 3NA's which is ok and a completed date.
Basically what I am trying to do is
If the NA is not null then lookup against the bookingdate and if this matches then that becomes the relevant Apptdate, if the NA's are null then the booking date is Appt1 and so on, I then want to do similar to earlier notes in this thread and workout the ApptOutcome result.
This below is an attempt at trying to work out an appointment activity query, as I have now got stuck. another problem is that the date field are DATETIME so the time will not match, so I thought about using CONVERT(VARCHAR(20), ASV.[completion_date], 103) to extract only the date is this a good idea?
SELECT ASV.[prop_seql] AS [UPRN],
QASV.[status] AS [Status],
QASVS.[status] AS [SubStatus],
CONVERT(VARCHAR(20), ASV.[booking_date], 103) AS [LatestNextApptDate],
CASE WHEN CONVERT(VARCHAR(20), ASV.[1st_na], 103) IS NULL THEN NULL
WHEN CONVERT(VARCHAR(20), ASV.[1st_na], 103) = CONVERT(VARCHAR(20), ASV.[booking_date], 103) THEN CONVERT(VARCHAR(20), ASV.[booking_date], 103)
ELSE CONVERT(VARCHAR(20), ASV.[1st_na], 103)
END AS [Appt1],
CONVERT(VARCHAR(20), ASV.[1st_na] , 103)AS [NA1],
CASE WHEN CONVERT(VARCHAR(20), ASV.[completion_date], 103) = CASE WHEN CONVERT(VARCHAR(20), ASV.[1st_na], 103) IS NULL THEN NULL
WHEN CONVERT(VARCHAR(20), ASV.[1st_na], 103) = CONVERT(VARCHAR(20), ASV.[booking_date], 103) THEN CONVERT(VARCHAR(20), ASV.[booking_date], 103)
ELSE CONVERT(VARCHAR(20), ASV.[1st_na], 103)
END THEN 'CompOnVisit1'
WHEN CONVERT(VARCHAR(20), ASV.[1st_na], 103) IS NOT NULL THEN 'NA1'
ELSE 'UnknAppt1'
END AS [Appt1Outcome],
CONVERT(VARCHAR(20), ASV.[2nd_na] , 103)AS [NA2],
CONVERT(VARCHAR(20), ASV.[3rd_na] , 103)AS [NA3],
CONVERT(VARCHAR(20), ASV.[completion_date], 103) AS [CompletedDate],
C.[name_short] AS [Contract],
'ASV' AS [JobType]
FROM dbo.contracts C
INNER JOIN dbo.property P
INNER JOIN dbo.servicing_jobs ASV
INNER JOIN dbo.quicklist_service_status QASV ON ASV.[status] = QASV.[id]
INNER 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] > '20090401'
AND C.[name_short] NOT LIKE 'Test'+'%'
ORDER BY QASV.[status], QASVS.[status], ASV.[booking_date]
October 2, 2009 at 7:39 am
I am sorry, but I am not following the logic here. What happens if your NA column is not the same as appt1? What if the booking_date is less than the NA dates?
As for your convert - no, I wouldn't use convert to do the comparison. Using convert returns the date & time. You can't strip the time portion of a datetime column, but you can return the datetime at midnight using the following:
DATEADD(day, DATEDIFF(day, 0, {date column}), 0)
For example:
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0);
The above returns today's date at midnight. With that date, you can now compare and determine if your date columns are the same datetime.
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 8:25 am
am sorry, but I am not following the logic here. What happens if your NA column is not the same as appt1? What if the booking_date is less than the NA dates?
Ok,
The problem I have is that the database only records
Booking Date,
NA1,
NA2,
NA3,
Completed Date
The day any NA happens would be the Appt date for that NA.
Ok this is example of what I’m trying to do
Example dates
Booking Date 05/10/09
NA1 30/09/09
NA2 02/10/09
NA3,
Completed Date
So in this example the NA1 date is 30/09/09 therefore I want to show Appt1 as 30/09/09 as the booking date is not equal to the NA1 date, then again for Appt2 I would show 02/10/09 the booking date is not equal to the NA2 date, and as the NA3 date is null I would want to show the Appt3 date the same as the booking date as the booking date is either the latest or next appointment date.
From this I then want to show an ApptOutcome field for each Appt. In this case I would want to show Appt1Outcome as NA1 and the Appt2Outcome as NA2 and then for Appt3 I would show Appt3Pending as todays date hasn’t got to the date of the Appt3 yet. I would also want to show CompletedOnVisit if the Completed date was populated. So if the Completed date was showing as 02/10/09 then I want to show the Appt2Outcome as CompletedOnVisit2, If the ApptOutcome is unknown then I want to show relevant ApptOutcome as UnknAppt
Hope this helps
October 2, 2009 at 9:41 am
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?
If the dates are NULL - you can use the following:
COALESCE(asv.[1st_NA], asv.booking_date) AS appt1Date
COALESCE(asv.[2nd_NA], asv.booking_date) AS appt2Date
COALESCE(asv.[3rd_NA], asv.booking_date) AS appt3Date
COALESCE returns the first non null value. Now, if the columns are actually empty strings ('') - then we do the following:
COALESCE(NULLIF(asv.1st_NA], ''), asv.booking_date) AS appt1Date
The NULLIF returns a NULL if the column value equals the second parameter - in this case an empty string.
You don't really need to check for the dates being equal - and returning the booking_date if they are. If they are equal - then it doesn't matter which one you return. Your check for the outcome then becomes:
CASE WHEN asv.[1st_NA] IS NOT NULL THEN 'NA1' ELSE 'UnknAppt1' END AS appt1Outcome
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 1:26 pm
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 🙂
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply