CODEWITHSUNDEEP
sql-serverms-access
jsmabbas
jsmabbas

Reputation: 35

Convert Access query to SQL Server Query

I have a MS Access query and I want to convert in SQL Server query, any help will be greatly appreciated.

SELECT
  dbo_Employees.*,
  (SELECT top 1 dbo_attendance.attend_date
    FROM dbo_Attendance
    WHERE dbo_attendance.ID_Employee=dbo_attendance.ID_Employee
      and dbo_attendance.attend_date > dbo_attendance.attend_date
    order by dbo_attendance.attend_date asc) AS NextDate,
  IIf(IsNull(NextDate),Now(),Nextdate) AS next123,
  Next123-dbo_attendance.attend_date AS difference,
  dbo_attendance.attend_date,
  IIf(dbo_attendance.attend_date+90<Next123,1,0) AS Day90Credit,
  IIf(dbo_attendance.attend_date+90<Next123,dbo_attendance.attend_date+90,dbo_attendance.attend_date+365) AS CreditDate,
  IIf((Day90Credit=0 And CreditDate<Now()) Or Day90Credit=1,1,0) AS TotalCredit
FROM dbo_attendance, dbo_Employees
WHERE (((dbo_Employees.Employee_ID)=[dbo_attendance].[ID_Employee]));

Upvotes: 0

Views: 560

Answers (2)

M. Fawad Surosh
M. Fawad Surosh

Reputation: 480

You can try CTE (Common Table Expressions) in Sql Server for complex calculations, see this link: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

I refactored part of your query as below, proceed adding your calculations under WITH block:

WITH Emp_CTE (ID_Employee, attend_date)
AS
(
  SELECT emp.*, 
    (SELECT TOP 1 att.attend_date FROM dbo_Attendance AS att 
      WHERE att.ID_Employee = emp.ID_Employee 
        AND att.attend_date > emp.attend_date 
      ORDER BY att.attend_date ASC) AS [NextDate]
  FROM dbo_Employees
)
SELECT ISNULL(NextDate, GETDATE()) AS [next123],
        ISNULL(NextDate, GETDATE()) - att.attend_date AS [difference]
FROM Emp_CTE;

Upvotes: 0

JNevill
JNevill

Reputation: 50019

In sql server (and most every other RDBMS) we use CASE statements instead of iif(). The structure is pretty simple CASE WHEN <condition> THEN <value if true> ELSE <value if false> END.

Changing your iif() over to CASE will be the bulk of the switch over. The first iif() however is better represented as a COALESCE() which allows a list of fields or values. Coalesce will grab the first Non-Null value/field from the list for that record.

The other things that have to be switched is the Date logic. In SQL Server you use DATEADD() to add days (or other date parts like year and month) to a date. you use DATEDIFF() to subtract two dates to get a date part (like Days or Months or Years). 

SELECT dbo_Employees.*,
    (
        SELECT TOP 1 dbo_attendance.attend_date
        FROM dbo_Attendance
        WHERE dbo_attendance.ID_Employee = dbo_attendance.ID_Employee
            AND dbo_attendance.attend_date > dbo_attendance.attend_date
        ORDER BY dbo_attendance.attend_date ASC
        ) AS NextDate,
    COALESCE(NextDate, GETDATE()) AS next123,
    datediff(day, dbo_attendance.attend_date, COALESCE(NextDate, GETDATE())) AS difference,
    dbo_attendance.attend_date,
    CASE 
        WHEN DATEADD(DAY, 90, dbo_attendance.attend_date) < COALESCE(NextDate, GETDATE())
            THEN 1
        ELSE 0
        END AS Day90Credit,
    CASE 
        WHEN DATEADD(DAY, 90, dbo_attendance.attend_date) < COALESCE(NextDate, GETDATE())
            THEN dateAdd(DAY, 90, dbo_attendance.attend_date)
        ELSE DATEADD(DAY, 365, dbo_attendance.attend_date)
        END AS CREDITDATE, 
    CASE 
        WHEN (
                Day90Credit = 0
                AND CreditDate < GETDATE()
                )
            OR DATEADD(DAY, 90, dbo_attendance.attend_date) < COALESCE(NextDate, GETDATE())
            THEN 1
        ELSE 0
        END AS TotalCredit
FROM dbo_attendance,
    dbo_Employees
WHERE dbo_Employees.Employee_ID = [dbo_attendance].[ID_Employee];

Lastly... I can't remember how this works in SQL server since it's been a while since I was in the environment, but you might have to switch instances of dbo_ to dbo.. Your server will cry foul and let you know anyhow.

Upvotes: 2

Related Questions