IS NOT NULL with CASE in WHERE

  • texpic

    SSCertifiable

    Points: 5882

    If the record's StartDate is not today need the WHERE clause "EndDate IS NOT NULL" (all of today's records are included, even ifthey do not have an end stamp).

    The test query should return C, D, E and F.

    CREATE TABLE #Test (MyId char(1), StartDate datetime, EndDate datetime)

    --no end date

    INSERT INTO #Test (MyId, StartDate)

    SELECT 'A', '2013-10-30 10:00' UNION ALL

    SELECT 'B', '2013-11-30 10:00' UNION ALL

    SELECT 'C', '2013-12-30 10:00'

    --end date

    INSERT INTO #Test (MyId, StartDate, EndDate)

    SELECT 'D', '2013-10-30 10:00', '2013-10-30 11:00' UNION ALL

    SELECT 'E', '2013-11-30 10:00', '2013-11-30 11:00' UNION ALL

    SELECT 'F', '2013-12-30 10:00', '2013-12-30 11:00'

    SELECT * FROM #Test

    CASE WHEN StartDate < CAST(GETDATE() as Date) THEN WHERE EndDate IS NOT NULL END

  • texpic

    SSCertifiable

    Points: 5882

    Guess I'm not thinking:

    SELECT * FROM #Test

    WHERE (StartDate < CAST(GETDATE() as Date) AND EndDate IS NOT NULL)

    OR StartDate >= CAST(GETDATE() as Date)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply