1

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

Anjali
  • 1,680
  • 4
  • 26
  • 48

2 Answers2

2

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
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
0

This may help:

SELECT  ord.[User Id] ,
        SUM(ISNULL(oc.ItemCount, 0)) Items
FROM    [Order] ord
        LEFT JOIN ( SELECT  [Order ID] ,
                            COUNT(DISTINCT [Item Name]) ItemCount
                    FROM    [OrderChild] oc
                    GROUP BY [Order ID]
                  ) oc ON ord.[ID] = oc.[Order ID]
WHERE   [Order Status] = 'D'
        AND DATEDIFF(MINUTE, [Order Modified Timestamp], GETDATE()) <= 60
GROUP BY ord.[User Id];
Yared
  • 2,206
  • 1
  • 21
  • 30
  • order status is not required here, my question is how many items he delivered in last one hour. – Anjali Sep 29 '16 at 09:13
  • 1
    remove [Order Status] = 'D' filter to achieve what you want from my query. And the result will be items delivered by each users in your [Order] table – Yared Sep 29 '16 at 09:21