My task is to show the count of items delivered by the shopkeeper[user Id] for the last one hour. Below are the table designs [Order parent table][Order child table]
Order parent table:
Items are scanned by using barcode reader, once it is scanned it is updated in database, once it fulfilled the order it updated the modified stamp.
How to approach this scenario? Do I need to change the database design or can I use the current design to get the result set?
My output: In front end,
Last one hour count = 10
[Item Placed] will provide the count of items will be delivered .
SELECT SUM([Item Placed]) FROM [Order parent table] OP JOIN [Order child table] OC ON OP.ID=OC.OrderID WHERE OP.OrderStatus='D' AND DATEDIFF(MINUTE, OP.[Order Modified Timestamp], GETDATE()) <= 60 AND [user Id] = 1 --mention the user id here --AND OC.[Item Status]='D' --Apply this filter with valid status if you require child table stus