Query to count number of days

I have a table (image attached)
And I need to get from it the latest StageNum and all the Approver Names in this stage
and quantity of days of the latest stage.
I need the output like this: “StageNum = 5, Approver Names: Name9, Name10, Days - 1”
(have no idea how to calculate number of days )

I tried to get the max stage num, but it’s not filtering correctly.
I’d be grateful for any advice how to fix it.

qbds.addSelectionField(fieldNum(MyTable, Approver),SelectionField::Max);
qbds.addGroupByField(fieldNum(MyTable, StageNum));
filter = query.addHavingFilter(qbds, fieldStr(MyTable, StageNum),AggregateFunction::Max);


I think you’ll need to do it in several steps. What do you need to do with the query?

If you can do it in X++, the easiest way may be getting the highest StageNum by one query and then the rest of data in a while select.

If you an a Query object for some reason, it’ll be more difficult. I think you’ll end up with several views and computed columns.

For example, the first view may give you data for each StageNumber. It’ll have a computed column for the number of days, which will logically (not preciselly in code) do this: max(CreatedDateTime) - min(CreatedDateTime). Another computed column will give you a concatenated list of approvers. I suggest this approach: T-Sql concatenate rows using STUFF and For XML PATH.
Then create another simple view returning the highest stage number. Join these two views in a query and you’re done. I would personally look for another solution before doing all this work. For example, a PowerBI report with CONCATENATEX would be an elegant solution, IMHO. But of course, I don’t know your requirements.