0

I have two tables, one is products and another is prices like below

Products table

id    name          price
1     Computer      4
2     Printer       5

Prices table

id    product_id       date_start        date_end       price 
1     1                2017-01-01        2017-01-31     2
2     1                2017-02-03        2017-02-23     3

Query: Get total price between 30.01.2017 and 05.02.2017? Expected Output:

 30.01.2017 to 31.01.2017       2 days       2*2=4
 01.02.2017 to 02.02.2017       2 days       2*4=8 /* this date is not defined in `prices` table, here we should take original price */
 03.02.2017 to 05.02.2017       3 days       3*3=9

What could be the SQL that will give me 21?

I tried so much but no luck :(

Thanks in advance.

TheLittleHenry
  • 33
  • 5
  • There are many things unclear here. Dates you mentioned in output and input do not match at all. There is no logic defined to derive output date from input date ? What query or code you have written so far to solve this? What issue you faced there? What unexpected output you got ? How did the number 21 come up? – Chetan May 06 '17 at 02:37
  • It makes sense to me – Strawberry May 06 '17 at 07:55
  • Hi @Chetan, the date format that i have written in 'price' table is YMD iso date format, in output i wrote it in normal way(it is not sql) just to make thinks clear. I tried with this link http://stackoverflow.com/questions/4647461/hotel-room-rates-for-different-seasons but my problem is: my 'price' table has irregular date ranges. when a date is missing in 'price' table we should take original price which is define in 'Products' table otherwise it should take price from 'Price' table – TheLittleHenry May 06 '17 at 09:36

0 Answers0