Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert Access Query into SQL Query

Posted on 2012-04-10
2
Medium Priority
?
351 Views
Last Modified: 2012-05-22
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,
0
Comment
Question by:maximyshka
2 Comments
 
LVL 3

Accepted Solution

by:
Chris__W earned 1200 total points
ID: 37829560
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 37830613
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question