FY dates vs. CY dates

[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.

 

[John Vinson]

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'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]