Avatar of clintnash
clintnash
Flag for United States of America asked on

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

Avatar of undefined
Last Comment
clintnash

8/22/2022 - Mon
Shalu M

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

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

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

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

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

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

ASKER
That nailed it. Sorry if the question was confusing to some but as always I am thankful for your time.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.