Access IIF clause to T-SQL

[n90]

Hey all so I need to convert this daily report I run every morning to T-SQL, however I am having extreme difficulty converting the IIF statement I have in the Where clause of my access report to SQL. Here is what I currently have and works fine -

SELECT JJobs.Job, JJobs.RNum, JJobs.LogDT, Lst.LName AS JobStatus
FROM Sites INNER JOIN
JJobs ON Sites.Site = JJobs.Site INNER JOIN
City ON Sites.CityID = City.CityID INNER JOIN
Lst INNER JOIN
Lst AS Lst_1 ON LEFT(Lst.LID, 1) = Lst_1.LID ON JJobs.JS = Lst.LID
WHERE (Lst.TID = 'JS') AND (Lst_1.TID = 'JST')

What I need to add now is this IIF statement I have in the Criteria in my Access DB for the LogDT - >IIf(Weekday(Date())=2,Date()-3,Date()-1).

The report Basically looks at the current day and pulls Jobs that have the previous date on them and if its a Monday it will pull jobs opened on Friday/Sat/Sunday, thats the whole point of that IIF statement. Any help would be Awesome!

 

[pbaldy]

CASE is commonly used in T-SQL in place of an IIf() in Access. Syntax examples in BOL.

 

[n90]

CASE is commonly used in T-SQL in place of an IIf() in Access. Syntax examples in BOL.

Can you elaborate a little more on this, I'm still having alot of difficulty with migrating it over.

 

[pbaldy]

What have you got so far?

 

[n90]

What have you got so far?

Just what I posted, still. Every CASE statement I tried adding to the WHERE clause does not seem to work.

 

[pbaldy]

What CASE statement have you tried, and what didn't work? You'll also need GETDATE() and DatePart() to replace Weekday() and Date().

 

[n90]

What I've been trying is this statement in the WHERE Clause - (JJobs.LogDT > (CASE WHEN GetPart(day,GetDate()) = 2 GetDate() THEN GetDate()-3 ELSE GetDate()-1))

 

[pbaldy]

I'm not familiar with GetPart, plus you've got a stray GetDate() in there. Try DatePart:

WHERE JJobs.LogDT > CASE WHEN DatePart(dw,GetDate()) = 2 THEN GetDate()-3 ELSE GetDate()-1 END

 

[n90]

I'm not familiar with GetPart, plus you've got a stray GetDate() in there. Try DatePart:

WHERE JJobs.LogDT > CASE WHEN DatePart(dw,GetDate()) = 2 THEN GetDate()-3 ELSE GetDate()-1 END

Thanks that worked great however now I have an issue with the amount of jobs its pulling vs my Access DB. It seems that my Access DB when ran at lets say 8am today will pull all the Jobs from yesterday 12am till 8am today vs when I run it in SQL it will only pull Jobs from 8am yesterday to when the report is run 8am today. So there is a gap of jobs between the 2 Queries. Any Idea how to fix this?

 

[pbaldy]

Does your field contain a time portion? GetDate() is the equivalent of Now(). To get just the date portion (the equivalent of Date()):

Convert(varchar,GetDate(), 101)

 

[n90]

Does your field contain a time portion? GetDate() is the equivalent of Now(). To get just the date portion (the equivalent of Date()):

Convert(varchar,GetDate(), 101)

Your a god send! Now I got another question , I also have a column in my query in Access that has an IIF statement now would I go about bringing that into t-sql?

Finished: IIf([JobState]="Tech" And ([JobStatus]="Finished" Or [JobStatus]="Checked-in"),[jjobs].[Respond],"")

Basically if the state or status of the Job is one of those it will fill in the spot with respond time if not than it will just leave it blank.

 

[pbaldy]

Well, in Access SQL view it would look like:

IIf(...) As Finished

so again you can use CASE in T-SQL and it would look like:

CASE... As Finished

 

[n90]

Well, in Access SQL view it would look like:

IIf(...) As Finished

so again you can use CASE in T-SQL and it would look like:

CASE... As Finished

I'm Just trying this out -

[Circle K Jobs].Job, [Circle K Jobs].RNum, [Circle K Jobs].LogDT, CASE WHEN Lst.LName = 'Tech' THEN Jjobs.Respond Else "." AS Finished, Lst.LName AS JobState, Lst_1.LName AS JobStatus

It saying incorrect syntax near the keyword 'AS'

 

[pbaldy]

In SQL Server you want single quotes, not double, like you have in the first post.

 

[n90]

In SQL Server you want single quotes, not double, like you have in the first post.

So I fixed the statement -

CASE WHEN Lst.LName = 'Tech' THEN [Circle K Jobs].Respond Else '.' AS Finished

however it still will not run and gives me the same error.

 

[pbaldy]

You forgot the "End":

CASE WHEN Lst.LName = 'Tech' THEN [Circle K Jobs].Respond Else '.' End AS Finished