dates sql query

Question

dates sql query

forum member

Ten Centuries

Points: 1290
More actions
September 6, 2009 at 5:01 am

#156181

Hi,
I have inserted a record whose start time and end time 11/08/2009 9:00:00 AM and 11/08/2009 10:00:00 PM
and If users does another booking from 7:00:00 AM to 11:00:00 PM then it shouldn't book because there exists a booking in the below range from 9:00 am to 10:00pm
so ideally it should allow booking from before till 9:00am or after 10:00pm
and also another issue i am facing when my start and end time is 8:00 am to 9:00am it should allow booking because from 9:00 am another booking starts
I have trouble writing query that cover the multiple scenarios please help with this
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

This is an interesting challenge, but you didn't provide enough information for anyone to really help you out. For instance, can you have a booking from 9:15am - 9:45am, or are they always from hour to hour, lasting an hour?

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

forum member Ten Centuries Points: 1290 More actions · Answer 2

Hi,

Thanks for the reply and i appreciate help on this . Its a hourly base booking.

The following is the data table structure without any index on it

bookingid,username,startdate,enddate

startdate and enddate are datetime

Thanks for the help

LutzM SSC Guru Points: 107049 More actions · Answer 3

How about the following solution:

Note: I took the time to write some basic DDL, because it's Sunday... 🙂 ). I'm not sure whether I covered all possible scenarios when I tested it but it looks like I did...

create TABLE #t (start datetime, finish DATETIME,

CONSTRAINT [PK_t_start_finish]

PRIMARY KEY CLUSTERED (start, finish)

)

INSERT INTO #t

SELECT '11/08/2009 9:00:00 AM', '11/08/2009 10:00:00 PM' UNION ALL

SELECT '11/09/2009 9:00:00 AM', '11/09/2009 10:00:00 PM' UNION ALL

SELECT '11/10/2009 9:00:00 AM', '11/10/2009 10:00:00 PM' UNION ALL

SELECT '11/11/2009 9:00:00 AM', '11/11/2009 10:00:00 PM'

DECLARE @begin DATETIME, -- start time for requested booking window

@end DATETIME -- end time for requested booking window

SET @begin = '11/08/2009 11:00:00 PM'

SET @end = '11/09/2009 8:00:00 AM'

SELECT

CASE WHEN EXISTS (SELECT 1

FROM #t

WHERE

(start > @begin AND start < @end) -- another session starts within the requested window

OR (start @begin) -- begin of requested session is within a window already booked

) THEN 'blocked' ELSE 'available'

END

DROP TABLE #t --cleanup

/* result: either 'blocked' or 'available' */

Edit: Typo fixed.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 4

Hey "Forum Member"

Please take time to read the article posted under Lutz' signature. There are some very good rewards for you, if you follow the procedures outlined there. Basically, if you will take the time to set up the problem correctly, more people will attempt to answer it. You will get tested code examples and you will get them more quickly. There are several reasons for this:

We are all volunteers and have our own lives and jobs outside of SSC. Our time is as important to us as yours is to you. Frankly, a lot of people will look past a complicated verbal description that isn't supported by sample scripts.

If you supply a script that creates a couple of tables and populates them with sample data, then any volunteer can go to work solving the problem immediately. Otherwise, we each individually have to waste time setting up the problem, and might make mistakes that you would then have to correct. Then we would have to rethink the problem because of data or schema changes. It is in your interest and ours that the presentation of your problem be consistent for everyone, so that you get a correct answer the first time around. If you've already tried to code a solution, post up your efforts to date. It let's us see what your thinking is and lets us know that you are putting something of yourself into it, not just trying to farm out your work to others.

Finally, doing your part on the front end is also a gesture of respect and consideration. It is the best way of saying "thank you" to the people who are helping you. It will win you friends among the regular volunteers and make them happy to help you again in the future.

Thanks 🙂

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

forum member Ten Centuries Points: 1290 More actions · Answer 5

forum member

Ten Centuries

Points: 1290

September 6, 2009 at 12:04 pm

