[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Check before inserting data

Posted on 2014-03-18
16
Medium Priority
?
462 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 41

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 41

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 70

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 41

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 70

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 41

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 70

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 70

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 41

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 70

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 70

Expert Comment

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

Good luck on future qs.
0
 
LVL 41

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 70

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

826 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