Search code examples
sqlsql-servert-sqlsqltransaction

How to retrieve count of records updated in table using SQL Server?

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:

enter image description here

Child table:

enter image description here

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

Solution

  • Assuming Column [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