date between another date range

[rohit_bibe07]

col1_date in between [col2_date <=15 and col2_date >=1] 

I want col1_date between col2_date <=15 and col2_date >=1 this range please provide some logic to build

Regards,

Rohit

[Dolphin]

Hi Rohit,

can you clarify your requirements a little bit more?

You have two date columns. Is [col2_date <= 15 and col2_date >= 1] the name of the second or do you want it to be in that range?

And what do you mean by col1_date should be in between that field? Do you want all dates in between that range or do you want to check if col1_date is in between the range.

Let's assume you have a column with a date range and want to check if col1_date is in between that range:

If you don't have the from and to dates you can calculate them like this (with the range looking like '2022/01/01-2022/01/15'):

Date#(SubField(col2_date, '-', 1), 'YYYY/MM/DD') AS FromDate,
Date#(SubField(col2_date, '-', 2), 'YYYY/MM/DD') AS ToDate,

Then you can just do a simple If Condition:

If(col1_date >= FromDate and col1_date <= ToDate, 1, 0) AS isInBetween

If you want to get all dates between the range you could use a mastercalendar and intervalmatch:

AllDates:
Load
    DateKey; //You can calculate all time Fields with this date
Load
    Date(MinDate + IterNo() - 1) AS DateKey
    While (MinDate + IterNo() -1) <= MaxDate;
Load
    Min(FromDate) as MinDate,
    Max(ToDate) as MaxDate
Resident Source;


Left Join(Source) //Based on FromDate and ToDate

DatesBetween:
IntervalMatch(DateKey)
Load Distinct
    FromDate,
    ToDate
Resident Source;

Kind Regards,

Johannes

[rohit_bibe07]

Hi,

Johannes thanks for your answer, my requirement is that i want date range from [col2_date<=15 and col2>=1], col2_date is date column like 'DD-MM-YYYY' and another condition is that i want to filter date of col1_date with respect to above condition means if user select date from col2_date then col1_date will be filtered. so the overall condition is that  "col1_date inbetween  [col2_date<=15 and col2>=1] in this range.

Regards,

Rohit

[Dolphin]

What do you mean with col2_date<=15? Do you want to check if the date is >= 15 (Date(15) would return 1900/01/14 as a date)? Or do you want the Date of 15 day ago compared to col2_date?

If you have col2_date and want to create a range with it you could do something like this:

Load
    col1_date,
    col2_date,
    Date(col2_date -15) & AS FromDate,
    Date(col2_date +1) AS ToDate
    Date(col2_date -15) & Date(col2_date +1) as [col2_date<=15 and col2_date>=1]
From Source

With these new Dates (FromDate & ToDate) you can again just check if col1_date is in between by creating the If condition in the previous answer.

[Dolphin]

Hi again,

I thought of a use case you might mean. Pls Check the script. Is this the right Direction?

Basically you can select Date2 as ReferenceDate and get all Date1s that are in between the ReferenceDates Range (and the same Dim!):

Source:
Load * Inline [
Dim, Date1, Date2
A, 2022/01/15, 2022/01/09
A, 2022/01/27, 2022/02/09
A, 2022/02/02, 2022/02/10
B, 2022/03/09, 2022/07/12
B, 2022/07/09, 2022/07/12
C, 2022/03/04, 2022/03/03
];

NoConcatenate

Fact:
Load
    Dim,
    Date#(Date1, 'YYYY/MM/DD') AS Date1,
    Date#(Date2, 'YYYY/MM/DD') AS Date2
Resident Source;
Drop Table Source;

Time_Temp:
Load
    Dim,
    Date2 AS ReferenceDate,
    Date(Date2 - 15, 'YYYY/MM/DD') AS DateFrom,
    Date(Date2 + 1, 'YYYY/MM/DD') AS DateTo
Resident Fact;

Left Join(Time_Temp)

IntervalMatch(Date1, Dim)
Load
    DateFrom,
    DateTo,
    Dim
Resident Time_Temp;

Time:
Load 
    Date1,
    ReferenceDate,
    DateFrom,
    DateTo,
    Dim as ReferenceDim
Resident Time_Temp;
Drop Table Time_Temp;

Kind Regards,

Johannes