CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Dealing with NULL Dates within a Date Range

I have the following query I'm putting together to try and show all the values between 28 and 29 Nov and also those that have a TimeIn value between those dates but a NULL value for TimeOut.

    SELECT  
        TimeIn,
        TimeOut
    FROM
        Movement VM
    WHERE 
        Area  = 1 AND
        ((
        VM.TimeIn >= '28 Nov 2016' AND 
        VM.TimeOut < DATEADD (d,1,'29 Nov 2016' ) OR VM.TimeOut IS NULL) )
        order by timein

These are the results I get:

**TimeIn                  TimeOut**

2016-11-10 01:16:04.807 NULL

2016-11-10 01:18:48.900 NULL

2016-11-10 01:28:10.350 NULL

2016-11-10 01:31:28.693 NULL

2016-11-13 16:22:45.273 NULL

2016-11-14 22:18:06.210 NULL

2016-11-18 23:05:15.753 NULL

2016-11-19 00:22:39.810 NULL

2016-11-19 00:36:44.570 NULL

2016-11-19 00:40:42.507 NULL

2016-11-19 00:42:14.463 NULL

2016-11-19 00:45:05.860 NULL

2016-11-20 22:35:10.830 NULL

2016-11-20 22:41:27.383 NULL

2016-11-20 23:22:57.177 NULL

2016-11-21 02:17:36.053 NULL

2016-11-28 02:13:05.060 2016-11-28 02:13:05.060

2016-11-29 00:01:14.910 2016-11-29 00:01:14.910

2016-11-29 00:04:29.827 2016-11-29 00:04:29.827

2016-11-29 00:07:14.840 2016-11-29 00:07:14.840

2016-11-29 00:09:52.807 2016-11-29 00:09:52.807

2016-11-29 00:13:10.217 2016-11-29 00:13:10.217

2016-11-29 00:15:33.587 2016-11-29 00:15:33.587

2016-11-29 00:25:12.573 2016-11-29 00:25:12.573

2016-11-29 02:47:41.570 NULL

2016-11-29 12:31:50.503 2016-11-29 12:31:50.503

2016-11-29 12:39:01.483 2016-11-29 12:39:01.483

2016-11-29 13:03:43.477 2016-11-29 13:04:43.663

2016-11-30 01:43:05.487 NULL

2016-11-30 01:44:05.480 NULL

2016-11-30 01:51:22.277 NULL

2016-11-30 01:55:24.000 NULL

2016-11-30 01:57:44.620 NULL

2016-11-30 02:00:04.150 NULL

2016-11-30 02:16:05.823 NULL

2016-11-30 15:57:35.517 NULL

2016-12-02 00:28:22.487 NULL

2016-12-02 01:07:07.303 NULL

2016-12-09 02:18:40.897 NULL

but I only want to return the below. How can I get my syntax in the correct method?



 **TimeIn                  TimeOut**

    2016-11-28 02:13:05.060 2016-11-28 02:13:05.060

    2016-11-29 00:01:14.910 2016-11-29 00:01:14.910

    2016-11-29 00:04:29.827 2016-11-29 00:04:29.827

    2016-11-29 00:07:14.840 2016-11-29 00:07:14.840

    2016-11-29 00:09:52.807 2016-11-29 00:09:52.807

    2016-11-29 00:13:10.217 2016-11-29 00:13:10.217

    2016-11-29 00:15:33.587 2016-11-29 00:15:33.587

    2016-11-29 00:25:12.573 2016-11-29 00:25:12.573

    2016-11-29 02:47:41.570 NULL

    2016-11-29 12:31:50.503 2016-11-29 12:31:50.503

    2016-11-29 12:39:01.483 2016-11-29 12:39:01.483

    2016-11-29 13:03:43.477 2016-11-29 13:04:43.663

Upvotes: 0

Views: 72

Answers (1)

TJB
TJB

Reputation: 877

The reason you are getting the top and not the bottom is due to your OR syntax. Your OR needs to be in the same parentheses as your ValidTo limitation

i.e. (validTo > '29 Nov 2016' OR null) and not (validTo > '29 Nov 2016') OR NULL. Having NULL outside returns every single row that has a NULL in that column.

So your syntax needs to be (edited):

    SELECT TimeIn
    ,[TimeOut]
FROM Movement VM
WHERE Area = 1
    AND CAST(VM.TimeIn AS DATE) BETWEEN '28 Nov 2016' AND '29 Nov 2016'
            AND (
                CAST(VM.TimeOut AS DATE) <= '29 Nov 2016'
                OR VM.TimeOut IS NULL
                )
ORDER BY timein

Upvotes: 1

Related Questions