• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

SQL Check before inserting data

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
R8VI
Asked:
R8VI
  • 7
  • 6
  • 2
  • +1
2 Solutions
 
PadawanDBAOperational DBACommented:
What are the data types of the columns?  (conversions/casts may be needed)
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
R8VIAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Scott . . .  you didn't take into account the stylist.  I like your logic around the start end times a lot better :-).
0
 
Scott PletcherSenior DBACommented:
>> 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
 
R8VIAuthor Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
Scott PletcherSenior DBACommented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
LOL, interesting split.

Good luck on future qs.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now