Get The Previous Billing Cycle Given The Date Record And First Record With A 30-day Interval

Question

I'm trying to get the previous billing cycle given two values:

1. First recorded date
2. Given recorded date

How would I get the previous billing cycle given that the previous billing cycle should be within the 30 day interval of the first recorded date? Here's a table to give you a view what I'm talking about:

|======================|======================|======================|
| first_record_date    | date_recorded        | prev_bill_cycle      |
|======================|======================|======================|
| 2021-06-30           | 2021-11-15           | 2021-10-30           |
| 2021-06-15           | 2021-09-16           | 2021-09-15           |
| 2021-09-15           | 2021-09-16           | 2021-09-15           |
| 2021-06-30           | 2022-01-05           | 2021-12-30           |
| 2021-11-30           | 2022-11-31           | 2021-11-30           |
| 2020-02-29           | 2022-01-31           | 2022-01-29           |
| 2020-02-29           | 2022-01-31           | 2022-01-29           |
| 2020-02-29           | 2022-02-28           | 2022-02-28           |
| 2021-01-05           | 2022-01-03           | 2021-12-05           |
| 2021-01-05           | 2022-01-07           | 2022-01-05           |
|======================|======================|======================|

Some Notes:

• Year is the same as date_recorded except when month of record is 1
• Dates/day are always the same as the first record except during leap year or if some months have 31 days
• Month is before date if record day is less than first record except during leap year or if some months have 31 days.

Here's what I got so far:

SELECT
  first_record,
  record_date,
  (
    CASE
      WHEN date_recorded::DATE <= first_record::DATE THEN first_record_date
      WHEN date_recorded::DATE - INTERVAL '30 day' <= first_recorded_date::DATE THEN first_recorded_date
      WHEN date_recorded::DATE - INTERVAL '30 day' > CONCAT(TO_CHAR(date_recorded,'YYYY-MM-'),TO_CHAR(first_recorded_date,'DD'))::DATE
        THEN CONCAT(TO_CHAR(date_recorded,'YYYY-MM-'),TO_CHAR(first_recorded_date,'DD'))::DATE
      WHEN date_recorded::DATE - INTERVAL '30 day' > first_recorded_date::DATE THEN date_recorded
      ELSE date_recorded::DATE
    END
  )AS previous_billing_cycle
FROM records_table

I'm having some issues with the leap year or if the day does not match with the first_record_date day specifically. For example, some months do not have day 31.

Here's a sample in DB-Fiddle: https://www.db-fiddle.com/f/rZcgNiB9Nrt2PgVm2WnywA/5

Answer 1

I realized that using CASE/IF statements will not work, because the calcualation does not match necessarily for 30 interval days since the first_record_date.

Here's the code:

SELECT
  first_record_date,
  date_recorded,
  DATE_PART('day', (first_record_date::DATE + INTERVAL '90 day') - first_record_date::DATE) AS first_and_next_cycle_date,
  date_recorded::DATE - first_record_date::DATE AS date_recorded_minus_first_record,
  (date_recorded::DATE - first_record_date::DATE)::INTEGER / 30 AS multiplier,
  DATE_PART('day', (first_record_date::DATE + INTERVAL '30 day' * ((date_recorded::DATE - first_record_date::DATE)::INTEGER / 30)) - first_record_date::DATE) AS additional_days,
  first_record_date::DATE + INTERVAL '1 day' * DATE_PART('day', (first_record_date::DATE + INTERVAL '30 day' * ((date_recorded::DATE - first_record_date::DATE)::INTEGER / 30)) - first_record_date::DATE) AS previous_billing_cycle,
  first_record_date::DATE + INTERVAL '1 day' * DATE_PART('day', (first_record_date::DATE + INTERVAL '30 day' * (((date_recorded::DATE - first_record_date::DATE)::INTEGER / 30) + 1)) - first_record_date::DATE) AS end_billing_cycle
FROM records

Here's the answer in DB fiddle: https://www.db-fiddle.com/f/rZcgNiB9Nrt2PgVm2WnywA/9