Access to SQL (IIF)

Steven · March 3rd, 2004, 06:03 PM

Can somebody help me out with a bit of a problem I'm having with a query conversion.

I've got a query in Access that I want to make into a view on the server, but since SQL Server doesn't like IIF, it's making life a bit hellish.

Basically, this is the statement I need to fix.

IIf(Table1.AdmDate>Table2.AdmDate-0.25 And Table1.AdmDate<Table2.AdmDate,Table1.AdmDate,Table 2.AdmDate)

I thought it would be:

CASE Table1.AdmDate
    WHEN >Table1.AdmDate-0.25 AND
    <Table2.AdmDate Then Table1.AdmDate
    ELSE Table2.AdmDate
END

Basically what I'm wanting to happen is this:
If Table1.AdmDate is up to 6 hours before Table2.AdmDate, then return Table1.AdmDate, otherwise, return Table2.AdmDate

Any help would be appreciated

I am a loud man with a very large hat. This means I am in charge

rgerald · March 3rd, 2004, 07:32 PM

try something like this:

Code:

select a.id, CASE WHEN datediff(hh, a.date1, b.date2) < 6 THEN a.date1 ELSE b.date2 END 'a_date' from t_Date1 a inner join t_date2 b on a.id = b.id

I threw this together very quickly so it may not be exactly right, however it should point you in the right direction.

Rand

Steven · March 3rd, 2004, 10:03 PM

Thanks Rand,
I eventually figured it out:

Code:

CASE WHEN 
    Table1.AdmDate > Table2.AdmDate - 0.25 AND 
    Table1.AdmDate < Table2.AdmDate 
THEN 
    Table1.AdmDate         
ELSE                       
    Table2.AdmDate END AS AdmitDate

However, getting that to work inside a user defined function is a story for another thread :)
Steven

I am a loud man with a very large hat. This means I am in charge