How to calculate qty days between periods in the same month
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi guys,
I have a challenge to calculate qty days between periods in the same month. See the example below. Could help me, please?
Table 1 | |
Doc number (Key) | Start Date |
1 | 2023-01-01 |
1 | 2023-01-15 |
1 | 2023-01-25 |
Table 2 | |
Doc number (Key) | End Date |
1 | 2023-01-10 |
1 | 2023-01-20 |
1 | 2023-01-30 |
Result | |||
Doc number (Key) | Start Date | End Date | Qty Days |
1 | 2023-01-01 | 2023-01-10 | 9 |
1 | 2023-01-15 | 2023-01-20 | 5 |
1 | 2023-01-25 | 2023-01-30 | 5 |
Result | |
Doc number (Key) | Qty Days |
1 | 19 |
Note: It's possible exists many Start and End dates in the same month or not.
Thanks,
Igor
- Labels:
- Developer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
take a look at this workflow and see if it helps
without a unique key between the two tables, it'll be hard to do a true join.
If we joined on doc number, because it's repeated, we'd come out with 9 records (each combination of start and end) -- however you could have a series of logic to remove the duplicates.
I have one option joining on position, if that's possible for your needs. and the other option joining on doc num but removing the duplicates at the end
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@igorfv posting one way to do this (see below and attached).
As an aside, I would recommend that there is some additional level of detail in the Doc number (Key) field as it can get confusing if you are attempting to join on record position. But, for this use case, the solution gets to the desired end state.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Guys,
Great! It's worked. Thank you for your support.
Igor Valle
