SQLTeam.com | Weblogs | Forums

How to rewrite IIF and dlookup function from access to Microsoft SQL Server

Transact-SQL
#1

Can someone please help me with the rewrite of the IIF and Dlookup I this code . I understand better with CASE ad Select statements

SELECT
ScopeHistory.ProjectReturnID,
ScopeHistory.ProjectID,

--IIf([ScopeDate] Is Null,Null,

--DLookUp("QTR_ID","Quarters","QuarterNo = " & IIf(Month([ScopeDate])<4,4,IIf(Month([ScopeDate])<7,1,
--IIf(Month([ScopeDate])<10,2,3))) & "AND CalendarYear = " & Year([ScopeDate])))*1 AS [QTR Change Occurs],
--IIf([Qtr Change Occurs]=[QtrID],1,0) AS [Change This Qtr], Month([ScopeDate]) AS test,

FROM
(((((ScopeHistory
INNER JOIN Impact ON ScopeHistory.ImpactEndDate = Impact.ImpactID) INNER JOIN Impact AS Impact_1 ON ScopeHistory.ImpactCosts = Impact_1.ImpactID)
LEFT JOIN Impact AS Impact_2 ON ScopeHistory.ImpactStratOut = Impact_2.ImpactID)
LEFT JOIN Impact AS Impact_3 ON ScopeHistory.ImpactStratOutNM = Impact_3.ImpactID) LEFT JOIN Impact AS Impact_4 ON ScopeHistory.ImpactStratOutM = Impact_4.ImpactID)
LEFT JOIN Impact AS Impact_5 ON ScopeHistory.ImpactPeople = Impact_5.ImpactID
ORDER BY ScopeHistory.ProjectReturnID;

#2

What is the DLOOKUP doing? It seems to be looking up the QTR_ID value from the Quarters table - based on the Quarter of the ScopeDate.

If you can share the structure of your Quarters table - we should be able to figure out how to perform that lookup...but it may not even be necessary as we can probably calculate the values needed instead of looking them up.

#3

Thank you so much .

CREATE TABLE [dbo].[Quarters](
[QTR_ID] [int] IDENTITY(1,1) NOT NULL,
[QtrName] nvarchar NULL,
[SnapshotDate] datetime2 NULL,
[QuarterNo] [smallint] NULL,
[QuarterYear] nvarchar NULL,
[YearID] [smallint] NULL,
[QuarterPeriod] nvarchar NULL,
[CalendarYear] [smallint] NULL,
[CommisionDate] datetime2 NULL,
[ReturnDate] datetime2 NULL,
[CurrentQtr] [bit] NULL,
[ReportYear] nvarchar NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL

thanks and any help much appreciated

#4

Hi Jeffw8713
just to give you a idea of the code I have tried putting together using CASE and SELECT but its to working yet as am a DBA and not too much of an expert at programming .

CASE

WHEN [ScopeDate] Is Null THEN NULL

ELSE

(SELECT qtr. QTR_ID, QuarterNo =

CASE

WHEN  Month([ScopeDate])<4 THEN 4 
WHEN  Month([ScopeDate])<7 THEN 1

WHEN  Month([ScopeDate])<10 THEN 2  ELSE 3

END
AS [QTR Change Occurs]
FROM Quarters as qtr INNER JOIN [ScopeHistory] sc
ON qtr.QTR_ID= sc.QTRID
AND qtr.[CalendarYear] = Year([ScopeDate])*1

CASE
WHEN [QTR Change Occurs] =QTR_ID THEN 1 ELSE 0

END
AS [Change This Qtr],

#5

After reading your initial post and the follow ups, it is not yet clear to me what you are trying to do. But my guess is that you are trying to calculate the quarter based on a date column in your table, and that your first quarter begins on April 1. If that is the case, what you can do is something like in the examples below.

In the example, I am using a variable @Date. You can replace that with your column name - ScopeDate - in your query against the table.

DECLARE @Date DATE = '20200903';

-- Calculate quarter for @Date. By default quarter is calendar quarter
SELECT DATEPART(QUARTER,@Date);

-- If your fiscal year starts on April 1, you can calculate the quarter
-- using a case expression.
SELECT
    CASE DATEPART(QUARTER,@Date)
        WHEN 1 THEN 4
        WHEN 2 THEN 1
        WHEN 3 THEN 2
        WHEN 4 THEN 3
    END;

-- There are clever ways to make the code compact, but I would stay away
-- from this type of clever tricks. They are not any more efficient, and
-- are much less readable.
SELECT (DATEPART(QUARTER,@Date)+2)%4+1
#6

Hi JamesK
Thank you for your help
what I am trying to achieve with the original code is to rewrite the IIF statements and dlookup in that code -----I have commented out the part of the Code that I need help with .

replace the IIFs statement with CASE WHEN

replace the Dlookups with SELECT Statements

we have recently migrated from Microsoft Access to SQL Server and the old code does not work with Dlookups in SQL Server

so I need to use CASE and SELECT (which needs to be a subquery) .

#7

What @JamesK and I are trying to point out is that you don't need recreate the IIF and DLOOKUP in SQL Server...

To do that - we need to understand what results you are looking to get from that code. If you are just trying to calculate the quarter it can be done using DATEPART and a simple case expression. If it is something else - we cannot figure that out from the code.

It seems you are looking up the QTR_ID from your table based on the QuarterNo - but then you have CalendarYear...when it probably should be a FiscalYear (but you don't have a FiscalYear column).

With that said - maybe you need a CROSS APPLY:

SELECT ...
FROM ScopeHistory sc
CROSS APPLY (SELECT ...
               FROM Quarters q
              WHERE q.QTR_ID = DATEPART(QUARTER, sc.ScopeDate) + 2) % 4 + 1
                AND q.CalendarYear = YEAR(ScopeDate)
            ) As qtr

Not sure that works though...because Q1/Q2 should be next fiscal year and Q3/Q4 would be current year.

#8

thank you both . I will try this code and let you know .

yes u are correct I saying I dont need to recreate the IIF and Dlookup . there are lots of other ways .

I suppose its because am not used to complex coding .