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,
The desired results would look as follows.
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.
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
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
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.
Try this -
Result -
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;
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
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..
thanks,
Clint..
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?
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.
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.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions