Solved

SQL Check before inserting data

Posted on 2014-03-18
16
445 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
Comment Utility
What are the data types of the columns?  (conversions/casts may be needed)
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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:ScottPletcher
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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:
ScottPletcher earned 350 total points
Comment Utility
>> 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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 150 total points
Comment Utility
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:ScottPletcher
Comment Utility
>> 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:ScottPletcher
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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:ScottPletcher
Comment Utility
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:ScottPletcher
Comment Utility
LOL, interesting split.

Good luck on future qs.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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:ScottPletcher
Comment Utility
>> 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now