Help w/query pls

[Olga]

Hi,

I need to display on a monthly basis the policies sold showing the results
in a row from

their start date to the end date based on the following data:

policy_cost, date_start and date_end



Column one should display the first month (date_start) and the n column the
last month (date_end).

The difference between date_start and date_end vary from 1 month to 48.



Select Sum(Case

When Datepart(mm,date_start) = 1 then
policy_cost/Datediff(mm,date_start,date_end)) end as Jan,

..

Sum(Case

When Datepart(mm,date_start) = 12 then
policy_cost/Datediff(mm,date_start,date_end)) end as Dic

From table

Where datepart(yyyy, date_start) = 2005 **revenues with start date 2005**



The above query returns the first 12 months, how to display the rest of the
months considering

that the last policy can have date_start late December and date_end 48
months later?

Any help is highly appreciated.

Olga

 

[Dale Fye]

Olga,

are you doing this in Access or in SQL Server.

If you are using Access as your front-end to either an Access or SQL Server
backend, you should be able to create a Crosstab query that uses the
DateDiff("m", Date_Start, Date_End) as the column header. If you want to
make sure you have exactly 48 column headers(one for every month for the next
4 years), you might have to define them manually using the ColumnHeadings
property of the cross tab query. Instead of using the datediff as the column
header, you might want to use something like: Format(Date_End, "yyyy-mm")

HTH
Dale