The below query allows you to extract the start date and end date for CELCAT Modules. The start date is the date of the first event in you timetable year that is assinged to the Module and the end date is the date of the last event in your timetable year assigned to the Module. -- generate week/date mapping... -- NB only works with contiguous weeks. If you have gaps between -- weeks in your timetable then you'll need to modify this section!
CREATE TABLE #WEEK_DATES (week int, start_date datetime)
DECLARE @wdate1 datetime
select @wdate1 = week1_date from CT_CONFIG
DECLARE @n int
select @n = 1
WHILE @n <= 56
BEGIN
select @wdate1 = DATEADD(day, 7, @wdate1)
insert into #WEEK_DATES values(@n, @wdate1)
select @n = @n + 1
END; -- main select...
select
m.unique_name,
Min(DATEADD(day, e.day_of_week, wd.start_date)) as 'Start Date',
Max(DATEADD(day, e.day_of_week, wd2.start_date)) as 'End date' from CT_EVENT as e
left join CT_EVENT_MODULE as em
on e.event_id = em.event_id
left join CT_MODULE as m
on m.module_id = em.module_id
left join #WEEK_DATES as wd
on CHARINDEX('Y', e.weeks)= wd.week
left join #WEEK_DATES as wd2
on (LEN(e.weeks) + 1 - CHARINDEX('Y', REVERSE(e.weeks))) = wd2.week group by unique_name
order by unique_name DROP TABLE #WEEK_DATES |