CODEWITHSUNDEEP
sql-server-2008
sourabhgk
sourabhgk

Reputation: 99

SQL Server - find out active customers in given date range

I have a customers table

TBLCustomer:

CustomerCode    StartDate    EndDate
-----------------------------------------
C1               2016-02-01    2016-05-01
C2               2016-05-01    2016-12-01
C3               NULL          2016-05-01
C4               2016-07-01    NULL
C5               NULL          NULL

Now I want to write a query to find out customers which are active on a current date on following conditions.

  1. If both start and end date are not blank, then customer is active between given date range.

  2. If start date is NULL and end date is there then customer is active till enddate.

  3. If start date is there and end date is NULL then customer is active from startdate.

  4. If start and end date are NULL , then customer is always active.

Sample output:

Current Date: 2016-04-07

CustomerCode    
----------------
C1               
C3               
C5

Please reply. Thanks.

Upvotes: 1

Views: 1166

Answers (2)

lysp
lysp

Reputation: 36

You could simply provide an ISNULL with a date that will be well before/after the bounds:

ie.

WHERE @ActiveDate BETWEEN ISNULL(StartDate, '1900-01-01') AND ISNULL(EndDate, '2100-01-01')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522301

One way of implementing your logic would be to use the COALESCE() function on the StartDate and EndDate. In cases where the StartDate be NULL, you can replace it with a very early date (the year 1753 in my example below). Then, if the StartDate be missing, a customer will effectively be treated as if he were always there. I apply similar logic for the EndDate.

SELECT t.*
FROM TBLCustomer t
WHERE someDate BETWEEN COALESCE(StartDate, CAST('1753-1-1' AS DATETIME)) AND
                       COALESCE(EndDate, CAST('9999-12-31' AS DATETIME))

Upvotes: 0

Related Questions