Beginning and Ending dates for groups of events

[Michael Kucan]

I was hoping to get some help. I need a "pure SQL" solution (no VBA) to the
following challenge:

My customers have products for which they are charged a monthly fee. They
can have more than one product active at one time. I need a query that shows
the begging and end date for the product with the largest monthly fee. For
example:

Product Eff Dt End DT Monthly Fee
A 01/01/2007 12/31/2007 $40
B 05/01/2007 07/15/2007 $70

I need the following output:
Product Eff Dt End Dt Monthly Fee
A 01/01/2007 04/30/2007 $40
B 05/01/2007 07/15/2007 $70
A 07/16/2007 12/31/2007 $40


Any ideas?

 

[Michel Walsh]

You need a driver table with value for the first of each month (each year)



MonthDriver 'table name
md ' field name
1/1/2007
2/1/2007
3/1/2007
....
12/1/2007 ' data sample


then,

SELECT d.md, a.product, a.monthlyFee as Fee
FROM monthDriver as d INNER JOIN yourTable As a
ON d.md BETWEEN a.effDt AND a.endDate


will properly explode your date ranges into multiple records (one per
applicable month). Save that query as qu1 and then use one of the four
techniques described in http://www.mvps.org/access/queries/qry0020.htm to
get the max per month AND its associated record values (for the other
fields).



Hoping it may help,
Vanderghast, Access MVP

 

[Jamie Collins]

You need a driver table with value for the first of each month (each year)

MonthDriver 'table name
md ' field name
1/1/2007
2/1/2007
3/1/2007
...
12/1/2007 ' data sample

Who said the considered start date for the monthly fee was the first
day of the month? The OP's expected results indicate they want to know
the day the highest fee changes, therefore a Calendar table showing
one row for every considered day could be useful...

SELECT d.md, a.product, a.monthlyFee as Fee
FROM monthDriver as d INNER JOIN yourTable As a
ON d.md BETWEEN a.effDt AND a.endDate

will properly explode your date ranges into multiple records (one per
applicable month). Save that query as qu1 and then use one of the four
techniques described inhttp://www.mvps.org/access/queries/qry0020.htmto
get the max per month AND its associated record values (for the other
fields).

FWIW I can't get such a JOIN condition to work unless it is enclosed
in parentheses.

The following uses a Calendar date to generate a 'schedule' of top
fees for each considered day:

SELECT C1.dt, T1.CustomerID, MAX(T1.monthlyFee) AS top_fee
FROM yourTable AS T1, Calendar AS C1
WHERE C1.dt BETWEEN T1.effDt AND T1.endDate
GROUP BY C1.dt, T1.CustomerID;

Note the product with the top fee for that day can only be uniquely
identified if the monthly fees are themselves unique!

I can't think of a good way of converting contiguous days into periods
of start and end dates using pure Jet SQL. At this point I think even
I would be tempted to loop through a cursor (i.e. recordset) looking
for the changes in top_fee...

Jamie.

--