title: Get The Previous Billing Cycle Given The Date Record And First Record With A 30-day Interval
body: <p>I'm trying to get the previous billing cycle given two values:</p>
  <ol>
  <li>First recorded date</li>
  <li>Given recorded date</li>
  </ol>
  <p>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:</p>
  <pre><code>|======================|======================|======================|
  | 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           |
  |======================|======================|======================|
  </code></pre>
  <p>Some Notes:</p>
  <ul>
  <li>Year is the same as <code>date_recorded</code> except when month of record is 1</li>
  <li>Dates/day are always the same as the first record except during leap year or if some months have 31 days</li>
  <li>Month is before date if record day is less than first record except during leap year or if some months have 31 days.</li>
  </ul>
  <p>Here's what I got so far:</p>
  <pre><code>SELECT
    first_record,
    record_date,
    (
      CASE
        WHEN date_recorded::DATE &lt;= first_record::DATE THEN first_record_date
        WHEN date_recorded::DATE - INTERVAL '30 day' &lt;= first_recorded_date::DATE THEN first_recorded_date
        WHEN date_recorded::DATE - INTERVAL '30 day' &gt; 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' &gt; first_recorded_date::DATE THEN date_recorded
        ELSE date_recorded::DATE
      END
    )AS previous_billing_cycle
  FROM records_table
  </code></pre>
  <p>I'm having some issues with the leap year or if the day does not match with the <code>first_record_date</code> day specifically. For example, some months do not have day 31.</p>
  <p>Here's a sample in DB-Fiddle: <a href="https://www.db-fiddle.com/f/rZcgNiB9Nrt2PgVm2WnywA/5" rel="nofollow noreferrer">https://www.db-fiddle.com/f/rZcgNiB9Nrt2PgVm2WnywA/5</a></p>