Grouping Dates

[KCK]

I have a database of freight invoices that I need to create a crosstab query to see the month the invoice was paid compared to the month of the invoice itself. The problem I have is the month the invoice is paid (INVPAID) is not the calendar month but a custom fiscal month. For example, February 2009 starts 1/29/09 and ends 2/19/09, March 2009 starts 2/20/09 and ends 3/19/09.
The invoice date (INVDATE) is grouped by the calendar month which I have figured out so that is not a problem.
I need to group the amount paid in each INVPAID month. The matrix below shows how I envision the results to look.

Invpaid>>.......Feb 09....Mar 09
---------
Invdate
Mar 09................ 0...... 95
Feb 09............... 56...... 89
Jan 09................50.......45
Dec 08................75.......34
Nov 08................43.......75

Any ideas on how to group the custom fiscal months automatically?

 

[BeerSnob]

I think you're going to need to build a new lookup table that tells you what fiscal month a particular date is part of.

actual_date----fiscal_month
1/1/09---------1/1/09
1/2/09---------1/1/09
:
:
1/28/09--------1/1/09
1/29/09--------2/1/09

Then you link the invdate to actual_date to get your fiscal month .

 

[KCK]

I think you're going to need to build a new lookup table that tells you what fiscal month a particular date is part of.

actual_date----fiscal_month
1/1/09---------1/1/09
1/2/09---------1/1/09
:
:
1/28/09--------1/1/09
1/29/09--------2/1/09

Then you link the invdate to actual_date to get your fiscal month .

HI Beersnob,

Thanks for the advice, it did the trick!