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
( 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' )
Could 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.