Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL date check issue

Posted on 2014-12-12
18
Medium Priority
?
122 Views
Last Modified: 2014-12-12
Hi

I'm having one of those days (had one yesterday too)

I have a booking system and bookings have a slotStart and slotEnd datetime.

When making a booking I need to check if it overlaps with an existing one. I have all the logic and returns values done, what I can't sort out is the sql.

I have a table with slotstart and slot end, and I pass in the same (Start/ End) I want to return 1 if the new booking overlaps an existing.

I have this but the where isn't right:
      Select  1
      From RoomBookSchedule
      Where Location_ID = @Location_ID
      And isnull(IsDeleted,0) = 0
      And
      (SlotStart < @SlotStart Or SlotStart <= @SlotEnd)

Andy
0
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40495697
What are you getting? An error?
Can you provide a sample of data so we can work it out?
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40495710
Not getting error, just getting data I don't want.

A
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40495721
The SELECT only returns '1'. How do you know if the data is what you want?
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 3

Author Comment

by:Andy Green
ID: 40495725
create table #RoomBookSchedule
(      Location_ID int
,      SlotStart smalldatetime
,      SlotEnd smalldatetime);


Insert into #RoomBookSchedule
values            (2, '2014-12-12 08:30:00', '2014-12-12 09:30:00')
            ,      (2, '2014-12-12 09:30:00', '2014-12-12 10:30:00')
            ,      (2, '2014-12-12 12:30:00', '2014-12-12 13:30:00')
            ,      (2, '2014-12-12 13:30:00', '2014-12-12 14:30:00')
            
            
            
declare @Location_ID int
declare @SlotStart smalldatetime
declare @SlotEnd smalldatetime
declare @Clinic_ID int

set @Location_ID = 2
set @SlotStart ='2014-12-12 10:30:00'
set @SlotEnd ='2014-12-12 11:30:00'
set @Clinic_ID = 0


      Select  1
      From RoomBookSchedule
      Where Location_ID = @Location_ID
      And isnull(IsDeleted,0) = 0
      And
      (SlotStart < @SlotStart Or SlotStart <= @SlotEnd)


drop table #RoomBookSchedule


If you look at the data there is a slot between 1030 & 1230, so the proc should return nothing as there isn't a slot at 1030 - 1130.

It should return 1 if the parameters overlap any existing booking.

A
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40495726
The return 1 is a flag to be used on the ASP page. If it returns 1 there is an overlap with existing booking and this is echoed on the asp.net page

A
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40495739
What results do you expect?

AND  (SlotStart < @SlotStart Or SlotStart <= @SlotEnd) means SlotStart < MAXIMUMOF(@SlotStart, @SlotEnd).
For a range check you should use AND  (SlotStart > @SlotStart Or SlotStart <= @SlotEnd). Wrong comparision operator, I guess.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40495747
Then your criteria is not correct. Should be like this:
Select  1
From RoomBookSchedule
Where Location_ID = @Location_ID 
   And isnull(IsDeleted,0) = 0 
   And (SlotStart > @SlotStart And SlotStart <= @SlotEnd)

Open in new window

0
 
LVL 3

Author Comment

by:Andy Green
ID: 40495767
Not quite right, if you change the parameter to set @SlotStart ='2014-12-12 10:29:00', this ovrlaps an existing booking by 1 min, so should return 1
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40495778
I think what you're trying to achieve cannot be done in this way. I understand your logic and I don't think the comparison operators were wrong. What you were trying to check is if there is a line which has a start time before the desired start time @slotStart and if so, make sure that it also ends before the desired start time. While this might apply to the second line of your sample data (the one before the empty slot), it does NOT apply to your first line of dummy data. As such, the query returns 1.
You're basically looking for a line in the table that does not yet exist. Therefore your query always returns 1, because it has overlap with all the records that DO exist.
I'm still trying to work out a way that does give you what you want, but it's a bit tricky. Will let you know when I crack it.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40495805
Thanks Koen. If it helps I do a search for empty slots, maybe I should be doing this the same way.

I have a function that returns a table of slots:

ALTER Function [dbo].[CreateTimeSlots](

@SlotStart smalldatetime,
@SlotEnd smalldatetime

)
Returns

@Slots TABLE
(
SlotStart smalldatetime,
SlotEnd smalldatetime
)

As
Begin

;With cteSlots (SlotStart, SlotEnd) AS (
    select @SlotStart,DATEADD(n, 30, @SlotStart)

    Union ALL

    select
      DATEADD(n, 30, SlotStart),DATEADD(n, 60, SlotStart)

    From cteSlots
    Where SlotStart <= DATEADD(n, 30, @SlotEnd)
      )

      Insert Into @Slots (SlotStart, SlotEnd)
      Select SlotStart,SlotEnd
      From cteSlots
      where SlotStart >= @SlotStart And SlotEnd <= @SlotEnd
      Option(Maxrecursion 3660);
      Return

End

I then cursor though these slots populating a temp table if a slot is occupied.

Andy
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40495807
Yes, you need to search for gaps and compare those gaps with your variables.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40495856
E.g. when a row exists, then you can insert it.

-- Assumption: There are no overlapping rows in your table.
DECLARE @RoomBookSchedule TABLE
    (
      SlotStart SMALLDATETIME ,
      SlotEnd SMALLDATETIME
    );

INSERT  INTO @RoomBookSchedule
VALUES  ( '20141212 08:30:00', '20141212 09:30:00' ),
        ( '20141212 09:30:00', '20141212 10:30:00' ),
        ( '20141212 12:30:00', '20141212 13:30:00' ),
        ( '20141212 13:30:00', '20141212 14:30:00' );             

