doodledump
Programmer
- Sep 4, 2024
- 4
- 0
- 1
Hey all,
So I've got a report where users can input a StartDate / EndDate, and expect to get back a list of documents active during that date range. These documents are active for a period of time that is stored in the datebase as ActiveDate / InactiveDate. I need to be able to compare these two ranges so if any of the documents active days fall between the supplied date range, it will be pulled up.
For Example, If the report period is 10/6 - 10/13 then
Doc 1 Active 10/2 - 10/7
Doc 2 Active 10/8 - 10/20
Doc 3 Active 10/5 - 10/14
Should all be pulled up.
The closest formula I could think of would be (ActiveDate TO InactiveDate) IN (StartDate TO EndDate) but it seems IN only works comparing a date to a range, not a range to a range. Is there a more elegant solution than just:
Start IN Active TO Inactive OR
End IN Active TO Inactive OR
Active IN Start TO End OR
Inactive IN Start TO End
I'm using Visual Studio 2022 with Crystal Reports version S36 as a pluggin
So I've got a report where users can input a StartDate / EndDate, and expect to get back a list of documents active during that date range. These documents are active for a period of time that is stored in the datebase as ActiveDate / InactiveDate. I need to be able to compare these two ranges so if any of the documents active days fall between the supplied date range, it will be pulled up.
For Example, If the report period is 10/6 - 10/13 then
Doc 1 Active 10/2 - 10/7
Doc 2 Active 10/8 - 10/20
Doc 3 Active 10/5 - 10/14
Should all be pulled up.
The closest formula I could think of would be (ActiveDate TO InactiveDate) IN (StartDate TO EndDate) but it seems IN only works comparing a date to a range, not a range to a range. Is there a more elegant solution than just:
Start IN Active TO Inactive OR
End IN Active TO Inactive OR
Active IN Start TO End OR
Inactive IN Start TO End
I'm using Visual Studio 2022 with Crystal Reports version S36 as a pluggin
