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 End37826 Half Day Breakout-6/6-6/10- 8:30-11:30am 6/6/16 8:30 AM 6/6/16 11:30 AM37826 General Event 6/6/16 1:00 PM 6/6/16 4:00 PM37914 Half Day Breakout-6/6-6/10- 8:30-11:30am 6/6/16 1:00 PM 6/6/16 4:00 PM37914 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.
SQLMicrosoft SQL Server
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.Scheduleunion 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, EndDatefrom dbo.Schedule)select UID, Schedule, StartDate, dateadd(MINUTE, 180, StartDate) as EndDatefrom data order by UID,StartDate;
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?
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)
Open in new window
Result -
Open in new window