Convert Access Query into SQL Query

Hi,
I have an access query. How do I convert it in SQl Server query
Access version:

SELECT tblProjectStage.ProjectStageID, tblProjectStage.StageTypeID, tblProjectStage.RevID, tblProjectStage.CEQRNUmber, tblProjectStage.StageDate, tblProjectStage.StageAge, tblProjectStage.StageEndDate, IIf([tblProjectStage]![StageEndDate] Is Null,DateDiff("d",[tblProjectStage]![StageDate],Date()),DateDiff("d",[tblProjectStage]![StageDate],[tblProjectStage]![StageEndDate])) AS ElapsedDay, tblTypeOfStages.StageTypeName
FROM (last_stage_qry INNER JOIN tblProjectStage ON last_stage_qry.MaxOfProjectStageID = tblProjectStage.ProjectStageID) INNER JOIN tblTypeOfStages ON tblProjectStage.StageTypeID = tblTypeOfStages.StageTypeID;

I have a problem converting the part below

IIf([tblProjectStage]![StageEndDate] Is Null,DateDiff("d",[tblProjectStage]![StageDate],Date()),DateDiff("d",[tblProjectStage]![StageDate],[tblProjectStage]![StageEndDate])) AS ElapsedDay,
maximyshkaAsked:
Who is Participating?
 
Chris__WConnect With a Mentor Commented:
Hello,

Use a CASE function for the IIf, and GetDate() instead of Date().

CASE ([tblProjectStage].[StageEndDate] Is Null THEN DateDiff(day,[tblProjectStage].[StageDate],GetDate()) ELSE DateDiff(day,[tblProjectStage].[StageDate],[tblProjectStage].[StageEndDate])) AS ElapsedDay,

If any of those fields are not actually stored as the format datetime, replace table.field with CAST(table.field AS datetime).

You can also CAST the CASE above as a different data type:
CAST(CASE(...)) AS ElapsedDay

Does that cover what you are looking for?

Thanks,
Chris
0
 
Anthony PerkinsConnect With a Mentor Commented:
This is how you do it:
SELECT  p.ProjectStageID,
        p.StageTypeID,
        p.RevID,
        p.CEQRNUmber,
        p.StageDate,
        p.StageAge,
        p.StageEndDate, 
--	IIf([tblProjectStage]![StageEndDate] Is Null,
--			DateDiff("d",[tblProjectStage]![StageDate],Date()),
--			DateDiff("d",[tblProjectStage]![StageDate],[tblProjectStage]![StageEndDate])) AS ElapsedDay, 
        CASE WHEN p.StageEndDate IS NULL
             THEN DATEDIFF(DAY, p.StageDate, GETDATE())
             ELSE DATEDIFF(DAY, p.StageDate, p.p.StageEndDate)
        END ElapsedDay,
        t.StageTypeName
FROM    last_stage_qry s
        INNER JOIN tblProjectStage p ON s.MaxOfProjectStageID = p.ProjectStageID
        INNER JOIN tblTypeOfStages t ON p.StageTypeID = t.StageTypeID ;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.