?
Solved

SQL Check before inserting data

Posted on 2014-03-18
16
Medium Priority
?
458 Views
Last Modified: 2014-03-20
Hi,

I have a table called apts which stores appointments looks like so  

STylistName       StartDate        StartTime       EndDate              EndTime
RP                        15/03/2014         10:30                 15/03/2014              12:15
RP                        15/03/2014         14:45                 15/03/2014              15:45

Now before adding a new appointment I want to do a check that, the stylist is free at the time of the new appointment if free insert data otherwise return 1

So if new appointment was - this would be added
Stylistname = RP
StartDate = 15/03/2014
Starttime = 12:30  
EndDate = 15/03/2014
Endtime = 13:30  

So if new appointment was - this would not be added
Stylistname = RP
StartDate = 15/03/2014
Starttime = 15:00  
EndDate = 15/03/2014
Endtime = 15:45

I can do the insert i need to understand how to do the check

Please help

Thanks

R8VI
0
Comment
Question by:R8VI
[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
  • 6
  • 2
  • +1
16 Comments
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39937746
What are the data types of the columns?  (conversions/casts may be needed)
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39937761
In general there are 3 checks that you need to do:

1) The start datetime of your NEW appoinment  doesn't fall between an existing start/end time

2)  the end datetime of your new appointment doesn't fall between an existing start/end time

3)
the start time of your new appointment < the start time of an existing appointment and the end time of your new appointment > the same appointment.
0
 

Author Comment

by:R8VI
ID: 39937763
Hi,

The datatypes are varchar.

I am not getting the date time in full either for example i am getting the variables as so

@StartDate = '15/03/2014'
@EndDate = '15/03/2014'
@Stylist = 'RP'
@StartTime = '12:30'
@EndTime = '13:30'
0
Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39937786
Normally I wouldn't use betweens for datetimes . . . but  as this is an appointment a 1 minute buffer is okay.

if you want people to be able to end at 15:30 and begin at 15:30 change between to
@starttime >= starttime (datetime cast)  and
@starttime < endtime (datetimecast)

declare @startD varchar(50), @startT varchar(50)
declare @endD varchar(50), @endT varchar(50)
declare @startTime smallDateTime
declare @endTime smalldateTime


select @startTime = cast (convert(datetime, @startD, 103) + @startT as smalldatetime),
          @endTime = cast (convert(datetime, @endD, 103) + @endT as smalldatetime),


select  * 
into #temp
from appointments 
where 
(
--1)
    @starttime between  
               cast (convert(datetime, StartDate, 103) + StartTime  as smalldatetime) 
             and 
                cast (convert(datetime, EndDate, 103) + EndTime  as smalldatetime) 
     or
--2)
   @endTime between 
              cast (convert(datetime, StartDate, 103) + StartTime  as smalldatetime) 
             and 
                cast (convert(datetime, EndDate, 103) + EndTime  as smalldatetime) 
--3)
or ( @startTime < cast (convert(datetime, StartDate, 103) + StartTime  as smalldatetime) 
             and 
     @endTime > 
                cast (convert(datetime, EndDate, 103) + EndTime  as smalldatetime) 
  )
)
and
Stylistname = @Rp

