SQL Torment 1

[scottsanpedro]

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

--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

 

[scottsanpedro]

All sorted out. Got some help from elsewhere.
For your interest

AND 
				CASE WHEN @StartYear = @EndYear THEN
                CASE WHEN (DET_PERIODNUMBR >= @StartPeroid AND DET_PERIODNUMBR <= @EndPeroid AND DET_Year = @EndYear) THEN 1 
                     ELSE 0
                END
				ELSE
                CASE WHEN DET_Year = @StartYear and DET_PERIODNUMBR >= @StartPeroid THEN 1
                     WHEN DET_Year = @EndYear   and DET_PERIODNUMBR <= @EndPeroid   THEN 1
                     ELSE 0
                END
				END = 1

 

[bborissov]

AND
 CASE WHEN (@StartYear = @EndYear  AND 
           (DET_PERIODNUMBR >= @StartPeroid AND DET_PERIODNUMBR <= @EndPeroid AND DET_Year = @EndYear)) OR
           (DET_Year = @StartYear and DET_PERIODNUMBR >= @StartPeroid) OR
           (DET_Year = @EndYear   and DET_PERIODNUMBR <= @EndPeroid)
       THEN 1
 ELSE 0 END = 1

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[scottsanpedro]

cheers for the better layout
appreciated
Scott