I would like Fiscal Year to be displayed between the ranges below. Currently this script does nothing and only displays the calendar years I already have scripted. Maybe I missing the proper syntax.
MAPPING LOAD * Inline [
(10/31/2009 - 11/01/2010), 2010,
(10/31/2008 - 11/01/2009), 2009,
(10/31/2007 - 11/01/2008), 2008,
(10/31/2006 - 11/01/2007), 2007,
(10/31/2005 - 11/01/2006), 2006,
(10/31/2004 - 11/01/2005), 2005,
(10/31/2003 - 11/01/2004), 2004,
(10/31/2002 - 11/01/2003), 2003,
(10/31/2001 - 11/01/2002), 2002,
I have also tried the below solution I found on the forums.
LOAD date(date#(20061231,'YYYYMMDD')+recno(),'MM/DD/YY') as "invoice_date"
LEFT JOIN ([Fiscal Calendar])
,date(monthstart(invoice_date),'MMM YY') as "Month Fisical"
,date(yearstart(invoice_date),'YYYY') as "Calendar Year"
,date(yearstart(invoice_date,0,4),'YYYY') as "Fiscal Year"
RESIDENT [Fiscal Calendar]
This solution worked better but not all my invoice_dates are showing up. My invoice_date values from the database show up in the YYYY-MM-DD TT:TT:TT.TTT format. I would say it is was a date format conversion issue but the above code 'YYYYMMDD' does convert SOME of the date values correctly and others it does not convert and thus are not displayed. I do not understand why and can not find a pattern.
Thanks for any help on the above or other methods of doing this.
It was a date format issue I just the below in my SQL.
CONVERT(varchar(23),invoice_date,101) AS invoice_date,
,date(yearstart(invoice_date,0,3),'YYYY') as "Fiscal Year"
The 11 value displays how many months 'in' the year you want to be.
...sometimes I enjoy answering my own questions.
Hopefully this will help someone else someday.