Select a custom date range period to retrieve results from last month day

Question

I have a lot of data with a lot of dates (date begin, date end, date activation etc.). I would like to retrieve those data selecting a specific time range and returning a period date.

I want only results where:
(last month date) <= Date Activation and (last month date) > Date End
+ return the column containing the period

If I select a unique period:

select "client Name","Program" from "database"."schema"."table" 
WHERE "Date Activation" <= '2020-12-31' AND "Date End" > '2020-12-31'

The aim is to retrieve results like this (I want it for all periods in my table):

client Name	Program	period
client 1	program 1	2020/11/30
client 2	program 2	2020/12/31
client 3	program 3	2020/12/31
client 3	program 3	2021/01/31
client 1	program 1	2021/01/31
client 2	program 4	2021/02/28

score 2 · Accepted Answer · answered Mar 12 '21 at 12:05

This should achieve what you want:

-- set parameter to be used as generator "constant" including the start day
-- set start and end dates to match the date range you want to report on
set num_days =  (Select datediff(day, TO_DATE('2020-01-01','YYYY-MM-DD'), current_date()+1));

-- generate all the dates between the start and end dates
with date_list as (
  select
    dateadd(
      day,
      '-' || row_number() over (order by null),
      dateadd(day, '+1', current_date())
    ) as date_val
  from table (generator(rowcount => ($num_days)))
),
-- create a distinct list of month-end dates from the list of dates
month_list as (
  select distinct last_day(date_val) as month_end
  from date_list
)

-- Join the list of month-end dates to your data table
select
cpd.client_name
,cpd.program
,ml.month_end
from month_list ml
inner join client_project_data cpd on cpd.Date_Activation <= ml.month_end and cpd.Date_End > ml.month_end;

-- clean up previously set variable
-- unset num_days;

score 1 · Answer 2 · answered Mar 12 '21 at 10:46

I believe this is how you would get the first two columns:

SELECT DISTINCT "client Name", "Program" 
FROM "database"."schema"."table" 
WHERE "Date Activation" < "Date End" AND LAST_DAY("Date Activation") <> LAST_DAY("Date End")

But with the third one you will have to get creative.

If the difference between "Date Activation" and "Date End" can only be one month, then LAST_DAY("Date Activation") would do it.

But if the difference is bigger, then you will probably need two or more month ends to list. You should form some kind of array of month ends which are between "Date Activation" and "Date End". And the you would need to make separate rows out of such array.

Select a custom date range period to retrieve results from last month day

2 Answers2