How can I sum up the days where a target date is between 2 date fields. The summation should happen per day. A more detailed description can be found in the details.

Question

I have a “Case Table” table. This has 2 columns “Case ID” and “Target date”. I also have a 2 table “Horizon” with 3 columns “Case ID”, “Start date” and “End date”. The relationship between the tables is as follows: “Case Table” N : 1 "Horizon".

The primary key is “Case ID”

Target with the help of a PQL:

I want to build a graph where on the X axis are the days and on the Y axis are the number of target dates.

If the “target date” from the “Case Table” is between the “start date” (exclusive) and “end date” (inclusive) from the “Horizon” table, it should be counted.

Example (The case ID of both tables is the same “Case ID” = 1):

Target date = 20.09.2024

Start date = 18.09.2024

End date = 22.09.2024

Result:

X axis: 17.09.2024; Y axis: 0

X axis: 18.09.2024; Y axis: 0

X axis: 19.09.2024; Y axis: 1

X axis: 20.09.2024; Y axis: 1

X axis: 21.09.2024; Y axis: 1

X axis: 22.09.2024; Y axis: 1

X axis: 23.09.2024; Y axis: 0

A 1 is added for all days where the target date lies between the start date (Exclusive) and the end date (Inclusive). This now happens for each row in the “Case Table”

If I had a second row with the same values as in the example above, it would continue like this:

CaseID = 2

Target date = 20.09.2024

Start date = 18.09.2024

End date = 22.09.2024

Result:

X axis: 17.09.2024; Y axis: 0

X axis: 18.09.2024; Y axis: 0

X axis: 19/09/2024; Y axis: 2 <- (1 from CaseID = 1 + 1 from CaseID = 2)

X axis: 20/09/2024; Y axis: 2 <- (1 from CaseID = 1 + 1 from CaseID = 2)

X axis: 21/09/2024; Y axis: 2 <- (1 from CaseID = 1 + 1 from CaseID = 2)

X axis: 22/09/2024; Y axis: 2 <- (1 from CaseID = 1 + 1 from CaseID = 2)

X axis: 23.09.2024; Y axis: 0

How do I write the PQL for this? Is it even possible?

Page 1 / 1

I think you should bring the “Start date” and “End date” columns from the Horizon table to your case table using BIND function.

Then you can use a combination of Sum and CASE WHEN Start date<target_date<= End date I believe. But here I didn't get the logic you are trying to achive a 100% maybe the date logic gets more complex than that. But BIND is definitely needed in your scenario,

a general advice for any complex PQL logic. Try first in a table to figure out the PQL (one column the that provides you the correct values and afterwards focus on how to make you visual work.

Reply

manuel.wetze · Answer

I think you should bring the “Start date” and “End date” columns from the Horizon table to your case table using BIND function.

Then you can use a combination of Sum and CASE WHEN Start date<target_date<= End date I believe. But here I didn't get the logic you are trying to achive a 100% maybe the date logic gets more complex than that. But BIND is definitely needed in your scenario,

a general advice for any complex PQL logic. Try first in a table to figure out the PQL (one column the that provides you the correct values and afterwards focus on how to make you visual work.

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded