ddd
SELECT
t1.car_no,
SUM(DATEDIFF(FROM_UNIXTIME(t1.allocation_to_date), FROM_UNIXTIME(t1.allocation_from_date)) + 1) AS total_days_booked,
SUM(
CASE
WHEN t2.allocation_to_date IS NOT NULL THEN
DATEDIFF(FROM_UNIXTIME(t1.allocation_from_date), FROM_UNIXTIME(t2.allocation_to_date)) - 1
ELSE
0
END
) AS missing_days
FROM
tbl_booked_car_details t1
LEFT JOIN
tbl_booked_car_details t2 ON t1.car_no = t2.car_no AND FROM_UNIXTIME(t1.allocation_from_date) > FROM_UNIXTIME(t2.allocation_to_date)
GROUP BY
t1.car_no;
Comments
Post a Comment