Solved

Convert Access Query into SQL Query

Posted on 2012-04-10
2
340 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 300 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 200 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

777 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