Problem with simple select query to find dates in SQL Server

Question

I use simple query to get dates of contract for a person:

SELECT 
    [KlienciUmowyDataPoczatkowa],
    IsNULL([KlienciUmowyDataKoncowa], GETDATE()) AS 'KlienciUmowyDataKoncowa'
FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  [PortfelID] = 3

This is what I get:

KlienciUmowyDataPoczatkowa  KlienciUmowyDataKoncowa
2005-11-28 00:00:00.000    2008-07-22 00:00:00.000
2008-07-23 00:00:00.000    2010-03-09 15:45:42.457

Client signs a contract which started at 2005-11-28 and then appendix was signed so his currenct contract ended 2008-07-22 and new one started at 2008-07-23 and lasts till today (NULL that was converted to current time). There can be clients with many many more appendix but it all goes like that.

My question is: How do i get contract that is/was active between lets say 2008-04-01 - 2008-06-30? It's possible that the client between that period will have 2 or even 5 appendix so it should return all of them.

Also I am not sure if it's needed to use IsNull? Maybe there's a better way for this so i could skip IsNull usage and entering current date as replacement.

EDIT:

I thought that solution from marc_s solved it but it seems it didn't:

SELECT 
    [KlienciUmowyDataPoczatkowa],
    [KlienciUmowyDataKoncowa]
FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
 WHERE  [PortfelID] = 3 AND 
 [KlienciUmowyDataPoczatkowa] <= '2008-07-01' AND IsNULL([KlienciUmowyDataKoncowa], '99991231') >= '2008-09-30'

It return 0 dates for the period of 2008-07-01 to 2008-09-30 when it should return both:

2005-11-28 00:00:00.000    2008-07-22 00:00:00.000
2008-07-23 00:00:00.000    NULL

As the client had contract in both times.

EDIT2:

I've tested proposed 2 queries. First one for dates (20080401 - 20080630) as in example below returns 1 row for 1st query (expected), returns 0 rows for 2nd query (not expected).

 SELECT [KlienciUmowyDataPoczatkowa],
    IsNULL([KlienciUmowyDataKoncowa], '99991231') AS 'KlienciUmowyDataKoncowa'
  FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]     
  WHERE  [PortfelID] = 3  
  AND [KlienciUmowyDataPoczatkowa] <= '20080401' AND IsNULL([KlienciUmowyDataKoncowa], '99991231') >= '20080630'

 SELECT 
 [KlienciUmowyDataPoczatkowa],
 [KlienciUmowyDataKoncowa]
 FROM   
[BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
 WHERE  
[PortfelID] = 3 AND 
-- either: start date is sometime between the two dates
([KlienciUmowyDataPoczatkowa] BETWEEN '20080401' AND '20080630'
-- or: end date is sometime between the two dates        
  OR 
  ISNULL([KlienciUmowyDataKoncowa], GETDATE()) BETWEEN '20080401' AND '20080630')

Second test for dates '20080701' to '20080930' shows for first query 0 results (not expected), and for 2nd query 2 rows (expected).

 SELECT [KlienciUmowyDataPoczatkowa],
    IsNULL([KlienciUmowyDataKoncowa], '99991231') AS 'KlienciUmowyDataKoncowa'

 FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]

WHERE  [PortfelID] = 3  

 AND [KlienciUmowyDataPoczatkowa] <= '20080701' AND IsNULL([KlienciUmowyDataKoncowa], '99991231') >= '20080930'


SELECT 
[KlienciUmowyDataPoczatkowa],
[KlienciUmowyDataKoncowa]

