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

clintnash
clintnash used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Shalu M

Commented:
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

Author

Commented:
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

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
clintnash

Author

Commented:
There is a lookup table that connects the user to the event they have registered for.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
Software Team Lead
Commented:
pls test if this example suitable in your scenario?

declare @Schedule table
(
	[UID] int, 
	[Schedule] varchar(200),
	GroupID int,
	[StartDate] Datetime null, 
	[EndDate] Datetime null
);

insert into @Schedule
values
(37826, 'Half Day Breakout-6/6-6/10- 8:30-11:30am', 5, '6/6/16 8:30 AM', '6/6/16 11:30 AM'),
(37826, 'General Event', 6, '6/6/16 8:30 AM', '6/6/16 4:00 PM'),
(37914, 'Half Day Breakout-6/6-6/10- 8:30-11:30am', 5, '6/6/16 1:00 PM', '6/6/16 4:00 PM'),
(37914, 'General Event', 6, '6/6/16 8:30 AM', '6/6/16 4:00 PM'),
(37000, 'General Event', 6, '6/6/16 1:00 PM', '6/6/16 4:00 PM'),
(37000, 'Half Day Breakout-6/6-6/10- 8:30-11:30am', 5, '6/6/16 1:00 PM', '6/6/16 4:00 PM'),
(37200, 'General Event', 6, '6/6/16 1:00 PM', '6/6/16 4:00 PM'),
(37200, 'Half Day Breakout-6/6-6/10- 8:30-11:30am', 5, '6/6/16 8:30 AM', '6/6/16 4:00 PM');

;with cte
as
(
	select UID, Schedule, GroupID, cast(StartDate as date) StartDate, case when cast(StartDate as time) = '08:30:00' then 1 else 2 end tmp_Slot,
	row_number() over (partition by UID order by GroupID, StartDate, EndDate) idx
	from @Schedule
), cte2 as
(
	select cte.UID, cte.Schedule, cte.GroupID, cte.StartDate,
	cte.tmp_Slot, cte.idx, cte2.tmp_Slot prev_Slot	
	from cte
	left join cte cte2 on cte2.idx = cte.idx - 1 and cte2.UID = cte.UID
), cte3 as
(
	select UID, Schedule, GroupID, StartDate, tmp_Slot, prev_Slot,
	case when cte2.idx = 1 then
		tmp_Slot
	else
		case when prev_Slot = 2 then
			1
		else
			2
		end		
	end Slot
	from
	cte2
)
Select UID, Schedule, GroupID, StartDate,
case when Slot = 1 then '08:30:00' else '01:00:00' end StartTime,
case when Slot = 1 then '11:30:00' else '04:00:00' end EndTime
from cte3

Open in new window

clintnash

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial