Complicated Query??? (1 Viewer)

[TravelerOn]

This is a complicated one!!!!

I need some assistance with the following problem with which I surmise is a query-related problem but there could be other ways to solve it but I'll start here.

I have a table that contains the following for a given record:

Start Date
Finish Date
# of Patients

I would like to generate a report that contains the following conditions or calculations. (I can do it "manually" by dumping the data in a table but that's not really what I want ideally. I have no reason to store this data just need it to be calculated for the report and then discarded.) So here's what I want:

I need it to display the [# of patients] value for each quarter starting 2 quarters before the [start date] until 2 quarters after the [finish date]. The number of patients is constant.

For example,
Startdate = Jan 1, 2002 = Q1/02
Finishdate = Oct 1, 2002 = Q4/02
# of patients = 300

then it should display: Q3/01 ---> 300, Q4/02 ---> 300, etc.... up to Q2/03 ----> 300 and then stop.

How do I write this?

Thanks.

 

[Jon K]

Since the number of quarters between the StartDate and FinishDate varies in each record, I don't think you can achieve what you desired with a query or queries

 

[TravelerOn]

Then...

Would it be more appropriate to approach this using code?