CODEWITHSUNDEEP
sqlsql-server
rigamonk
rigamonk

Reputation: 1181

Case in Where Clause with between

I'm trying to change the criteria for a where clause based on a case statement. The problem is it doesn't like by 'between' statement. Can i change "Where" criteria using a case in there? here's whats causing the problem (Incorrect syntax near the keyword 'between'.) I'm trying to get the fiscal month from the PMF_FISCALCALENDAR table within a date range. The problem is that the one date range could be an invalid number, in which case i need to use the second date.

Incorrect syntax near the keyword 'between'.

,(select PERIOD =
    CASE 
        WHEN PERIOD = '1' THEN
            'Jan'           
        WHEN Period = '2' THEN
            'Feb'
        WHEN PERIOD = '3' THEN
            'Mar'
        WHEN Period = '4' THEN
            'Apr'
        WHEN PERIOD = '5' THEN
            'May'
        WHEN Period = '6' THEN
            'Jun'
        WHEN PERIOD = '7' THEN
            'Jul'
        WHEN Period = '8' THEN
            'Aug'
        WHEN PERIOD = '9' THEN
            'Sep'
        WHEN Period = '10' THEN
            'Oct'
        WHEN PERIOD = '11' THEN
            'Nov'
        WHEN Period = '12' THEN
            'Dec'

    END

FROM PMF_FISCALCALENDAR 
WHERE 
(CASE
    WHEN YEAR(SALESQUOTATIONLINE.KTI_SENTDATE) = 1900  Then
            SALESQUOTATIONLINE.KTI_SENTDATE between PMF_FISCALCALENDAR.STARTDATE and PMF_FISCALCALENDAR.ENDDATE
        ELSE 
            SALESQUOTATIONTABLE.PMF_QUOTESENTDATE between PMF_FISCALCALENDAR.STARTDATE and PMF_FISCALCALENDAR.ENDDATE
END)
    ) AS 'Fiscal Month Sent'

I want to use one or the other case results as the where clauses criteria. I'm using SQL SERVER 2005

Upvotes: 0

Views: 135

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Try this for better syntax and performance:

SELECT PERIOD = convert(char(3), dateadd(m, PERIOD, -1), 0)
FROM PMF_FISCALCALENDAR 
WHERE 
    SALESQUOTATIONLINE.KTI_SENTDATE < '1901-01-01' AND
    SALESQUOTATIONLINE.KTI_SENTDATE >= '1900-01-01' AND
    SALESQUOTATIONLINE.KTI_SENTDATE between PMF_FISCALCALENDAR.STARTDATE and PMF_FISCALCALENDAR.ENDDATE
or
    (SALESQUOTATIONLINE.KTI_SENTDATE <  '1900-01-01' or
     SALESQUOTATIONLINE.KTI_SENTDATE >= '1901-01-01' or 
     SALESQUOTATIONLINE.KTI_SENTDATE is null) AND
    SALESQUOTATIONTABLE.PMF_QUOTESENTDATE between PMF_FISCALCALENDAR.STARTDATE and PMF_FISCALCALENDAR.ENDDATE

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166416

Change the statement to something like

WHERE 
    CASE
        WHEN YEAR(SALESQUOTATIONLINE.KTI_SENTDATE) = 1900  Then
                SALESQUOTATIONLINE.KTI_SENTDATE 
            ELSE 
                SALESQUOTATIONTABLE.PMF_QUOTESENTDATE 
    END between PMF_FISCALCALENDAR.STARTDATE and PMF_FISCALCALENDAR.ENDDATE

Upvotes: 3

Related Questions