I have a table for my customer orders and it has a structure like :
{ id , customername, orderid, type, count, productid, time}
Let me explain my scenario: When my customer creates an order :
{ id=1 , customername="mycustomername", orderid="1001", type="mycustomername.created", count=2, productid=123, time=19.08.2022 13:44:56}
new update of my customer
{ id=1 , customername="mycustomername", orderid="1001", type="mycustomername.created", count=5, productid=123, time=19.08.2022 13:44:56}
my azure event trigger is listening to these changes and sending a notification to my marketplace.
5 minutes later or 10 minutes later depending on marketplace network traffic they are updating with a new row :
{ id=2 , customername="marketplacename", orderid="1001", type="marketplacename.updated", count=4, productid=123, time=19.08.2022 14:44:56}
5 minutes later :
{ id=2 , customername="marketplacename", orderid="1001", type="marketplacename.updated", count=3, productid=123, time=19.08.2022 14:49:56}
because they are making physical packages to the target`s address.
In Summary,
I am trying to measure the time differences between last updates of mycustomer and last updates of my market place. I read the below article and tried to solve it but I couldn`t (https://cosmosdb.github.io/labs/dotnet/labs/03-querying_in_azure_cosmosdb.html)
SELECT top 1 c.orderid, c.type, DateTimeDiff("hh", c.time, m.time) AS DifferenceInHours
FROM c
JOIN (SELECT c.orderid FROM c where c.type = "mycustomername.created") m
WHERE m.orderid =c.orderid and c.type="marketplacename.updated"