scottsanpedro
Programmer
Dear All,
I have two queries (Very much shorten for clarity) which I would love to be able to turn to one.
Due to accounting years I have a problem where due to spanning years I cannot seem to use the same logic.
If the @StartYear and @EndYear are the same I can use the 'AND' clause. If they are different I must use the OR clause.
It would be great to maybe use a CASE statement.
CASE @StartYear WHEN = @EndYear then apply this where clause
I don't want to use dynamic SQL as these are long queries and numerous.
Thanks in advance
Scott
I have two queries (Very much shorten for clarity) which I would love to be able to turn to one.
Due to accounting years I have a problem where due to spanning years I cannot seem to use the same logic.
If the @StartYear and @EndYear are the same I can use the 'AND' clause. If they are different I must use the OR clause.
It would be great to maybe use a CASE statement.
CASE @StartYear WHEN = @EndYear then apply this where clause
I don't want to use dynamic SQL as these are long queries and numerous.
Thanks in advance
Scott
Code:
--C = Current Year
--L = Last Year
--When Years are different (L9 - C3)
SELECT SM_STOCK_CODE,
SM_Location
FROM GMCPUBS.dbo.STK_MOVEMENTS
WHERE SM_STOCK_CODE = @StockCode
AND SM_Location = @Location
AND
(
(DET_PERIODNUMBR >= @StartPeroid
AND DET_Year = @StartYear)
OR
(DET_PERIODNUMBR <= @EndPeroid
AND DET_Year = @EndYear)
)
--When Year is the same (C1 - C7)
SELECT SM_STOCK_CODE,
SM_Location
FROM GMCPUBS.dbo.STK_MOVEMENTS
WHERE SM_STOCK_CODE = @StockCode
AND SM_Location = @Location
AND DET_Year = @StartYear
AND DET_PERIODNUMBR >= @StartPeroid AND DET_PERIODNUMBR <= @EndPeroid