Calculating monthly periods with non traditional dates...

J

JoeAccess381

My boss has required that I use non-traditional dates (starting the 21st of
one month and ending on the 20th of the next month) in calculating cycle
quantities. How do I write that in my query to have allow it to calculate
that way for multiple separate periods at the same time?

Thanks,

Joe
 
J

John Vinson

My boss has required that I use non-traditional dates (starting the 21st of
one month and ending on the 20th of the next month) in calculating cycle
quantities. How do I write that in my query to have allow it to calculate
that way for multiple separate periods at the same time?

Thanks,

Joe
Click to expand...

One way is to use a "non equi join" to a date-range table. You could
have a Table FiscalDates with fields FYear, FMonth, Start and End,
with records like

2006 January 1/21/2006 2/20/2006
2006 February 2/21/2006 3/20/2006
<and so on>

You could use a Query joining this this to a table containing real
calendar dates in SaleDate:

SELECT yourtable.*,
FiscalDates.Fyear, FiscalDates.FMonth
FROM yourtable
INNER JOIN FiscalDates
ON yourtable.SaleDate >= FiscalDates.Start
AND yourtable.SaleDate <= FiscalDates.End

This will have the added advantage that you can adjust the dates for
holidays.

John W. Vinson[MVP]
 
Top