Crosstab Month Sort

[dawnecia]

I have a report that shows summary information received monthy by locations
from policy holders. I would like the information to start with the month of
the effective date for the policy. For example, Policy 1 has an effective
date on 5/14/07, I would like the information to group like:
May 07 June July ........ April 08
Location1 500 500 500 500
location2 700 700 700
Location3 800 800 800

Any help will be appreciated.

 

[MGFoster]

I have a report that shows summary information received monthy by locations
from policy holders. I would like the information to start with the month of
the effective date for the policy. For example, Policy 1 has an effective
date on 5/14/07, I would like the information to group like:
May 07 June July ........ April 08
Location1 500 500 500 500
location2 700 700 700
Location3 800 800 800

Any help will be appreciated.

To sort the column names you have to format the PIVOT clause - something
like this:

PIVOT Format(<date column name>, "yyyy/mm")

This will produce column names like 2007/05 for May, 2007. It's best to
put the year first and then the month, this will sort the dates in
chronological order.

To sort by month name you'd have to actually name the months like this:

PIVOT Format(<date column name>, "mmm") IN ("Jan", "Feb", "Mar", etc)

This precludes inserting the year, 'cuz you'd have to explicitly include
the year number in the IN () clause. Because the year changes over time
you'd have to change the query when the year changed. That's why it is
better to use the "yyyy/mm" format.