Skip to Content
0
Jun 08, 2022 at 09:23 AM

SAP business one - how to use CrossJoin with aggregation

43 Views Last edit Jun 08, 2022 at 09:27 AM 3 rev

I need to get the count of the Sales orders by date range and the total sum of Quantity property in DocumentLines related to exact Sales order. I can get the count of Sales order by date, but I can't get the total sum of Quantity for that Sales order in one query. For now I am using this Query:

image.png

image.png

So we want to merge this two queries and get the expected result as following:

{
"value": [
{
"@odata.id": null,
"CreationDate": "2022-05-12",
"Quantity":"200",
"count": 5
},
{
"@odata.id": null,
"CreationDate": "2022-05-19",
"Quantity":"300",
"count": 10
},
{
"@odata.id": null,
"Quantity":"400",
"CreationDate": "2022-05-26",
"count": 15
},
}

Does anyone familiar with this kind of issue.

Thanks in advance.

Attachments

image.png (23.7 kB)
image.png (38.9 kB)