Use CASE in WHERE clause - Data Parameters Sql Server

Question

I'm trying to verify the example, if the date is null pick a date from another field. Below the query:

SELECT
    *
FROM
    OrderPublish OP
    INNER JOIN Advertising AD ON
        AD.IdOrderPublish = OP.IdOrderPublish
    INNER JOIN Client Cli ON
        Cli.IdClient = OP.IdClient
    LEFT JOIN AdvertisingInserted AII ON
        AD.IdAdvertisingInserted = AII.IdAdvertisingInserted
    LEFT JOIN TypeProduct TP ON
        AII.IdTypeProduct = TP.IdTypeProduct
    INNER JOIN Publication PUB ON
        PUB.IdAdvertisingInserted = AII.IdAdvertisingInserted
WHERE
    TP.IdTypeProduct in (5,7)
    AND CASE WHEN PUB.DtIni IS NULL
        THEN
            OP.DtInc >= Isnull('08/18/2013', OP.DtInc)
        ELSE
            PUB.DtIni >= Isnull('08/18/2013', PUB.DtIni)
        END
    AND CASE PUB.DtFinal WHEN NULL
        THEN
            OP.DtInc <= Isnull('08/23/2013', OP.DtInc)
        ELSE
            PUB.DtFinal <= Isnull('08/23/2013', PUB.DtFinal)
        END

Answer 1

You can do this with COALESCE() in your case, but if you want to use CASE in WHERE criteria you can do so. Move the matching criteria outside the CASE statement, you need the CASE statement to return the values to be compared, not the comparison itself. This requires doubling up each CASE statement:

SELECT
    *
FROM
    OrderPublish OP
    INNER JOIN Advertising AD ON
        AD.IdOrderPublish = OP.IdOrderPublish
    INNER JOIN Client Cli ON
        Cli.IdClient = OP.IdClient
    LEFT JOIN AdvertisingInserted AII ON
        AD.IdAdvertisingInserted = AII.IdAdvertisingInserted
    LEFT JOIN TypeProduct TP ON
        AII.IdTypeProduct = TP.IdTypeProduct
    INNER JOIN Publication PUB ON
        PUB.IdAdvertisingInserted = AII.IdAdvertisingInserted
WHERE
    TP.IdTypeProduct in (5,7)
    AND CASE WHEN PUB.DtIni IS NULL
        THEN
            OP.DtInc
        ELSE
            PUB.DtIni 
        END
    >=  CASE WHEN PUB.DtIni IS NULL
        THEN
            Isnull('08/18/2013', OP.DtInc)
        ELSE
            Isnull('08/18/2013', PUB.DtIni)
        END

    AND CASE PUB.DtFinal WHEN NULL
        THEN
            OP.DtInc 
        ELSE
            PUB.DtFinal 
        END
    <= CASE PUB.DtFinal WHEN NULL
        THEN
            Isnull('08/23/2013', OP.DtInc)
        ELSE
            Isnull('08/23/2013', PUB.DtFinal)
        END    

That's how you can use a CASE statement in your WHERE criteria, but you could use AND () and OR () criteria along with COALESCE() to do this without the CASE

Also, Your ISNULL() statements are backwards, '08/18/2013' will never be NULL, so the field won't be evaluated, you probably mean: Isnull(OP.DtInc,'08/23/2013')

Answer 2

Instead of case you can try Coalesce function :http://msdn.microsoft.com/en-us/library/ms190349.aspx

SELECT
    *
FROM
    OrderPublish OP
    INNER JOIN Advertising AD ON
        AD.IdOrderPublish = OP.IdOrderPublish
    INNER JOIN Client Cli ON
        Cli.IdClient = OP.IdClient
    LEFT JOIN AdvertisingInserted AII ON
        AD.IdAdvertisingInserted = AII.IdAdvertisingInserted
    LEFT JOIN TypeProduct TP ON
        AII.IdTypeProduct = TP.IdTypeProduct
    INNER JOIN Publication PUB ON
        PUB.IdAdvertisingInserted = AII.IdAdvertisingInserted
WHERE
    TP.IdTypeProduct in (5,7)
    AND Coalesce(PUB.DtIni,OP.DtInc)>= Isnull('08/18/2013', OP.DtInc)
    AND Coalesce(PUB.DtFinal,OP.DtInc)<= Isnull('08/23/2013', OP.DtInc)