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.
LVL 1
clintnashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shalu MCommented:
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

0
clintnashAuthor 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..
0
Shalu MCommented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Learn more
clintnashAuthor Commented:
There is a lookup table that connects the user to the event they have registered for.
0
PortletPaulfreelancerCommented:
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.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
clintnashAuthor Commented:
That nailed it. Sorry if the question was confusing to some but as always I am thankful for your time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.