if (select count(*) from #temp) > 0
   select null AppointmentID, -1 errorcode, 'Existing Appointment' errorDesc
else
begin
   -- do insert
   select @@identity AppointmentID, 1 errorcode, 'Success' errorDesc
end

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39938014
DECLARE @StartDateTime datetime
DECLARE @EndDateTime datetime
DECLARE @return_code int

SET @StartDateTime = @StartDate + ' ' + @StartTime
SET @EndDateTime = @EndDate + ' ' + @EndTime

IF EXISTS(
    SELECT 1
    FROM apts a
    CROSS APPLY (
        SELECT
            a.StartDate + ' ' + a.StartTime AS StartDateTime,
            a.EndDate + ' ' + a.EndTime AS EndDateTime
    ) AS assign_alias_names_to_column_calcs
    WHERE
        @Starttime < EndDateTime AND
        @Endtime > StartDateTime
    )
BEGIN
    --overlap found
    SET @return_code = 1
END --IF
ELSE
BEGIN
    --no overlap found, insert new apt
    INSERT INTO apts ( ... )
    SELECT @...,
    SET @return_code = 0
END --ELSE

RETURN @return_code
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39942641
Scott . . .  you didn't take into account the stylist.  I like your logic around the start end times a lot better :-).
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1400 total points
ID: 39942719
>> you didn't take into account the stylist <<

D'OH, quite right.  I left that out of my testing (as it was trivial) but forgot to put it back into the final code.



DECLARE @StartDateTime datetime
DECLARE @EndDateTime datetime
DECLARE @return_code int

SET @StartDateTime = @StartDate + ' ' + @StartTime
SET @EndDateTime = @EndDate + ' ' + @EndTime

IF EXISTS(
    SELECT 1
    FROM apts a
    CROSS APPLY (
        SELECT
            a.StartDate + ' ' + a.StartTime AS StartDateTime,
            a.EndDate + ' ' + a.EndTime AS EndDateTime
    ) AS assign_alias_names_to_column_calcs
    WHERE
        StylistName = @Stylist AND
        @Starttime < EndDateTime AND
        @Endtime > StartDateTime
)
BEGIN
    --overlap found
    SET @return_code = 1
END --IF
ELSE
BEGIN
    --no overlap found, insert new apt
    INSERT INTO apts ( ... )
    SELECT @...,
    SET @return_code = 0
END --ELSE

RETURN @return_code
0
 

Author Comment

by:R8VI
ID: 39943009
Hi ScottPletcher,

I have tried using your way of doing this but I can still insert the appointment multiple times even if its at the same time

I am attaching the code

Could it be the fact that smalldatetime will have seconds and I dont actually store seconds


DECLARE @StartDateTime datetime
DECLARE @EndDateTime datetime
DECLARE @return_code int
DECLARE @StartDate varchar(500)
DECLARE @EndDate varchar(500) 
DECLARE @Stylist varchar(500) 
DECLARE @StartTime varchar(500)  
DECLARE @EndTime varchar(500) 

set @StartTime = '12:30'
set @EndTime = '13:30'
set @StartDate = '15/03/2014' --+ ' ' + @StartTime
set @EndDate = '15/03/2014' --+ ' ' + @EndTime
set @Stylist = 'RP'

Declare @StartDateTimetoConvert smalldatetime
Set @StartDateTimetoConvert = cast (convert(datetime, @StartDate, 103)+ @StartTime  as smalldatetime )
Declare @EndDateTimetoConvert smalldatetime 
Set @EndDateTimetoConvert = cast (convert(datetime, @EndDate, 103)+ @EndTime  as smalldatetime )

SET @StartDateTime = @StartDateTimetoConvert 
SET @EndDateTime = @EndDateTimetoConvert 

IF EXISTS(
    SELECT 1
    FROM tblAppointments a
    CROSS APPLY (
        SELECT
            a.StartDate + ' ' + a.StartTime AS StartDateTime,
            a.EndDate + ' ' + a.EndTime AS EndDateTime
    ) AS assign_alias_names_to_column_calcs
    WHERE
        @Stylist = @Stylist AND
        @Starttime < EndDateTime AND
        @Endtime > StartDateTime
)
BEGIN
    --overlap found
    SET @return_code = 1
END --IF
ELSE
BEGIN
    --no overlap found, insert new apt
    INSERT INTO tblappointments (StartDate, StartTime, Stylist, EndDate, EndTime)
    Values (@StartDate, @StartTime, @Stylist, @EndDate, @EndTime)
    --SELECT @...,
    SET @return_code = 0
END --ELSE

--RETURN @return_code 
Select @return_code

Open in new window

Please help

Thanks

R8VI
0
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 600 total points
ID: 39943073
You have to do the same convert inside the first select:
CROSS APPLY (
        SELECT
          cast (convert(datetime, a.StartDate, 103)+ a.StartTime as smalldatetime ) AS StartDateTime
          cast (convert(datetime, a.EndDate, 103)+ a.EndTime as smalldatetime ) AS EndDateTime
    ) AS assign_alias_names_to_column_calcs
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39943210
>> I have tried using your way of doing this but I can still insert the appointment multiple times even if its at the same time <<

Sorry, but I didn't have sample data readily available to test.  Your sample data was just literals, NOT in a format to load it into an actual table for testing.  I simply don't have time on every q to do the (boring) grunt work of converting:

STylistName       StartDate        StartTime       EndDate              EndTime
RP                        15/03/2014         10:30                 15/03/2014              12:15
RP                        15/03/2014         14:45                 15/03/2014              15:45

to an actual table -- CREATE TABLE ... --
with actual test data -- INSERT ... INTO ....

Doing that often takes me far longer than the resulting SQL itself!


If you'll provide sample data in a table create and insert statements, I'll be happy to test the SQL and see what's going on.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39943254
I see, I think you had a slight typo, which I guess you found:

    WHERE
        @Stylist = @Stylist AND

One of the "@Stylist" should of course be the column name instead :-) .



>> You have to do the same convert inside the first select: <<

Actually you don't -- have you tried running the original code for just the conversion?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39943271
If he's storing the literal '15/03/2014' in the startdate why doesn't he need to convert it in the cross apply?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39943307
I'm certain the default/natural date format on his server is dmy -- why else would dates be stored in that format? -- so sql will by default recognize 'dd/mm/yyyy' as a valid date format.

SET DATEFORMAT dmy

DECLARE @StartDate varchar(10)
SET @StartDate = '15/03/2014'
DECLARE @Starttime varchar(10)
SET @Starttime = '15:00'  
DECLARE @EndDate varchar(10)
SET @EndDate = '15/03/2014'
DECLARE @Endtime varchar(10)
SET @Endtime = '15:45'

DECLARE @StartDateTime datetime
DECLARE @StartDateTimeSmall smalldatetime

SET @StartDateTime = @StartDate + ' ' + @Starttime
SET @StartDateTimeSmall = @StartDate + ' ' + @Starttime

SELECT
    @StartDateTime,
    @StartDateTimeSmall
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39943318
LOL, interesting split.

Good luck on future qs.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39943490
I'm certain the default/natural date format on his server is dmy -- why else would dates be stored in that format? -- so sql will by default recognize 'dd/mm/yyyy' as a valid date format.

Could be varchars . . . that was my impression.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39943560
>> If he's storing the literal '15/03/2014' in the startdate why doesn't he need to convert it in the cross apply? <<

Because the default on his server is dmy, not mdy.

Likewise, on a server where the standard dateformat is 'mm/dd/yyyy', I could just use:
'03/15/2014 13:23:45'
as a datetime/smalltime, without having to use a CONVERT.
0

Featured Post

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 ?
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

777 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