question

leo 1 avatar image
leo 1 asked

SQL Script

Hi,

I am trying to get the script done for my own but I really need help and it is difficult for me to lop through the code in store procedure.

What I want to achieve is, for instance -

There is 2 parameters, Parameter A as start and end time (09:00 - 15:00) what value we know in the table. But when Parameter B send the time like this (07:00 - 10:00), I want return value like this - 07:00-09:00, 09:00-10:00

when parameter B send the time like that (10:00 - 12:00), I want return vaule like this - 10:00-12:00

When parameter B send the time like this (13:00 - 17:00), I want return value like this - 13:00-15:00,15:00-17:00

When Parameter B send the time like that (15:00 - 18:00), I want return value like this - 15:00-18:00


Basically any within rage of Parameter A check with B value and return value if B values are between A otherwise split and return the time.

Thanks in advance.


timesplitsql2016
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Part of this is going to be how you parse out the times from the input parameters - I've not worried too much about that here, assuming that the parameters are well-formed and always specify the time in a 0-leading 24hr format.

I define 2 time frames : A, which is determined by the start and end times of ParamA, and B which is defined by the times in ParamB

There are then 6 possibilities:

  1. B starts and ends entirely before A
  2. B starts before A, but ends during A
  3. B starts and ends during A
  4. B starts during A but ends after A
  5. B starts and ends entirely after A
  6. B starts before A and ends after A


In my example I've included an id as the first column so you can see which ones match with different inputs


declare @ParamA varchar(50)
declare @ParamB varchar(50)

set @ParamA = '09:00 - 15:00'
set @ParamB = '08:00 - 16:00'

declare @AllTimes table 
 (TimeFrameAStart time,TimeFrameAEnd time,TimeFrameBStart time,TimeFrameBEnd time)
insert into @AllTimes 
 (TimeFrameAStart, TimeFrameAEnd, TimeFrameBStart, TimeFrameBEnd)
select 
    cast(left(@ParamA,5) as time), cast(right(@ParamA,5) as time), 
    cast(left(@ParamB,5) as time), cast(right(@ParamB,5) as time)
where cast(left(@ParamA,5) as time) <= cast(right(@ParamA,5) as time)
and cast(left(@ParamB,5) as time) <= cast(right(@ParamB,5) as time)

select * from @AllTimes


-- B is entirely before A starts - just one time segment
select 1 as id, TimeFrameBStart, TimeFrameBEnd from @AllTimes where TimeFrameBStart < TimeFrameAStart and TimeFrameBEnd <= TimeFrameAStart
union all
-- B starts before A starts, and B ends before A ends - split into 2 time segments
select 2, TimeFrameBStart, TimeFrameAStart from @AllTimes where TimeFrameBStart < TimeFrameAStart and TimeFrameBEnd > TimeFrameAStart and TimeFrameBEnd <= TimeFrameAEnd
union all
select 2, TimeFrameAStart, TimeFrameBEnd from @AllTimes where TimeFrameBStart < TimeFrameAStart and TimeFrameBEnd > TimeFrameAStart and TimeFrameBEnd <= TimeFrameAEnd
union all
-- B starts after A starts, and B ends before A ends  - just one time segment
select 3, TimeFrameBStart, TimeFrameBEnd from @AllTimes where TimeFrameBStart >= TimeFrameAStart and TimeFrameBEnd <= TimeFrameAEnd
union all
-- B starts after A starts, and B ends after A ends  - split into 2 time segments
select 4, TimeFrameBStart, TimeFrameAEnd from @AllTimes where TimeFrameBStart >= TimeFrameAStart and TimeFrameBStart < TimeFrameAEnd and TimeFrameBEnd > TimeFrameAStart and TimeFrameBEnd > TimeFrameAEnd
union all
select 4, TimeFrameAEnd, TimeFrameBEnd from @AllTimes where TimeFrameBStart >= TimeFrameAStart and TimeFrameBStart < TimeFrameAEnd and TimeFrameBEnd > TimeFrameAStart and TimeFrameBEnd > TimeFrameAEnd
union all
-- B is entirely after A ends - just one time segment
select 5, TimeFrameBStart, TimeFrameBEnd from @AllTimes where TimeFrameBStart >= TimeFrameAEnd and TimeFrameBEnd > TimeFrameAEnd
union all
-- B starts before A starts, and ends after A ends - split into 3 segments
select 6, TimeFrameBStart, TimeFrameAStart from @AllTimes where TimeFrameBStart < TimeFrameAStart and TimeFrameBEnd > TimeFrameAEnd
union all
select 6, TimeFrameAStart, TimeFrameAEnd from @AllTimes where TimeFrameBStart < TimeFrameAStart and TimeFrameBEnd > TimeFrameAEnd
union all
select 6, TimeFrameAEnd, TimeFrameBEnd from @AllTimes where TimeFrameBStart < TimeFrameAStart and TimeFrameBEnd > TimeFrameAEnd


3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

leo 1 avatar image leo 1 commented ·

@Kev Riley that is very close and your thought is fully cover for all range except below 2 type -

set @ParamA = '09:00 - 15:00'

set @ParamB = '08:00 - 15:00'

set @ParamB = '09:00 - 16:00'

If I pass 2 sample of @ParamB, it return NULL, can you check please?


Thanks.

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ leo 1 commented ·

A tweak on the bound changing from < to <=
I've updated the query in the answer

0 Likes 0 ·
leo 1 avatar image leo 1 Kev Riley ♦♦ commented ·

Thank you very much

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.