Conversion of Queries Access to SQL IIF - CASE 2

[Silvertri]

We've used upsize to get our back end up in SQL including queries (adp project option) (front end going to .Net via Microtools Access.Net convertor) however many didnt come across and now working through issues via copy paste fix
Problem with this code any suggestions gives incorrect syntax near tblMain details and near keyword then

Any suggestions TIA Silvertri

SELECT tblMainDetails.IncidentName, CONVERT(char(8), GetDate(), 108) AS CurrTime, DateDiff(hour,
[tblResourceJobsCrew].[DateIn] + [tblResourceJobsCrew].[TimeIn], GetDate()) AS Hours, CASE WHEN (DateDiff(hour,
[tblResourceJobsCrew].[DateIn] + [tblResourceJobsCrew].[TimeIn], GetDate()) > 0 THEN 'Crew Relief ' + [CrewName] ELSE 'Crew Check ' + [CrewName])
AS CrewChk
FROM tblMainDetails INNER JOIN
tblResourceJobsCrew ON tblMainDetails.IncidentName = tblResourceJobsCrew.IncidentName
WHERE (((tblMainDetails.CompletedTime) IS NULL))
GROUP BY tblMainDetails.IncidentName, CONVERT(char(8), GetDate() 108), CASE WHEN (DateDiff(hour,
[tblResourceJobsCrew].[DateIn] + [tblResourceJobsCrew].[TimeIn], GetDate()) > 0 THEN 'Crew Relief-' + [CrewName] ELSE 'Crew Check-' + [CrewName]);

 

[JamesLean]

A few amendments needed. Also, you don't need a GROUP BY clause as you're not using any aggregate functions. Try this:

SELECT m.IncidentName,
  CONVERT(char(8), getdate(), 108) AS CurrTime,
  DATEDIFF(hh, r.DateIn + r.TimeIn, getdate()) AS Hours,
  CASE WHEN DATEDIFF(hh, r.DateIn + r.TimeIn, getdate()) > 0 THEN 'Crew Relief ' + CrewName
    ELSE 'Crew Check ' + CrewName
  END AS CrewChk
FROM tblMainDetails m JOIN tblResourceJobsCrew r ON m.IncidentName = r.IncidentName
WHERE m.CompletedTime IS NULL

--James

 

[swampBoogie]

The parentheses are all wrong in case when (datediff

The syntax for a case expression is

case when expression then expression
when expression then expression
...
else value end

or

case expression
when expression then expression
when expression then expression
...
else value end


You are missing the end at the end of the case expressions.

 

[Silvertri]

G'day James,

Thanks again, 110 down 300 approx to go!!!

Unfortunately you'll probably be seeing more posts from me over the next fortnight or so...Regards Ken

BTW if you want to have a look at what we have done in Access our Web Site is

http://www.traveltech.ws

we are just a small family business currently trying to gear up to take on some of the big players in OZ !!!