Solved

SQL Check before inserting data

Posted on 2014-03-18
16
452 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 350 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 150 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

825 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