asked on

SQL Date Period

Hi Experts,

I've a problem getting some data from a table. The user enters a period of time. Then the query should look up in a table if there are records which:
- began before the entered period and end after the entered period
- began before the entered period and end in the entered period
- began before the entered period and have no end date (NULL)
- began in the entered period and end after the entered period
- began in the entered period and end in the entered period
- began in the entered period and have no end date (NULL)

Table structure:

ID | Desc | DateStart | DateEnd
--------------------------------------------------------------------
1 | Inv1 | 01/02/08 | 30/04/09
2 | Inv2 | 01/01/09 | 31/12/10
3 | Inv3 | 01/02/08 | NULL
4 | Inv4 | 01/02/08 | 30/06/08

Now, with an entered period of: 01/01/09 - 31/07/09, the query should return:

ID | Desc | DateStart | DateEnd
--------------------------------------------------------------------
1 | Inv1 | 01/02/08 | 30/04/09
2 | Inv2 | 01/01/09 | 31/12/10
3 | Inv3 | 01/02/08 | NULL

Can anybody help me with this?

Regards,
Zoidi

Guy Hengel [angelIII / a3]

this logic is what you are looking for:

where ( @date start <= DateEnd  DateEnd IS NULL )
  and @date_end >= DateStart

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zoidi

ASKER

Hi,

Thank you for your fast answer. It is almost correct except that it does not return records where DateEnd is NULL. Could you figure this out?

Thank you,
Zoidi

Kevin Cross

I think you just need to flip the DateEnd IS NULL in front.

where ( DateEnd IS NULL OR @date_start <= DateEnd )
  and @date_end >= DateStart
 
-- or alternatively
where @date_start <= coalesce(DaateEnd, @dae_start)
  and @date_end >= DateStart

Open in new window

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zoidi

ASKER

Thank you guys for your help. It worked just fine :)

zoidi

ASKER

Oh, sorry: The accepted solution should be mwvisa1's comment and the assisted solution should be angellll's

Regards,
Zoidi

Kevin Cross

Glad it helped.

I was merely trying to assist Angel Eyes anyway, so no worries on my end. However, good note/clarification for future readers, though, to indicate which code you used as your actual solution.

Happy coding!

Regards,
K