I have a table called billing_cycle and it has customer wise billing pay period information like monthly, weekly, bi-weekly, Quarterly, Yearly. Table Columns : Customer , Frequency, billing_start_date
Example:
Customer , Frequency, billing_start_date
001 , Monthly , 04-Feb-2021
002 , Weekly , 01-Mar-2021
003 , Bi-Weekly , 01-Mar-2021
My requirement is, I want to identify (query) what are the billing periods based on frequency type for a customer between given date range (From and To)
For example, Given date range is 01-Feb-2021 to 30-Oct-2021.
Then out put for customer 001(Monthly frequency) is
Pay_period_start , Pay_period_end
01-Feb-2021 , 28-Feb-2021
01-Mar-2021 , 31-Mar-2021
01-Apr-2021 , 30-Apr-2021 and so on till
01-Oct-2021 to 31-Oct-2021
Output for customer 002 (weekly interval 7 days):
Pay_period_start , Pay_period_end
01-Feb-2021 , 07-Feb-2021
08-Feb-2021 , 14-Feb-2021
15-Feb-2021 , 21-Feb-2021
22-Feb-2021 , 28-Feb-2021
01-Mar-2021 , 07-Mar-2021 and so on till
31-Oct-2021
and similarly for Customer 003 on Bi-weekly basis(15 days).
CodePudding user response：
Get the average time between pay peroids. Create a case statement
case when timediff = 7 then weekly when timediff = 14 then biweekly else monthly end
you can fill in the other values for quarterly yearly and such
CodePudding user response：
Here's Oracle code; see if it helps.
Setting date format (just to see what is what; you don't have to do that):
SQL> alter session set nls_date_Format = 'dd.mm.yyyy';
Session altered.
Here we go (read comments within code):
SQL> with
2 -- This is your sample table
3 customer (customer, frequency, billing_start_date) as
4 (select '001', 'Monthly' , date '2021-04-02' from dual union all
5 select '002', 'Weekly' , date '2021-03-01' from dual union all
6 select '003', 'Bi-Weekly', date '2021-03-01' from dual
7 ),
8 -- Date range, as you stated
9 date_range (start_date, end_date) as
10 (select date '2021-02-01', date '2021-10-30' from dual
11 )
12 -- Billing periods
13 select
14 c.customer,
15 --
16 case when c.frequency = 'Monthly' then add_months(d.start_date, column_value - 1)
17 when c.frequency = 'Weekly' then d.start_date ( 7 * (column_value - 1))
18 when c.frequency = 'Bi-Weekly' then d.start_date (14 * (column_value - 1))
19 end as pay_period_start,
20 --
21 case when c.frequency = 'Monthly' then add_months(d.start_date, column_value - 0) - 1
22 when c.frequency = 'Weekly' then d.start_date ( 7 * (column_value - 0)) - 1
23 when c.frequency = 'Bi-Weekly' then d.start_date (14 * (column_value - 0)) - 1
24 end as pay_period_end
25 from customer c cross join date_range d
26 cross join table(cast(multiset(select level
27 from dual
28 connect by level <= case when c.frequency = 'Monthly' then months_between(d.end_date, d.start_date)
29 when c.frequency = 'Weekly' then (d.end_date - d.start_date) / 7
30 when c.frequency = 'Bi-Weekly' then (d.end_date - d.start_date) / 14
31 end 1
32 ) as sys.odcinumberlist))
33 order by c.customer, pay_period_start;
Result:
CUSTOMER PAY_PERIOD_START PAY_PERIOD_END
---------- -------------------- --------------------
001 01.02.2021 28.02.2021
001 01.03.2021 31.03.2021
001 01.04.2021 30.04.2021
001 01.05.2021 31.05.2021
001 01.06.2021 30.06.2021
001 01.07.2021 31.07.2021
001 01.08.2021 31.08.2021
001 01.09.2021 30.09.2021
001 01.10.2021 31.10.2021
002 01.02.2021 07.02.2021
002 08.02.2021 14.02.2021
002 15.02.2021 21.02.2021
002 22.02.2021 28.02.2021
002 01.03.2021 07.03.2021
002 08.03.2021 14.03.2021
002 15.03.2021 21.03.2021
002 22.03.2021 28.03.2021
002 29.03.2021 04.04.2021
002 05.04.2021 11.04.2021
002 12.04.2021 18.04.2021
002 19.04.2021 25.04.2021
002 26.04.2021 02.05.2021
002 03.05.2021 09.05.2021
002 10.05.2021 16.05.2021
002 17.05.2021 23.05.2021
002 24.05.2021 30.05.2021
002 31.05.2021 06.06.2021
002 07.06.2021 13.06.2021
002 14.06.2021 20.06.2021
002 21.06.2021 27.06.2021
002 28.06.2021 04.07.2021
002 05.07.2021 11.07.2021
002 12.07.2021 18.07.2021
002 19.07.2021 25.07.2021
002 26.07.2021 01.08.2021
002 02.08.2021 08.08.2021
002 09.08.2021 15.08.2021
002 16.08.2021 22.08.2021
002 23.08.2021 29.08.2021
002 30.08.2021 05.09.2021
002 06.09.2021 12.09.2021
002 13.09.2021 19.09.2021
002 20.09.2021 26.09.2021
002 27.09.2021 03.10.2021
002 04.10.2021 10.10.2021
002 11.10.2021 17.10.2021
002 18.10.2021 24.10.2021
002 25.10.2021 31.10.2021
003 01.02.2021 14.02.2021
003 15.02.2021 28.02.2021
003 01.03.2021 14.03.2021
003 15.03.2021 28.03.2021
003 29.03.2021 11.04.2021
003 12.04.2021 25.04.2021
003 26.04.2021 09.05.2021
003 10.05.2021 23.05.2021
003 24.05.2021 06.06.2021
003 07.06.2021 20.06.2021
003 21.06.2021 04.07.2021
003 05.07.2021 18.07.2021
003 19.07.2021 01.08.2021
003 02.08.2021 15.08.2021
003 16.08.2021 29.08.2021
003 30.08.2021 12.09.2021
003 13.09.2021 26.09.2021
003 27.09.2021 10.10.2021
003 11.10.2021 24.10.2021
003 25.10.2021 07.11.2021
68 rows selected.
SQL>
If you'd actually want to set periods regarding customer.billing_start_date, then all references to date_range.start_date should be modified to billing_start_date.
CodePudding user response：
Here is a solution for Postgres.
There is a slight difference compared to your expected output: the pay_period_start is calculated to not start before billing_start_date:
select t.customer,
case
when g.nr = 1 then t.billing_start_date
else g.dt::date
end as pay_period_start,
case t.frequency
when 'Weekly' then (g.dt interval '1 week' - interval '1 day')::date
when 'Bi-Weekly' then (g.dt interval '2 week' - interval '1 day')::date
else (g.dt interval '1 month' - interval '1 day')::date
end as pay_period_end
from the_table t
cross join generate_series(date_trunc('month', t.billing_start_date), date '2021-10-31',
case t.frequency
when 'Weekly' then interval '1 week'
when 'Bi-Weekly' then interval '2 week'
else interval '1 month'
end
) with ordinality as g(dt,nr)
order by t.customer, pay_period_start
If you indeed want pay_period_start to start on 2021-02-01 regardless of the actual billing_start_date you need to change the start value for generate_series() and the CASE expression for pay_period_start can also be simplified
CodePudding user response：
In Oracle, you can use:
WITH date_range (range_start, range_end) AS (
SELECT DATE '2021-02-01', DATE '2021-10-01' FROM DUAL
),
periods (customer, frequency, period_start, range_end) AS (
SELECT t.customer,
t.frequency,
CASE t.frequency
WHEN 'Monthly'
THEN ADD_MONTHS(
billing_start_date,
GREATEST(TRUNC(MONTHS_BETWEEN(range_start, billing_start_date)), 0)
)
WHEN 'Bi-Weekly'
THEN billing_start_date 14 * GREATEST(TRUNC((range_start - billing_start_date)/14), 0)
WHEN 'Weekly'
THEN billing_start_date 7 * GREATEST(TRUNC((range_start - billing_start_date)/7), 0)
END,
d.range_end
FROM table_name t
CROSS JOIN date_range d
WHERE t.billing_start_date <= d.range_end
UNION ALL
SELECT customer,
frequency,
CASE frequency
WHEN 'Monthly' THEN ADD_MONTHS(period_start, 1)
WHEN 'Bi-Weekly' THEN period_start 14
WHEN 'Weekly' THEN period_start 7
END,
range_end
FROM periods
WHERE period_start < range_end
)
SEARCH DEPTH FIRST BY customer SET order_rn
SELECT customer,
frequency,
period_start,
CASE frequency
WHEN 'Monthly' THEN ADD_MONTHS(period_start, 1)
WHEN 'Bi-Weekly' THEN period_start 14
WHEN 'Weekly' THEN period_start 7
END - 1 AS period_end
FROM periods
WHERE period_start <= range_end;
Which, for the sample data:
CREATE TABLE table_name (Customer , Frequency, billing_start_date) AS
SELECT '001', 'Monthly', DATE '2021-02-04' FROM DUAL UNION ALL
SELECT '002', 'Weekly', DATE '2021-03-01' FROM DUAL UNION ALL
SELECT '003', 'Bi-Weekly', DATE '2020-03-05' FROM DUAL;
Outputs:
CUSTOMER FREQUENCY PERIOD_START PERIOD_END 001 Monthly 2021-02-04 00:00:00 2021-03-03 00:00:00 001 Monthly 2021-03-04 00:00:00 2021-04-03 00:00:00 001 Monthly 2021-04-04 00:00:00 2021-05-03 00:00:00 001 Monthly 2021-05-04 00:00:00 2021-06-03 00:00:00 001 Monthly 2021-06-04 00:00:00 2021-07-03 00:00:00 001 Monthly 2021-07-04 00:00:00 2021-08-03 00:00:00 001 Monthly 2021-08-04 00:00:00 2021-09-03 00:00:00 001 Monthly 2021-09-04 00:00:00 2021-10-03 00:00:00 002 Weekly 2021-03-01 00:00:00 2021-03-07 00:00:00 002 Weekly 2021-03-08 00:00:00 2021-03-14 00:00:00 002 Weekly 2021-03-15 00:00:00 2021-03-21 00:00:00 ... ... ... ... 002 Weekly 2021-09-13 00:00:00 2021-09-19 00:00:00 002 Weekly 2021-09-20 00:00:00 2021-09-26 00:00:00 002 Weekly 2021-09-27 00:00:00 2021-10-03 00:00:00 003 Bi-Weekly 2021-01-21 00:00:00 2021-02-03 00:00:00 003 Bi-Weekly 2021-02-04 00:00:00 2021-02-17 00:00:00 003 Bi-Weekly 2021-02-18 00:00:00 2021-03-03 00:00:00 ... ... ... ... 003 Bi-Weekly 2021-09-02 00:00:00 2021-09-15 00:00:00 003 Bi-Weekly 2021-09-16 00:00:00 2021-09-29 00:00:00 003 Bi-Weekly 2021-09-30 00:00:00 2021-10-13 00:00:00
db<>fiddle here