Microsoft Dynamics 365 | Integration, Dataverse...

Unanswered

D365 FO : Date range sorting logic X++

(0) Share

Report

Posted on by Mayowa

Hi Everyone,

I need some help with the following problem

Items	Start date	End date	Amount
A	07-Jun-21	31-Dec-21	23000
B	1-Jan-22	31-Mar-22	33000
C	07-Jun-21	31-Mar-22	1000
D	07-Jun-21	30-Sep-21	5000
E	01-Oct-21	31-Mar-22	10000

I need help with the logic to aggregate the table above into the following

29000	07-Jun-21	30-Sep-21	23 +5 + 1
34000	01-Oct-21	31-Dec-21	23 +10 +1
44000	1-Jan-22	31-Mar-22	33 +10 +1

So basically , get the date ranges within these records and sum all the values that fall within those date ranges.

Any suggestions would be most appreciated.

Thanks

Mayowa

I have the same question (0)

All responses (8)

Answers (0)

Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

Because it's about X++, it's clearly not about D365 in general and therefore it doesn't belong to this forum. Please use an F&O forum next time, such as Finance forum. When you put your question to a forum about F&O, you won't also need to prefix thread titles with "D365 FO".

How do you determine the ranges to group by?

Was this reply helpful? Yes No

Mayowa

13 on at

Like (0)

Report

Morning Martin,

Your comment on the correct forum to use is duly noted..Thanks

If you look at the table, there is a min date of 7-Jun-21 and a max date of 31-Dec-22 Between the start and end date columns.

So the ranges are determined by the dates between the min and max dates

A	07-Jun-21	31-Dec-21
B	1-Jan-22	31-Mar-22
C	07-Jun-21	31-Mar-22
D	07-Jun-21	30-Sep-21
E	01-Oct-21	31-Mar-22

So between Jun 21 and Dec 22, we have

Jun 21 - sept 21

oct 21- dec 21

Jan 22- mar 22

Thanks

Mayowa

Was this reply helpful? Yes No

Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

Can please explicitly confirm that you want to ignore some dates?

For example, the first range (7-Jun-21 - 31-Dec-21) covers 30th September 2021, but none of your new ranges does.

Was this reply helpful? Yes No
Mayowa 13 on at

Like (0)

Report

Hi Martin,

Between June and Dec 21, we have sept and oct, which is why the dates are grouped as

June to Sept, Oct to dec.

Let me try and further explain.

The min date is June 21 and the Max Date is Mar 22

From the records on the table, there is sept 21, oct 21, dec 21,Jan 22 between June 21 and Mar 22.

so the grouping is:

June 21 - sept 21

Oct 21 - dec 21

Jan 22 - Mar 22

Thanks

Mayowa

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

Well, it isn't the confirmation that I hoped for, because it doesn't address my question.

The original ranges covered dates that aren't covered anymore, such as 30th September 2021. In other words, the data isn't just grouped - it filter out some dates. Is it intentional or a bug in your design?

Was this reply helpful? Yes No
Mayowa 13 on at

Like (0)

Report

Yes It is intentional, I was trying to explain why in my previous post.

The new ranges does cover 30th sept, Only ranges that are already covered are filtered out.

Using your example to illustrate what I mean..

Old Range New Range

7 JUN 21 - 31 DEC 21 7 JUN 21 - 30 SEPT 21

1 OCT 21 - 31 DEC 21

We are taking the entire table as a single set of records..

I hope its clearer now

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report
Okay, if you're confirming that you want to exclude some date ranges, then I'll ignore the statement "The new ranges does cover 30th sept" (because they doesn't) and I take into account that you want to filter out data, not just to group them.

What exactly do you need from us? Just the grouping? Or do you need something about the range calculation logic as well?

I don't know where you have the logic calculating ranges, but in general, I don't think you simply do it in a select statement. I see two ways:

Run one select for each group; summarize values and filter by from date and to date. Put the result to a temporary table.

Create a view with a computed column which will identify the range where each of the records belong (if it's possible). Then you can group by this computed column.

Was this reply helpful? Yes No

Mayowa

13 on at

Like (0)

Report

, HI Martin,

Thank you for your suggestion, Although I feel like you don't quite understand what I am saying, Most likely because I am terrible at explaining things.

Please let me try once more to explain

Pay element

Start Date

End Date

Annual Amount

Base Pay

07-Jun-21

31-Dec-21

23000

Base Pay

1-Jan-22

31-Mar-22

33000

Allowance A

07-Jun-21

31-Mar-22

1000

Allowance B

07-Jun-21

30-Sep-21

5000

Allowance C

01-Oct-21

31-Mar-22

10000

I am required to calc "Tax" for this employee between 7th Jun 21 and 31 Mar 22

So I would need to get the prorated value for his total renumeration between the date ranges that fall within this period.

Simply put, I would get the prorated amount for his total pay between 07-Jun-21 and 30-Sep-21 which is 29000, (23000 + 5000 + 1000), Because the dates overlap.. and the calc Tax on that prorated Value... and then ill get I would get the prorated amount for his total renumeration between 01-Oct-21 and 31-Dec-21 which is 34000, (23000 + 10000+1000),...and so on