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?