FY dates vs. CY dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a report that has order dates. (these dates are calendar
dates - CY). Our company goes by a 4/4/5 week Fiscal calendar year.
Example: January is 12/24/05 - 01/28/06. How do I set my dates to list
January orders with the Fiscal dates.
 
I am working on a report that has order dates. (these dates are calendar
dates - CY). Our company goes by a 4/4/5 week Fiscal calendar year.
Example: January is 12/24/05 - 01/28/06. How do I set my dates to list
January orders with the Fiscal dates.
Click to expand...

I'd suggest setting up a Fiscal Year table (perhaps using fill-down in
Excel for convenience) with the startdate, enddate, and fiscal month
name, from now for the next five or ten years. You can then use a "Non
Equi Join" query:

SELECT <whatever fields you want>, FiscalYear.FiscalMonth
FROM Orders INNER JOIN FiscalYear
ON Orders.OrderDate >= FiscalYear.StartDate
AND Orders.OrderDate <= FiscalYear.EndDate;


John W. Vinson[MVP]
 
Back
Top