0

I have two tables,
1. items
2. festival_rents

Sample records: items 

id | name   |  rent 
------------------------
1  |  Car   |   100             
2  |  Truck |   150   
3  |  Van   |   200

Sample records: festival_rents 

id | items_id   | start_date | end_date   | rent 
------------------------------------------------
1  |  1         | 2018-07-01 | 2018-07-02 | 200  
2  |  1         | 2018-07-04 | 2018-07-06 | 300
3  |  3         | 2018-07-06 | 2018-07-07 | 400

The table items contains list of items with name and rent. Each item in the items table may or may not have festival_rents. The table festival_rents has higher rents for each item for a date range with start_date and end_date. It is possible for a item to have multiple festival_rents with different date ranges. But it's for sure that date ranges for multiple festival_rents belonging to a same item won't collide and all date ranges are isolated.

The query that I'm looking for is, for a given start_date and end_date range, for each item in the items table, calculate the total rent and display each item with it's calculated total rent. The rent calculation for each item should include the festival_rents also, if any of the items has festival_rents falling within the given start_date and end_date.

Expected result:

Input: start_date=2018-07-01 and end_date=2018-07-06

Output:

id | name   | total_price 
------------------------
1  |  Car   |   1100          // 1st 2 days festival rent + 1 day normal rent + last 3 days festival rent (2 * 200) + (1 * 100) + (3 * 200)  
2  |  Truck |   900           // 6 days normal rent (6 * 150)
3  |  Van   |   1400          // 5 days normal rent + 1 day festival rent (200 * 5) + (400 * 1)
Kanmaniselvan
  • 522
  • 1
  • 8
  • 23

1 Answers1

0

You need a list of days either on a table or create on the fly:

How to get list of dates between two dates in mysql select query

Generating time series between two dates in PostgreSQL

SELECT i.name, SUM (f.rent)
FROM allDays a
JOIN festival_rents f
  ON a.day >= f.start_date 
 AND a.day < f.end_date  
JOIN items i
  ON f.item_id = i.item_id 
WHERE a.day BETWEEN @start_date
                AND @end_date
GROUP BY i.name

I assume the end_date is open range. So if you have ranges [A,B) and [B,C) Date B will have the rent from [B,C)

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Is it possible to do the aggregation without creating a table on the fly?. I don't understand what you meant by `end_date` is open range in the last line of your answer. If you are talking about the `end_date` in `festival_rents`, then end_dates won't collide with other `festival_rents` ranges. So with the example you gave, it will be `[A, A]` and `[B, C]` – Kanmaniselvan Nov 10 '17 at 02:24
  • open range mean: `start <= value < end` .... close range mean: `start <= value <= end`. And isn't possible because you need to assign a property to each day. You can create a permanent table to keep all days. That is only `365` rows for each years. or `36.500` for 100 years So isn't a big deal. – Juan Carlos Oropeza Nov 10 '17 at 12:54