Hi all
I am looking to have one calendar controlled by 3 dates [Arrival Date], START_DATE and END_DATE
As of now the calendar is controlled by [Arrival Date]
Script below:
//Temporary Calendar Created
TempDates:LOAD
[Arrival Date]
Resident xxx
Where isnull([Arrival Date])=0
Order By [Arrival Date];
LET vMinDate =Num(Peek('Arrival Date', 0,'TempDates'));
//LET vMaxDate =Num(Peek('Arrival Date', -1,'TempDates')); //needed at end of year to prevent rolling over into new financial year
LET vMaxDate = num(WeekEnd(Today()));LET vToday = num(Today());
Drop Table TempDates;
TempCalendar:LOAD
$(vMinDate) + ROWNO() - 1 AS Num,
DATE($(vMinDate) + ROWNO() - 1) AS TempDate
AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;
//Create Master Calendar
MasterCalendar:LOAD
TempDate as [Arrival Date]
,date(TempDate) as [ArrivalDateDate]
,Month(TempDate) as [ArrivalDateMonth]
,MonthName(TempDate) as [ArrivalDateMonthYear]
,if(Month(TempDate)>=4 and Month([TempDate])<=6,'Q1',
if(Month(TempDate)>=7 and Month([TempDate])<=9,'Q2',
if(Month([TempDate])>=10 and Month([TempDate])<=12,'Q3',
if(Month([TempDate])>=1 and Month([TempDate])<=3,'Q4',)))) as [ArrivalDateFinQuarter]
,if(Month([TempDate])<4,
Year([TempDate])-1 & '/' & num(fmod(Year([TempDate]),100),'00'),
Year([TempDate]) & '/' & num(fmod(Year([TempDate])+1,100),'00') ) as [ArrivalDateFinYear]
,If(Month(weekstart(TempDate))<4 ,num(fmod(Year(weekstart(TempDate))-1,100),'00') &'/' & fmod(Year(weekstart(TempDate)),100) , num(fmod(Year(weekstart(TempDate)),100),'00') &'/' & fmod(Year(weekstart(TempDate))+1,100) ) as NHSYearWE
,WeekEnd([TempDate]) as [ArrivalDateWeekEndDate]
,Weekday([TempDate]) as [ArrivalDay]
,if(weekend(TempDate) <= weekend(date('31/03/' & year(TempDate),'dd/mm/yyyy')),
// if true then in second year of the financial year
if(weekend(date('1/4/' & (year(TempDate) - 1),'dd/mm/yyyy')) < weekend(date('7/4/' & (year(TempDate) - 1),'dd/mm/yyyy')),
// if true then 1st april is not in the first week of the financial year
(interval(weekend(TempDate) - weekend(date('7/4/' & (year(TempDate) - 1),'dd/mm/yyyy')))/7) + 1,
(interval(weekend(TempDate) - weekend(date('1/4/' & (year(TempDate) - 1),'dd/mm/yyyy')))/7) + 1),
if(weekend(date('1/4/' & year(TempDate),'dd/mm/yyyy')) < weekend(date('7/4/' & year(TempDate),'dd/mm/yyyy')),
// if true then 1st april is not in the first week of the financial year
(interval(weekend(TempDate) - weekend(date('7/4/' & year(TempDate),'dd/mm/yyyy')))/7) + 1,
(interval(weekend(TempDate) - weekend(date('1/4/' & year(TempDate),'dd/mm/yyyy')))/7) + 1)) as [FinWeekNo]
,Day([TempDate]) as [ArrivalDayNumber]
,year(TempDate) as [ArrivalYear]
,if(Month(TempDate)>=4,Month(TempDate)-3, Month(TempDate)+9) as [ArrivalDateFinMonth]
,date(TempDate, 'YYYYMM') as ArrivalDateYearMonthNumber
RESIDENT TempCalendar
ORDER BY TempDate BCD;
DROP Tables TempCalendar;
