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
LVL 1
zoidiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
sorry:
where ( @date start <= DateEnd OR  DateEnd IS NULL )
  and @date_end >= DateStart

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this logic is what you are looking for:
where ( @date start <= DateEnd  DateEnd IS NULL )
  and @date_end >= DateStart
  

Open in new window

0
 
zoidiAuthor Commented:
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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Kevin CrossChief Technology OfficerCommented:
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

0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
type-o's
where @date_start <= coalesce(DateEnd, @date_start)
  and @date_end >= DateStart

Open in new window

0
 
zoidiAuthor Commented:
Thank you guys for your help. It worked just fine :)
0
 
zoidiAuthor Commented:
Oh, sorry: The accepted solution should be mwvisa1's comment and the assisted solution should be angellll's

Regards,
Zoidi
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.