Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calculating a conditional cumulative sum in Oracle

Tags:

sql

oracle

The broad view of what I'm trying to do is find out how many reservations that have not yet happened yet are on the books in the entire system, by the date that the reservation was booked. This means counting the number of all records that exist with a redemption_date after or equal to booking_date, grouping by booking_date. Please see the following hypothetical example for a better explanation:

redemption_date      booking_date
2013-01-01           2013-01-01
2013-01-06           2013-01-01
2013-01-06           2013-01-01
2013-01-07           2013-01-02
2013-01-08           2013-01-03
2013-01-09           2013-01-04
2013-01-10           2013-01-05
2013-01-10           2013-01-05
2013-01-10           2013-01-05
2013-01-10           2013-01-05
2013-01-10           2013-01-05
2013-01-11           2013-01-05

I would like the result:

booking_date         number_of_reservations
2013-01-01           3
2013-01-02           3
2013-01-03           4
2013-01-04           5
2013-01-05           11

But my brain is completely failing me as to how the query should be structured. Any tips? Thanks!

Edit: To clarify, number_of_reservations should be the number of reservations that were booked on that date, as well as those booked on days AFTER that. In other words, number_of_reservations is the number of reservations that are in the database as of booking_date (that have not yet happened). My original results did have mistakes. Sorry for the confusion

like image 354
the Internet Avatar asked Dec 04 '22 12:12

the Internet


1 Answers

SELECT
    booking_date,
    COUNT(
        CASE WHEN redemption_date >= booking_date 
        THEN 1 END
    ) AS number_of_reservations
FROM
    Reservations
GROUP BY
    booking_date

Sql Fiddle


Edit:

Based on the updated description, I believe this should give the desired results:

SELECT DISTINCT
    r."booking_date",
    (SELECT COUNT(*) 
     FROM reservations r2 
     WHERE 
         r2."booking_date" <= r."booking_date"
         AND r2."redemption_date" >= r."booking_date"
    ) AS number_of_reservations
FROM
    Reservations r
ORDER BY r."booking_date"

Sql Fiddle

like image 121
Michael Fredrickson Avatar answered Dec 08 '22 04:12

Michael Fredrickson