FROM   
[BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  
[PortfelID] = 3 AND 
-- either: start date is sometime between the two dates
([KlienciUmowyDataPoczatkowa] BETWEEN '20080701' AND '20080930'
-- or: end date is sometime between the two dates        
  OR 
  ISNULL([KlienciUmowyDataKoncowa], GETDATE()) BETWEEN '20080701' AND '20080930')

EDIT3:

Using COMBINED solution from both examples it works for both dates. But won't it blow back at me for diffrent client dates? Any Ideas?

 SELECT [KlienciUmowyDataPoczatkowa]
     , ISNULL([KlienciUmowyDataKoncowa], GETDATE()) AS 'KlienciUmowyDataKoncowa'
 FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  [PortfelID] = 3
  AND 
  (
  ([KlienciUmowyDataPoczatkowa] BETWEEN '20080401' AND '20080630'
   OR ISNULL([KlienciUmowyDataKoncowa], GETDATE()) BETWEEN '20080401' AND '20080630')
   OR ([KlienciUmowyDataPoczatkowa] <= '20080401' AND IsNULL([KlienciUmowyDataKoncowa], '99991231') >= '20080630')

  )

SELECT [KlienciUmowyDataPoczatkowa]
 , ISNULL([KlienciUmowyDataKoncowa], GETDATE()) AS 'KlienciUmowyDataKoncowa'
 FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  [PortfelID] = 3
  AND 
  (
  ([KlienciUmowyDataPoczatkowa] BETWEEN '20080701' AND '20080930'
   OR ISNULL([KlienciUmowyDataKoncowa], GETDATE()) BETWEEN '20080701' AND '20080930')
   OR ([KlienciUmowyDataPoczatkowa] <= '20080701' AND IsNULL([KlienciUmowyDataKoncowa], '99991231') >= '20080930')

  )

Answer 1

My question is: How do i get contract that is/was active between lets say 2008-04-01 - 2008-06-30? It's possible that the client between that period will have 2 or even 5 appendix so it should return all of them.

Basically that means:

• the start date of your contract must be on or before 2008-04-01
• the end date of your contract must be on or after 2008-06-30

So you need something like:

SELECT 
    (list of fields)
FROM dbo.YourTable
WHERE
    StartDate <= '20080401' AND EndDate >= '20080630'

(or whatever that will be in Polish :-)

You won't need ISNULL, unless one of your dates could be NULL (e.g. the EndDate = NULL means: contract is valid until revoked).

In that case, do something like:

SELECT 
    (list of fields)
FROM dbo.YourTable
WHERE
    StartDate <= '20080401' AND ISNULL(EndDate, '99991231') >= '20080630'

If the EndDate is NULL, pretend it's the 31st of December 9999 - that should do for the next couple thousand years :)

UPDATE:
For your update, try this query here:

SELECT 
    [KlienciUmowyDataPoczatkowa],
    [KlienciUmowyDataKoncowa]
FROM   
    [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  
    [PortfelID] = 3 AND 
    -- either: start date is sometime between the two dates
    ([KlienciUmowyDataPoczatkowa] BETWEEN '20080701' AND '20080930'
    -- or: end date is sometime between the two dates        
      OR 
      ISNULL([KlienciUmowyDataKoncowa], GETDATE()) BETWEEN '20080701' AND '20080930')

Does that return the results you're expecting??

Answer 2

If I well understand your question, I guess you could use something looking like so:

SELECT  
    [KlienciUmowyDataPoczatkowa], 
    CASE WHEN [KlienciUmowyDataKoncowa] IS NULL THEN GETDATE() ELSE [KlienciUmowyDataKoncowa] END AS 'KlienciUmowyDataKoncowa' 
FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy] 
WHERE  [PortfelID] = 3 
    AND [KlienciUmowyDataPoczatkowa] BETWEEN @ContractDateStart AND @ContractDateEnd

Where @ContractDateStart and @ContractDateEnd are the parameterized values contract date start and date end respectively of your query.

Answer 3

This should get all of your rows that either start or end during a period (Notice the variables @StartDate and @EndDate)

SELECT [KlienciUmowyDataPoczatkowa]
     , ISNULL([KlienciUmowyDataKoncowa], GETDATE()) AS 'KlienciUmowyDataKoncowa'
 FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  [PortfelID] = 3
  AND ([KlienciUmowyDataPoczatkowa] BETWEEN @StartDate AND @EndDate
   OR ISNULL([KlienciUmowyDataKoncowa], GETDATE()) BETWEEN @StartDate AND @EndDate)

Answer 4

I think this will work. If not then can you be a little more clear and give us some examples rows for what should and shouldn't be returned.

SELECT 
    [KlienciUmowyDataPoczatkowa],
    IsNULL([KlienciUmowyDataKoncowa], GETDATE()) AS 'KlienciUmowyDataKoncowa'
FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  [PortfelID] = 3
AND KlienciUmowyDataPoczatkowa] <= '2008-04-01'
AND IsNULL([KlienciUmowyDataKoncowa], GETDATE()) >= '2008-06-30'

Or you could do

SELECT 
    [KlienciUmowyDataPoczatkowa],
    IsNULL([KlienciUmowyDataKoncowa], GETDATE()) AS 'KlienciUmowyDataKoncowa'
FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  [PortfelID] = 3
AND KlienciUmowyDataPoczatkowa] <= '2008-04-01'
AND ([KlienciUmowyDataKoncowa] >= '2008-06-30'
  OR [KlienciUmowyDataKoncowa] IS NULL)

Finally it sounds like you might want all contracts for clients that had at least 1 active Contract between those dates. If that is the case do this:

SELECT 
    [KlienciUmowyDataPoczatkowa],
    IsNULL([KlienciUmowyDataKoncowa], GETDATE()) AS 'KlienciUmowyDataKoncowa'
FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
WHERE  [PortfelID] = 3
AND <Client_ID> IN
    (SELECT 
        <Client_ID>
    FROM   [BazaZarzadzanie].[dbo].[KlienciPortfeleUmowy]
    WHERE  [PortfelID] = 3
    AND KlienciUmowyDataPoczatkowa] <= '2008-04-01'
    AND IsNULL([KlienciUmowyDataKoncowa], GETDATE()) >= '2008-06-30')