To build total/avg/max days per month

[Singlespeed]

I have pre christmas developers block and cant figure this out, it appears simple!!

I have a table with Date Started Treatment, Date Treatment Completed going back 3 years.

I need to produce a monthly summary for the last 12 months to show how many people were in treatment at the end of each month, also how long they were in treament for.

I would like to do it in one query which I can then pivot to make a graph from.

 

[namliam]

Your going to need to generate the end of the month dates with something like this :
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=29069&d=1254389912

I made a while back.

Then have your query "full outer join" (or not join) with this but limit it to return only those end of months between the dates. then its a simple matter of counting.

 

[Singlespeed]

Cheers - the crux of my problem is that I need to count those in treatment at the end of each month, but they might have started before that month so they dont have any date values in that month.

 

[namliam]

Which is why you need to fake the months using something simular to the file I attached.