SQL Select - change time values for one row based on value in matching row

I am working on a scheduling page and have hit an interesting issue with trying to manipulate the times. Using the following table as a sample,

UID	        Event	                                         Start	          End
37826	Half Day Breakout-6/6-6/10- 8:30-11:30am	6/6/16 8:30 AM	6/6/16 11:30 AM
37826	General Event	                                6/6/16 8:30 AM	6/6/16 4:00 PM
37914	Half Day Breakout-6/6-6/10- 8:30-11:30am	6/6/16 1:00 PM	6/6/16 4:00 PM
37914	General Event	                                6/6/16 8:30 AM	6/6/16 4:00 PM

Open in new window

I need to change the time of the Full Day Event based on the start time of the half day. So if the half day is 8:30 to 11:30, the time start time for the general event should be 1:00 PM and the end time 4:00pm and vise versa if the Start time of the half day is 1:00 to 4:00 then the general event start should be 8:30 and the end 11:30.

The desired results would look as follows.

UID	        Event	                                         Start	          End
37826	Half Day Breakout-6/6-6/10- 8:30-11:30am	6/6/16 8:30 AM	6/6/16 11:30 AM
37826	General Event	                                6/6/16 1:00 PM	6/6/16 4:00 PM
37914	Half Day Breakout-6/6-6/10- 8:30-11:30am	6/6/16 1:00 PM	6/6/16 4:00 PM
37914	General Event	                                6/6/16 8:30 AM	6/6/16 11:30 AM

Open in new window

Please let me know if this makes sense, I have about nine different swipes at this using case but I think I'm going the wrong direction.

As always your help is greatly appreciated.
Thank you.

SQLMicrosoft SQL Server

Last Comment

clintnash

8/22/2022 - Mon

Shalu M

3/24/2017

Try this -

create table dbo.Schedule(
[ScheduleID] int PRIMARY KEY IDENTITY(1,1),
[UID] int, 
[Schedule] varchar(200),
[StartDate] Datetime null, 
[EndDate] Datetime null
);

insert into Schedule values(37826, 'Half Day Breakout', '6/6/16 8:30 AM', '6/6/16 11:30 AM');
insert into Schedule values(37914, 'Half Day Breakout', '6/6/16 1:00 PM', '6/6/16 4:00 PM');

with data as (
select [UID], Schedule, StartDate, EndDate from dbo.Schedule
union 
select [UID], 'General Event' as schedule, 
case cast(StartDate as time) 
when '08:30:00.0000000' then dateadd(MINUTE, 270, StartDate)
when '13:00:00.0000000' then dateadd(MINUTE, -270, StartDate)
end as StartDate, EndDate
from dbo.Schedule
)
select UID, Schedule, StartDate, dateadd(MINUTE, 180, StartDate) as EndDate
from data order by UID,StartDate;

Open in new window

Result -

UID         Schedule                                                                                                                                                                                                 StartDate               EndDate
----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- -----------------------
37826       Half Day Breakout                                                                                                                                                                                        2016-06-06 08:30:00.000 2016-06-06 11:30:00.000
37826       General Event                                                                                                                                                                                            2016-06-06 13:00:00.000 2016-06-06 16:00:00.000
37914       General Event                                                                                                                                                                                            2016-06-06 08:30:00.000 2016-06-06 11:30:00.000
37914       Half Day Breakout                                                                                                                                                                                        2016-06-06 13:00:00.000 2016-06-06 16:00:00.000

Open in new window

clintnash

3/24/2017

ASKER

Thank you for the response! Unfortunately the General Event actually is one of about 60 events each with specific ID and name. For the purpose of simplicity I change it to 'General Event'. I do have an additional column that I currently don't pull into this query, but I can add groupID to each row as well. The half days have a groupID = 5 and the General Events have a groupID = 6. Any other thoughts on how to do it pulling from the rows?

thanks,
Clint..

Shalu M

3/24/2017

How do you pick an event out of 60 for a certain day? Is it random? Is there any foreign key between the 2 tables?

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

clintnash

3/24/2017

ASKER

There is a lookup table that connects the user to the event they have registered for.

PortletPaul

3/25/2017

Instead of us guessing, why not supply:

a. your existing query
b. sample data from EACH table (data per table, include table name and column names)
c. expected result (which we have - thanks)

a "sample" might just be a few rows.

ASKER CERTIFIED SOLUTION

Ryan Chong

3/28/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

clintnash