CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Exclude in between dates

I have 2 dates @StartDate and @EndDate, I have a table in which I have stored shift dates of specific employees structure is defined below like this.

EmpCode   ShiftCode  StartDate    EndDate
1         24         2019-10-01   2019-10-12
1         26         2019-10-13   2019-10-20

If I provide @startDate = '2019-10-01' and @endDate = '2019-10-15' It should not get the following data, I want to exclude the rows where dates are in between the provided dates.

If the parameters provided are lets say are '2019-10-21' and '2019-10-31' respectively then it's should give me the employee code as its shift is not defined in these dates and I can add it.

This is what I have tried so far but it's not returning the desired result.

SELECT 
    EmpCode 
FROM 
    TABLE
WHERE 
    [Roster].[EndDate] NOT BETWEEN @startDate AND @endDate OR 
    [Roster].[EndDate] NOT BETWEEN @startDate AND @endDate

Upvotes: 1

Views: 174

Answers (2)

boyukbas
boyukbas

Reputation: 1235

You can achieve this by changing the 'NOT's position. Also you did have a copy paste error on your where clause.

SELECT 
    EmpCode 
FROM 
    TABLE
WHERE 
   NOT (
       [Roster].[StartDate] BETWEEN @startDate AND @endDate AND 
       [Roster].[EndDate] BETWEEN @startDate AND @endDate
   )

Upvotes: 1

user1945782
user1945782

Reputation:

As Shikar has deleted his post, the simple solution would be...

...
WHERE
    @startDate NOT BETWEEN [Roster].[StartDate] AND [Roster].[EndDate] AND 
    @endDate NOT BETWEEN [Roster].[StartDate] AND [Roster].[EndDate]

Upvotes: 2

Related Questions