Multi tool use

Overlapping issue in dates and rates in MySQL and PHP

Issue: overlapping rates

normal date: 2018-06-30 to 2018-07-06
normal rates: 4500 for 2018-06-30 to 2018-07-06

overlapping dates: 2018-07-03 to 2018-06-06
overlapping rate: 1500 for 2018-07-03 to 2018-06-06

Output I want :

(2018-06-30 to 2018-07-06)**
6/30 7/01 7/02 7/03 7/04 7/05
-----------------------------------------
4500 |4500 |4500| 1500| 1500| 1500


but getting

6/30 7/01 7/02 7/03 7/04 7/05
-----------------------------------------
4500 |4500 |4500 | 1500 | 1500 | 1500 |4500 |4500 | 4500


Here error like displaying both rates overlapping and normal rates also

Actual output what I want display

Query:
SELECT r.id,
r.title,
d.roomTypeId,
SUM(IFNULL( t.basePrice , 0)* 1) AS basePrice,
SUM(IFNULL( IF(d.overLap = 1,d.baseCharge,IF(d.isDayRate =
1,d.baseCharge,d.baseCharge)),0) * 1) AS priceAftDisc,
GROUP_CONCAT((IFNULL( IF(d.overLap = 1, IF((1 - t.baseOccupancy) > 0,((((1
- t.baseOccupancy) * d.adultUpCharge) + (0 *
d.childUpCharge))+d.baseCharge),d.baseCharge), IF((1 - t.baseOccupancy) >
0,(((1 - t.baseOccupancy) * d.adultUpCharge)+(0 * d.childUpCharge)+
IF(d.isDayRate = 1,d.baseCharge,d.baseCharge)),IF(d.isDayRate =
1,d.baseCharge,d.baseCharge)) ),0) * 1)) AS `priceBreak`,
t.roomType
FROM dateStr s
INNER JOIN rateDetails d ON d.hotelId = 1 AND d.roomTypeId IN (20,21) AND
s.dateStr BETWEEN periodFrom AND periodTo AND IF(minNights > 0, minNights
= 7,1=1)
INNER JOIN rateMaster r ON r.id = d.rateId AND r.`status` = 'A' AND
r.showOnWeb = 1 AND r.hotelId = 1 AND IF(minNights > 0, 1 =
1,maxLengthofStay <= 7)
INNER JOIN roomTypes t ON t.hotelId = 1 AND t.maxOccupancy >= 1 AND t.id =
d.roomTypeId AND t.id IN (20,21) AND t.`status` = 'A' WHERE s.dateStr BETWEEN '2018-06-30' AND DATE_SUB('2018-07-30',INTERVAL 1 DAY) GROUP BY t.id,d.rateId;

Note@ To get overlapping rates in database i have a column overlapping,if overlapping happen value = 1 (condition)

**Issues in this part ::**
((GROUP_CONCAT((IFNULL( IF(d.overLap = 1, IF((1 - t.baseOccupancy) > 0,((((1 - t.baseOccupancy) * d.adultUpCharge) + (0 * d.childUpCharge))+d.baseCharge),d.baseCharge), IF((1 - t.baseOccupancy) > 0,(((1 - t.baseOccupancy) * d.adultUpCharge)+(0 * d.childUpCharge)+ IF(d.isDayRate = 1,d.baseCharge,d.baseCharge)),IF(d.isDayRate = 1,d.baseCharge,d.baseCharge)) ),0) * 1)) AS priceBreak,))

**after executing query result like below:**


Query execution

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.