CODEWITHSUNDEEP
databasems-access
tamat
tamat

Reputation: 301

Database pricing tables aggregation issue

I'm using MS access for my work and have an issue that took me way too long to solve myself, so i decided to ask a you, guys. The question is next: I have two tables for pricing: a_prices

article5    a_price from_date   to_date
00009       952.00  01/01/2012  31/12/2013
00009       720.00  01/01/2014  31/12/2015
00009       750.00  01/01/2016  31/12/2199

and a_client_prices

client_id   article5    a_price from_date   to_date
1           00009       700     31/12/2014  11/09/2015
2           00009       800     05/10/2015  07/04/2016

The problem is to make a query that returns a table of prices for exact client, so if input of that query were "where client_id=1" the resulting table would look like next:

article5    a_price from_date   to_date
00009       952.00  01/01/2012  31/12/2013
00009       720.00  01/01/2014  30/12/2014
00009       700     31/12/2014  11/09/2015
00009       720.00  12/09/2014  31/12/2015
00009       750.00  01/01/2016  31/12/2199

And if client_id=2 then:

article5    a_price from_date   to_date
00009       952.00  01/01/2012  31/12/2013
00009       720.00  01/01/2014  04/10/2015
00009       800     05/10/2015  07/04/2016
00009       750.00  08/04/2016  31/12/2199

So inside the query there should be something like overlap-excluding mechanism, where a_client_prices have higher priority, but i can't imagine anything better then join table which contain record for every day, exclude excessive records and then aggregate it back to periods.

Take into consideration, that i'm using ms access, so i can easily use custom aggregation function, if you could advise one. I feel it could be one of the solutions, but have poor experience in that field to understand how to design one myself. Thanks for your help in advance, sorry if i missed the answer posted before that. Feel free to judge the initial data organization and propose other possibilities.

P.S. The solution with day by day aggregation is perfectly working, but take way too much processing time and the query is used way too much, so this solution was rejected.

Upvotes: 2

Views: 47

Answers (1)

Andre
Andre

Reputation: 27634

This cannot be done with a query.

You are taking this source record:

00009       720.00  01/01/2014  31/12/2015

and splitting it up into two records, changing the date fields on the fly:

00009       720.00  01/01/2014  30/12/2014
00009       720.00  12/09/2014  31/12/2015

And this is just one of several different cases.

No aggregation function will do this. You need a VBA solution, walking through the recordsets, and creating the target records in a new table.

Edit

My approach for calculating one client's price list would be:

  • delete * from temp table
  • Insert * from a_prices into temp table
  • for each record in a_client_prices:
    • if it "fits" into one date range: duplicate that, adapt from- & to-dates
    • if it overlaps two date ranges: adapt both records to exclude the new date range
    • insert a_client_prices record into temp table

It needs quite a bit of logic, but should be really fast.

Upvotes: 1

Related Questions