Query to find and format all Calendar events
-
Hi everyone, I'm working on a asp.net web page to display calendar events. The asp.net page uses a calendar control that I'm stuck with and could do with some help getting the data out of the db in the right format.
Here are 5 sample events and how I want the calendar control to display them:
Here's how the HTML code is formatted (note the "event-1-on-3" "event-2-on-3" and "event-3-on-3" which basically means that appointment overlaps with 2 others and so takes up 1/3 of the screen).
So my problem is; When I query the SQL db for events, I need to know which events overlap and assign those overlapping events a "1 OF #", "2 OF #" tag, so I can put them in the right HTML class above. I have control of the Calendar table, so that can be amended as necessary, but for now, here's what's in it:
CREATE TABLE usr_Calendar ( [ID] int, [Title] varchar(255), [Start] datetime, [Finish] datetime )INSERT INTO usr_Calendar
VALUES
( 1, 'Booking1', N'2017-12-18T09:00:00', N'2017-12-18T10:00:00' ),
( 2, 'Booking2', N'2017-12-18T09:00:00', N'2017-12-18T11:00:00' ),
( 3, 'Booking3', N'2017-12-18T10:00:00', N'2017-12-18T11:00:00' ),
( 4, 'Booking4', N'2017-12-18T12:00:00', N'2017-12-18T13:00:00' ),
( 5, 'Booking5', N'2017-12-18T10:00:00', N'2017-12-18T12:00:00' )SELECT *
FROM usr_CalendarCould anyone help me with a sproc or query which basically returns all events and works out the overlapping events like this:
Thanks in advance. Nick.
-
YUK!
interesting challenge. If I were to do it in the code I would be looking at either RANK() OVER() in a CTE to sequence the entries and then left join the CTE to itself based on CTE2.StartDate <= CTE1.EndDate. When you have data in the right hand table then there is an overlap.
Alternatively you could do it with a CURSOR, order the entries by start datetime and track the largest end datetime, If the current records start datetime < max end datetime then it is part of the same group. Assign a group number and then see if the current records end datetime > max end datetime then update max end datetime with current record end (this caters for E1 = 09:00-17:00, E2 = 11-:00-13:00, E3 = 12:00-18:00
HOWEVER
you should be able to solve your layout problem using either CSS flexbox to get the events to float right. Also have a look to see whether CSS grids might help you out. -
Hi Aaron,
Yeah, 'yuk' was my first response too.
Thanks for the advice, it'll be much easier to sort the formatting in the CSS and I've since found a nice calendar that will do this for me. Link below if anyone finds themselves in the same situ.
So my problem is; When I query the SQL db for events, I need to know which events overlap and assign those overlapping events a "1 OF #", "2 OF #" tag, so I can put them in the right HTML class above. 
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply