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.

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?

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clintnash

ASKER

That nailed it. Sorry if the question was confusing to some but as always I am thankful for your time.