Master Calendar

[suren946]

Hi All,

I have multiple date in single table as below

CATask:

LOAD

     if(len(trim([RITM Number]))>0,[RITM Number]) as RITM,

     if(len(trim([CATASK Number]))>0,[CATASK Number]) as CATask,

     if(len(trim(Queue))>0,Queue) as Queue,

     if(len(trim(Complexity))>0,Complexity) as Complexity,

     Date([Packaging / UAT Task Received date], 'MM/DD/YYYY') as [Packaging Task],

     Date([APF Notified Date],'MM/DD/YYYY') as [APF Notified Date],

     Date([APF ETA Date], 'MM/DD/YYYY') as [APF ETA Date],

     Date([APF Package Delivered Date],'MM/DD/YYYY') as [APF Package Delivered Date],

FROM

xls;

Created multiple Master calendars as below:

[Packaging / UAT Task Received date]:

Load

Date(Temp) as [Packaging Task],

Month(Temp) as [Packaging Task Month],

year(Temp) as [Packaging Task Year]

;

load Mindate+IterNo()-1 as Temp,

Maxdate

While Mindate+IterNo()<= Maxdate ;

Load date( min([Packaging Task]),'MM/DD/YYYY') as Mindate,

   date( Max([Packaging Task]),'MM/DD/YYYY') as Maxdate

Resident CATask;

[APF Notified Date]:

load Temp as [APF Notified Date],

Month(Temp) as [APF Notified Date Month],

year(Temp) as [APF Notified Date Year]

;

load Mindate+IterNo()-1 as Temp,

Maxdate

While Mindate+IterNo()<=Maxdate ;

Load date( min([APF Notified Date]),'MM/DD/YYYY') as Mindate,

   date( Max([APF Notified Date]),'MM/DD/YYYY') as Maxdate

Resident CATask;

[APF ETA Date]: 

load Temp as [APF ETA Date],

Month(Temp) as [APF ETA Month],

year(Temp) as [APF ETA Year]

;

load Mindate+IterNo()-1 as Temp,

Maxdate

While Mindate+IterNo()<=Maxdate ;

Load date( min([APF ETA Date]),'MM/DD/YYYY') as Mindate,

   date( Max([APF ETA Date]),'MM/DD/YYYY') as Maxdate

Resident CATask; 

[APF Package Delivered Date]:

load Temp as [APF Package Delivered Date],

Month(Temp) as [APF Package Delivered Date Month],

year(Temp) as [APF Package Delivered Date Year]

;

load Mindate+IterNo()-1 as Temp,

Maxdate

While Mindate+IterNo()<=Maxdate ;

Load date( min([APF Package Delivered Date]),'MM/DD/YYYY') as Mindate,

   date( Max([APF Package Delivered Date]),'MM/DD/YYYY') as Maxdate

Resident CATask;

I also want to create one common calendar as below but resulting synthetic keys as many dates. Please help me with code to create common master calendar with out SKs.

LOAD floor(if(len(trim(S.No))>0,S.No)) as SNO,

     if(len(trim([RITM Number]))>0,[RITM Number]) as RITM,

     if(len(trim([CATASK Number]))>0,[CATASK Number]) as CATask,

     if(len(trim(Queue))>0,Queue) as Queue,

     if(len(trim([Application Name]))>0,[Application Name]) as AppName,

     Date([Packaging / UAT Task Received date], 'MM/DD/YYYY') as CommonDate,

     Date([Packaging / UAT Task Received date], 'MM/DD/YYYY') as Flag,

     if(len(trim(Complexity))>0,Complexity) as Complexity

FROM