#1049883

Hi Lutz

My sincere thank you for your help.

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 6

Glad I could help 🙂

But, please think about what Bob told you in his post.

It will really help you in the future (maybe your next post isn't on a Sunday) 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

forum member Ten Centuries Points: 1290 More actions · Answer 7

Thanks Lutz,i apologise i had to do this on sunday.I was so desperate about resolving query i have tried for few days but couldn't combine two queries ,i tried "between" construct but didnt work so had to do approach sqlserver central.

Thanks once again for the help

forum member Ten Centuries Points: 1290 More actions · Answer 8

Hi Bob and Wayne

Thanks for the suggestions.I will definitely keep in mind on how to post the questions with proper data and code and will use this approach in future.I realize it will make things easier for members for reply and on concentrating on the T-SQL rather then building over all structure from the scratch and T-SQL

Thanks Guys for your reply on Sunday and I apologize i have killed some of your time in this process

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 9

I don't think Lutz meant it was wrong for you to ask a question on Sunday. I'm sure he was happy to help. After all, we are both here looking for problems to solve.

But if you asked it on a business day, he might have been to busy with his own job to take the time to set up the sample data for you. 😉

Best of luck.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

LutzM SSC Guru Points: 107049 More actions · Answer 10

It sounds like my "hint" was misleading... :ermm:

It doesn't really matter what day or time you post a question - there are people around here to help almost anytime!

It's a matter of how you'll post your question, not what time.

So, please take the time to read the link in my signature as Bob already recommended.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

drew.allen SSC Guru Points: 76776 More actions · Answer 11

lmu92 (9/6/2009)
create TABLE #t (start datetime, finish DATETIME, CONSTRAINT [PK_t_start_finish] PRIMARY KEY CLUSTERED (start, finish) ) INSERT INTO #t SELECT '11/08/2009 9:00:00 AM', '11/08/2009 10:00:00 PM' UNION ALL SELECT '11/09/2009 9:00:00 AM', '11/09/2009 10:00:00 PM' UNION ALL SELECT '11/10/2009 9:00:00 AM', '11/10/2009 10:00:00 PM' UNION ALL SELECT '11/11/2009 9:00:00 AM', '11/11/2009 10:00:00 PM' DECLARE @begin DATETIME, -- start time for requested booking window @end DATETIME -- end time for requested booking window SET @begin = '11/08/2009 11:00:00 PM' SET @end = '11/09/2009 8:00:00 AM' SELECT CASE WHEN EXISTS (SELECT 1 FROM #t WHERE (start > @begin AND start < @end) -- another session starts within the requested window OR (start @begin) -- begin of requested session is within a window already booked ) THEN 'blocked' ELSE 'available' END DROP TABLE #t --cleanup /* result: either 'blocked' or 'available' */

I think that you did cover all of the possibilities, but your code can be simplified. It's hard to see, because it's counterintuitive, but the following code will work.

DECLARE @begin DATETIME, -- start time for requested booking window

@end DATETIME -- end time for requested booking window

SET @begin = '11/09/2009 09:10:00 AM'

SET @end = '11/09/2009 10:00:00 AM'

SELECT

CASE WHEN EXISTS (SELECT 1

FROM #t

WHERE start @begin

) THEN 'blocked' ELSE 'available'

END

This, of course, assumes that there is error checking to ensure that start < finish when inserting records and that @begin < @end.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

LutzM SSC Guru Points: 107049 More actions · Answer 12

Nice job, Drew!

I've been trying to simplify it to get rid of the OR condition but couldn't figure it out...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 13

Drew: Sweet 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

forum member Ten Centuries Points: 1290 More actions · Answer 14

Hi Lutz and Bob,

I understand Lutz didnt mentioned not to post on sundays since intial post.I was only apologising that i have taken some of your time especially on weekend(of course everybody has only time to relax on weekends).So felt bad on this and thought to apologise

Will definitely take care in future how to ask question with proper script posted any and have gone through the link mentioned

Thanks all for the help