Convert IIF Access to CASE THEN sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert IIF Access to CASE THEN sql server

Author

Topic

jonathancr29
Starting Member

2 Posts

Posted - 2013-09-19 : 17:11:21

Hey im new in sql server programming, and now i have a little problem, i have an access db and i need to convert some access queries to sql server, but i dont really understand how to conver the iif access to case then sql server. Can anyone help me pleasejavascript:insertsmilie('

')

SELECT CatAIncidentbyDateAndORgID.Incident_ID,
CatAIncidentbyDateAndORgID.Incident_Date,
CatAIncidentbyDateAndORgID.Incident_Desc,

IIf (([Organisation_ID]=266) Or
([Organisation_ID]=438) Or
([Organisation_ID]=436) Or
([Organisation_ID]=435) Or
([Organisation_ID]=437) Or
([Organisation_ID])=421,90) AS NoDaysBeforeDue,

DMax("New_Due_Date","tbl_Incident_Final_Extension","[Incident_ID]=" & CatAIncidentByDateandOrgID.Incident_ID) AS ExtDate,
CatAIncidentbyDateAndORgID.Incident_ID, DateAdd("d",[Incident_date],90) AS DueDate,

IIf([DueDate]<Now(),DateDiff("d",[DueDate],Now()),0) AS OverDueDays,
CatAIncidentbyDateAndORgID.Organisation_ID, tbl_Incident_Final_Report.Final_Report_Date

FROM CatAIncidentbyDateAndORgID
LEFT JOIN tbl_Incident_Final_Report
ON CatAIncidentbyDateAndORgID.Incident_ID = tbl_Incident_Final_Report.Incident_ID
WHERE (((CatAIncidentbyDateAndORgID.Incident_Date) Between #1/1/2009# And #7/31/2013#) AND ((tbl_Incident_Final_Report.Final_Report_Date) Is Null));

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 17:18:07

The Overduedays would be like this:

case when [DueDate]< GETDATE() then DateDiff("d",[DueDate],GETDATE()) else 0 end OverDueDays

The other IIF expression below does not seem to be correct. You should see 2 commas in there, but there is only one in what you posted.

IIf (([Organisation_ID]=266) Or 
([Organisation_ID]=438) Or 
([Organisation_ID]=436) Or 
([Organisation_ID]=435) Or 
([Organisation_ID]=437) Or 
([Organisation_ID])=421,90) AS NoDaysBeforeDue

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-19 : 17:24:23

quote:
Originally posted by James K

The Overduedays would be like this:
case when [DueDate]< GETDATE() then DateDiff("d",[DueDate],GETDATE()) else 0 end OverDueDays
The other IIF expression below does not seem to be correct. You should see 2 commas in there, but there is only one in what you posted.
IIf (([Organisation_ID]=266) Or
([Organisation_ID]=438) Or
([Organisation_ID]=436) Or
([Organisation_ID]=435) Or
([Organisation_ID]=437) Or
([Organisation_ID])=421,90) AS NoDaysBeforeDue

That would probaply be the same as a case without else (not sure).
My suggestion for the last part:
case when organisation_id in (266,421,435,436,437,438) then 90 end as nodaysbeforedue

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 17:28:23

If I am not mistaken, the ELSE part is not an optional parameter in MS Access. I think it is the same in SQL 2012 as well, although, that I am not sure of.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-19 : 17:44:03

quote:
Originally posted by James K

If I am not mistaken, the ELSE part is not an optional parameter in MS Access. I think it is the same in SQL 2012 as well, although, that I am not sure of.

I think you're right. Searching the web, I couldn't find iif syntax without the "else" option. I assumed op had some working code - my mistake.
Anyway, it's a golden opportunity for op to consider that to do in case of none of the listed organisation id's :-)

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 18:03:23

SELECT c.Incident_ID,
c.Incident_Date,
c.Incident_Desc,
case when ([Organisation_ID]=266) Or
([Organisation_ID]=438) Or
([Organisation_ID]=436) Or
([Organisation_ID]=435) Or
([Organisation_ID]=437) Or
([Organisation_ID])=421 then 90 else null end AS NoDaysBeforeDue,
--DMax ?!
/*
DMax("New_Due_Date","tbl_Incident_Final_Extension","[Incident_ID]=" & CatAIncidentByDateandOrgID.Incident_ID) AS ExtDate,
*/
c.Incident_ID, DateAdd(day,90,[Incident_date]) AS DueDate,
case when [DueDate]< getdate() then DateDiff(day,[DueDate],getdate()) else 0 end AS OverDueDays,
c.Organisation_ID, t.Final_Report_Date
FROM CatAIncidentbyDateAndORgID c
LEFT JOIN tbl_Incident_Final_Report t
ON c.Incident_ID = t.Incident_ID
WHERE c.Incident_Date Between '1/1/2009' And '7/31/2013'
AND t.Final_Report_Date Is Null;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources