--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;
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.
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
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
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) .
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.