DECLARE @SlotStart SMALLDATETIME = '20141212 10:30:00';
DECLARE @SlotEnd SMALLDATETIME = '20141212 11:30:00';

-- @SlotStart and @SlotEnd must be in the same gap.
WITH    Data
          AS ( SELECT   RBS.SlotStart ,
                        RBS.SlotEnd ,
                        LAG(RBS.SlotEnd, 1, '19000101') OVER ( ORDER BY RBS.SlotStart ) AS PreviousSlotEnd
               FROM     @RoomBookSchedule RBS
               UNION ALL
               SELECT   '20790606' ,
                        '20790606' ,
                        MAX(RBS.SlotEnd)
               FROM     @RoomBookSchedule RBS
             )
    SELECT  *
    FROM    Data D
    WHERE   @SlotStart BETWEEN D.PreviousSlotEnd AND D.SlotStart
            AND @SlotEnd BETWEEN D.PreviousSlotEnd AND D.SlotStart;

Open in new window

0
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 2000 total points
ID: 40495858
This works:

create table #RoomBookSchedule
(      Location_ID int
,      SlotStart smalldatetime
,      SlotEnd smalldatetime);


Insert into #RoomBookSchedule
values            (2, '2014-12-12 08:30:00', '2014-12-12 09:30:00')
            ,      (2, '2014-12-12 09:30:00', '2014-12-12 10:30:00')
            ,      (2, '2014-12-12 12:30:00', '2014-12-12 13:30:00')
            ,      (2, '2014-12-12 13:30:00', '2014-12-12 14:30:00')
            
            
            
declare @Location_ID int
declare @SlotStart smalldatetime
declare @SlotEnd smalldatetime
declare @Clinic_ID int

set @Location_ID = 2
set @SlotStart ='2014-12-12 10:30:00'
set @SlotEnd ='2014-12-12 12:30:00'
set @Clinic_ID = 0;

Declare @tempTable table
(	RowNum		int
,	Location_ID	int
,	SlotStart	datetime
,	SlotEnd		datetime
)

insert into @tempTable

Select	top 1
			1
		,	a.Location_ID
		,	a.SlotStart
		,	a.SlotEnd
from	(select		ROW_NUMBER() over (partition by location_ID order by SlotStart) as 'RowNum'
				,	Location_ID
				,	SlotStart
				,	SlotEnd
		from	#RoomBookSchedule) as a
where	a.SlotStart < @SlotStart
order by RowNum desc

insert into @tempTable

Select	top 1
			2
		,	a.Location_ID
		,	a.SlotStart
		,	a.SlotEnd
from	(select		ROW_NUMBER() over (partition by location_ID order by SlotStart) as 'RowNum'
				,	Location_ID
				,	SlotStart
				,	SlotEnd
		from	#RoomBookSchedule) as a
where	a.SlotStart > @SlotStart
order by RowNum


select *
from @tempTable

Declare @NoSlot smallint

Set @NoSlot = 0

If	exists
(select	1
from	@tempTable
where	Location_ID = @Location_ID
and		((RowNum = 1
and		SlotEnd > @SlotStart)
or		(RowNum = 2
and		SlotStart < @SlotStart)
or		(RowNum = 2
and		SlotStart < @SlotEnd)))

Begin
Set @NoSlot = 1
End

Select @NoSlot as 'NoSlot'

drop table #RoomBookSchedule

Open in new window

0
 
LVL 35

Expert Comment

by:ste5an
ID: 40495879
Or
-- Without LAG()
WITH    Data
          AS ( SELECT   RBS.SlotStart ,
                        RBS.SlotEnd ,
                        COALESCE(( SELECT   MAX(I.SlotEnd)
                                   FROM     @RoomBookSchedule I
                                   WHERE    I.SlotEnd <= RBS.SlotStart
                                 ), '19000101') AS PreviousSlotEnd
               FROM     @RoomBookSchedule RBS
               UNION ALL
               SELECT   '20790606' ,
                        '20790606' ,
                        MAX(RBS.SlotEnd)
               FROM     @RoomBookSchedule RBS
             )
    SELECT  *
    FROM    Data D
    WHERE   @SlotStart BETWEEN D.PreviousSlotEnd AND D.SlotStart
            AND @SlotEnd BETWEEN D.PreviousSlotEnd AND D.SlotStart;

Open in new window

.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40495895
btw, when your slots have a constant length, then it's simple integer math. Just project the time to an int..
 
-- Assuming that a slot is always 1h and start always at hh:30:00.
-- Slot available, when no row found.
WITH    Data
          AS ( SELECT   CAST(RBS.SlotStart AS DATE) AS DateDay ,
                        DATEDIFF(MINUTE, '00:30:00', CAST(RBS.SlotStart AS TIME)) / 60 AS SlotNo
               FROM     @RoomBookSchedule RBS
             )
    SELECT  *
    FROM    Data D
    WHERE   D.DateDay = CAST(@SlotStart AS DATE)
            AND D.SlotNo = DATEDIFF(MINUTE, '00:30:00', CAST(@SlotStart AS TIME)) / 60;

Open in new window

0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 40495922
Thanks Guys, Sorry should have said SQL 2012 so cant use LAG(). Koens solution worked.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40495925
You don't need LAG(). It makes it only easier to read. See my second solution. No temp table required.

btw, seems like an important information. Please add this in future posts.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 40495940
I did see your second solutions Ste5an, but I couldn't get them to work, my accepted solution work first time.

I will make sure the sql version is in future posts.

Andy
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question