Hello all;
I have an interesting problem with the following sample data. Kindly note, there is no actual calendar in the system that contains the data, hence, I decided to quickly make a sample month's calendar using recursion to see if it can be used to determine the logic. Leap year is negligible. Please see my sample data so far.
with tm (info_month) as
(
select to_date('01/31/2021', 'MM/DD/YYYY') from dual
union all
select to_date('02/28/2021', 'MM/DD/YYYY') from dual
union all
select to_date('03/31/2021', 'MM/DD/YYYY') from dual
union all
select to_date('04/30/2021', 'MM/DD/YYYY') from dual
union all
select to_date('05/31/2021', 'MM/DD/YYYY') from dual
union all
select to_date('06/30/2021', 'MM/DD/YYYY') from dual
union all
select to_date('07/31/2021', 'MM/DD/YYYY') from dual
union all
select to_date('08/31/2021', 'MM/DD/YYYY') from dual
union all
select to_date('09/30/2021', 'MM/DD/YYYY') from dual
union all
select to_date('10/31/2021', 'MM/DD/YYYY') from dual
union all
select to_date('11/30/2021', 'MM/DD/YYYY') from dual
union all
select to_date('12/31/2021', 'MM/DD/YYYY') from dual
)
,t_details_info (id, loc_info, st_dt, end_dt) as
(
select 111, to_date('02/02/2019', 'MM/DD/YYYY'), 'New York', to_date('04/04/2022', 'MM/DD/YYYY') from dual
union all
select 222, to_date('03/01/2020', 'MM/DD/YYYY'), 'New Orleans', to_date('09/22/2021', 'MM/DD/YYYY') from dual
union all
select 333, to_date('04/04/2021', 'MM/DD/YYYY'), 'Paris', to_date('03/29/2022', 'MM/DD/YYYY') from dual
union all
select 777, to_date('01/01/2022', 'MM/DD/YYYY'), 'Boston', to_date('02/20/2022', 'MM/DD/YYYY') from dual
union all
select 444, to_date('11/16/2020', 'MM/DD/YYYY'), 'Berlin', null from dual
union all
select 345, to_date('11/13/2021', 'MM/DD/YYYY'), 'London', null from dual
)
,t_describe(id, pos_info) as
(
select 111, 'out' from dual
union all
select 222, 'out' from dual
union all
select 333, 'out' from dual
union all
select 777, 'out' from dual
union all
select 444, 'in' from dual
union all
select 345, 'in' from dual
)
requirement spec
The expected result is gotten by
-
first starting at the associated st_dt of the id and checking to see if there is an end_dt, if there is an end_dt, then for each months between the st_dt and end_dt, check to see if the end_dt > last_day of each of the months , if it is then count it as 1. This is how the results are gotten for ID=333. For feb 2020, which is a leap year and hence has 29 day, that is negligible
-
If there is not associated end_dt as shown in ID=444, then utilize today's date as the end_dt and then for each months between st_dt and today's date, check to see if the today's date > last_day of each of the months, if it is then count it as 1. This is how the results are gotten for ID=444
expected result shown below
ID each_monthend_cnt Month_given
444 1 Nov 2020
444 1 Dec 2020
444 1 Jan 2021
444 1 Feb 2021
444 1 Mar 2021
444 1 Apr 2021
444 1 May 2021
444 1 Jun 2021
444 1 Jul 2021
444 1 Aug 2021
444 1 Sept 2021
444 1 Oct 2021
444 1 Nov 2021
444 1 Dec 2021
444 1 Jan 2022
444 1 Feb 2022
444 1 Mar 2022
333 1 Apr 2021
333 1 May 2021
333 1 Jun 2021
333 1 Jul 2021
333 1 Aug 2021
333 1 Sep 2021
333 1 Oct 2021
333 1 Nov 2021
333 1 Dec 2021
333 1 Jan 2022
333 1 Feb 2022
Thanks for your time. All help is appreciated. Kindly let me know the solution can be done without a calendar. I have a feeling it cannot to be done if you do not have a calendar but I would like to validate the assumption with experts first.
Thanks again