Associating Multiple Date Fields to a single fiscal calendar
I have a calendar table that with gregorian date as the "key" and Fiscal Month, Year, Week as fields. I have an app that allows me to define a fiscal calendar for different clients; e.g. 4-4-5 quarters with every period starting on a Sunday - for example 2011 starting Jan 2, 2011. Periodically you have to add a 53 week to shift the calendar a bit. So given any gregorian date, I can tell you exactly what is the fiscal time. If I had only one date in the field list it's easy just Select FiscalPeriod,FiscalYear,GregorianDate as 'OrderDate' from dimTime to associate it with Select OrderNbr,OrderDate from Orders. Of course their is never one date in a table; order date, requested date, promise date, revised promise date, maybe you'll get it on this date, etc. I want to be able to summarize data by FiscalPeriod for multiple date fields.
Thanks, the latter link was the most helpful. This is how I did it :
SQL SELECT "DAY_",
CpnyID, OrdNbr, LineRef, SchedRef,PromDate as "DAY_",
'PROMDATE' as DateType
CpnyID, OrdNbr, LineRef, SchedRef,ReqDate as "DAY_",
'REQDATE' as DateType
I build a Group Hierarchy from the fiscal calendar to allow drill down from fiscal year, to quarter, to month, and to week -- in 4-4-5 every fiscal month starts on a Sunday and ends on a Saturday and has 4 or 5 weeks.
In the table below 5/30/2011 in is Fiscal_Month 2011-006 since the Fiscal_Month 2011-005 ending 5/28/2011