hellismyrestingplace Posts - Answer Overflow

hellismyrestingplace

•Created by hellismyrestingplace on 4/16/2025 in #help

Sum Count Of Weekend Days In Overlapping Periods (MySQL)

I need to get a total count of all Fridays and Saturdays for all overlapping periods against my selected Checkin and Checkout dates. Table "special_prices" structure:

- id (int)
- apartment_id (int)
- start (date)
- end (date)
- price (int)

- id (int)
- apartment_id (int)
- start (date)
- end (date)
- price (int)

The current query is using a stored function that works but I need a solution that doesn't use that but rather some raw sql approach for better performance. Current query for context (along with PHP bit):

$weekendDays = Helper::getNumberOfWeekendDays($checkIn, $checkOut);

            $query->leftJoin(\DB::raw('(
                SELECT
                    apartment_id,
                    SUM(DATEDIFF(LEAST("' . $checkOut . '", end), GREATEST("' . $checkIn . '", start)) * price) AS total_special_price,
                    SUM(DATEDIFF(LEAST("' . $checkOut . '", end), GREATEST("' . $checkIn . '", start))) AS special_days_count,
                    SUM(count_weekend_days(GREATEST("' . $checkIn . '", start), LEAST("' . $checkOut . '", end))) AS special_weekend_days
                FROM special_prices
                WHERE start < "' . $checkOut . '" AND end > "' . $checkIn . '"
                GROUP BY apartment_id
            ) AS sp'), 'sp.apartment_id', '=', 'a.id');

            $priceExpression = "ROUND(
                (pr." . $column . " * (" . $numberOfNights . " - COALESCE(sp.special_days_count, 0))
                + (COALESCE(a.weekend_extra_price, 0) * (" . $weekendDays . " - COALESCE(sp.special_weekend_days, 0)))
                + COALESCE(sp.total_special_price, 0)
                ) / " . $numberOfNights . "
            )";

$weekendDays = Helper::getNumberOfWeekendDays($checkIn, $checkOut);

            $query->leftJoin(\DB::raw('(
                SELECT
                    apartment_id,
                    SUM(DATEDIFF(LEAST("' . $checkOut . '", end), GREATEST("' . $checkIn . '", start)) * price) AS total_special_price,
                    SUM(DATEDIFF(LEAST("' . $checkOut . '", end), GREATEST("' . $checkIn . '", start))) AS special_days_count,
                    SUM(count_weekend_days(GREATEST("' . $checkIn . '", start), LEAST("' . $checkOut . '", end))) AS special_weekend_days
                FROM special_prices
                WHERE start < "' . $checkOut . '" AND end > "' . $checkIn . '"
                GROUP BY apartment_id
            ) AS sp'), 'sp.apartment_id', '=', 'a.id');

            $priceExpression = "ROUND(
                (pr." . $column . " * (" . $numberOfNights . " - COALESCE(sp.special_days_count, 0))
                + (COALESCE(a.weekend_extra_price, 0) * (" . $weekendDays . " - COALESCE(sp.special_weekend_days, 0)))
                + COALESCE(sp.total_special_price, 0)
                ) / " . $numberOfNights . "
            )";

- I can't change the table structure - having a separate row pear each special price date could solve this problem but I must use the start - end - special_price "end" column should not be counted in the special_weekend_days Goal is to find a way to accurately count all Fridays and Saturdays (special_weekend_days) in all overlapping periods.

411 replies

Gaming

Programming