Calculating monthly periods with non traditional dates...

[Guest]

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

 

[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

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]