WondersWhy
Technical User
Greetings from Minneapolis!
Hopefully one of the experts here can help with a little puzzle and find the missing piece.
I have a table that I have created in SQL that contains all of our business calendar information. It has a column for YearNbr, MonthNbr, WeekNbr, WorkingDayNbr, etc. The key join column is simply "Date". Every day within a range from 1996 through 2012 has all of the above characteristics defined so that my Impromptu reports match our main ERP system's accounting periods. It works pretty slick, but...
If I wanted a report that shows a summary of the last three months sales, and I were able to rely on simply the Gregorian Calander, I could simply extract the month from "now", extract the month from the transaction date, and ... you know the routine.
You might already imagine my predicament. Say it is December 29th, 2003 and we have already begun the year 2004. I want to start reporting on this new year. I need to take today's date (12/29/2003), kick it up against the custom calander, recognize that it is now 2004, and report the new current Jan 2004 (MTD), and last two full months (Dec 2003 and Nov 2003). Lunar months I remind you!
How do I do that? I guess that what I would hope for is some kind of special conditional trick to recognize the current period based on today as a constant (since today changes from day to day of course...) I want to define this in my SQL catalog conditions if I can.
I can do simple tricks like (Now - transaction date < 30 days) and get rolling reports, but I only get perfect "full" periods a few times a year. Plus... If I want to use days between kind of functions, it seems I can only get up to 999 days (side issue?). I would like for instance, to build queries for summary reports that go 5 years back (full fiscal lunar years back to be specific).
Am I completely out to lunch? Barking at the moon?
If I am, or if I'm not, thanks a bunch for your input!
Tony T.
Hopefully one of the experts here can help with a little puzzle and find the missing piece.
I have a table that I have created in SQL that contains all of our business calendar information. It has a column for YearNbr, MonthNbr, WeekNbr, WorkingDayNbr, etc. The key join column is simply "Date". Every day within a range from 1996 through 2012 has all of the above characteristics defined so that my Impromptu reports match our main ERP system's accounting periods. It works pretty slick, but...
If I wanted a report that shows a summary of the last three months sales, and I were able to rely on simply the Gregorian Calander, I could simply extract the month from "now", extract the month from the transaction date, and ... you know the routine.
You might already imagine my predicament. Say it is December 29th, 2003 and we have already begun the year 2004. I want to start reporting on this new year. I need to take today's date (12/29/2003), kick it up against the custom calander, recognize that it is now 2004, and report the new current Jan 2004 (MTD), and last two full months (Dec 2003 and Nov 2003). Lunar months I remind you!
How do I do that? I guess that what I would hope for is some kind of special conditional trick to recognize the current period based on today as a constant (since today changes from day to day of course...) I want to define this in my SQL catalog conditions if I can.
I can do simple tricks like (Now - transaction date < 30 days) and get rolling reports, but I only get perfect "full" periods a few times a year. Plus... If I want to use days between kind of functions, it seems I can only get up to 999 days (side issue?). I would like for instance, to build queries for summary reports that go 5 years back (full fiscal lunar years back to be specific).
Am I completely out to lunch? Barking at the moon?
If I am, or if I'm not, thanks a bunch for your input!
Tony T.
