Home > Software design >  MS SQL SUM() using multiple tables and adding only last recorded value
MS SQL SUM() using multiple tables and adding only last recorded value

Time:11-05

I am trying to add hours quoted per machine. I have to look at three tables to get the correct and most recent data. I was able to get a list of the hours I need to add.

However, I tried all kinds of ways to use a SUM() function but it always gives me aggregate errors.

Here is the SQL Code:

SELECT 
(SELECT TOP 1 change.hours 
    FROM change WHERE change.id = part.id
    ORDER BY change.timeStamp DESC) as 'Hours'

FROM change 
INNER JOIN part ON (part.id = change.id)
INNER JOIN  completed ON (part.id = completed.id) 

WHERE part.id NOT IN (SELECT completed.id FROM completed WHERE completed.completed = 1) 
and (SELECT TOP 1 change.machine FROM change WHERE part.id = change.id ORDER BY change.timeStamp DESC ) = :machine

GROUP BY change.id, part.id

I basically need to single-cell result that will add all the hours per machine. The result will be displayed on GUI.

RESULT SHOULD BE Machine 1 has to be 12 hours Machine 2 has to be 18 hours Machine 3 has to be 18 hours

TABLES

part

id number description job
14 40023-10-100-10-03 Base 40023
15 40023-10-200-10-03 Base 40023
16 40024-10-100-10-01 sensor bracket 40024
17 40024-10-100-10-02 Side 40024
18 40025-10-100-10-01 Conveyor hold 40025
19 40025-10-200-00-01 Part 40025
20 40026-10-400-00-01 Motor Mount 40026
21 40026-10-200-10-10 Delta arms 40026
22 40023-10-200-10-03 Base 40023

change

id qty hours machine operator startTime stopTime completed date timeStamp
14 0 0 2 2 NULL NULL False NULL 2021-10-28 00:00:00.000
15 0 0 4 3 NULL NULL False NULL 2021-10-28 11:01:41.427
19 0 0 3 1 NULL NULL False NULL 2021-10-28 11:10:50.730
18 0 0 2 3 NULL NULL False NULL 2021-10-28 11:13:46.213
16 3 2.5 2 2 NULL NULL False 2021-10-27 2021-10-28 13:41:12.393
16 3 2.5 2 2 NULL NULL False 2021-10-27 2021-10-28 13:41:12.393
15 1 9 3 3 NULL NULL True 2021-10-29 2021-10-28 21:38:44.883
14 0 0 1 1 NULL NULL False NULL 2021-11-01 10:36:43.223
14 0 0 1 1 NULL NULL False NULL 2021-11-01 10:37:47.153
16 1 0.5 2 2 NULL NULL False 2021-11-01 2021-11-01 11:12:06.840
21 0 0 1 1 NULL NULL False NULL 2021-11-01 11:45:30.050
20 0 0 2 3 NULL NULL False NULL 2021-11-10 10:44:00.000
23 0 0 0 0 NULL NULL True 2021-11-02 2021-11-02 16:26:18.583
16 1 1 2 2 NULL NULL False 2021-11-01 2021-11-01 11:03:44.160
17 0 0 2 2 NULL NULL False NULL 2021-10-28 11:25:03.967
17 0 0 1 1 NULL NULL False NULL 2021-11-01 10:40:36.850
17 0 0 1 1 NULL NULL False NULL 2021-11-01 10:42:56.350
22 0 0 3 2 NULL NULL False NULL 2021-11-02 11:58:08.360
17 0 0 1 2 NULL NULL False NULL 2021-11-01 10:43:44.273
14 0 0 1 1 NULL NULL False NULL 2021-11-01 10:44:23.440
14 0 0 1 1 NULL NULL False NULL 2021-11-02 12:57:06.810

change

id hours qty machine operator notes rush timeStamp
14 2 3 2 1 False 2021-10-28 10:48:54.910
15 10 1 3 2 False 2021-10-28 10:49:47.643
16 7 10 2 3 Need material True 2021-10-28 10:50:33.880
17 4 2 1 1 False 2021-10-28 00:00:00.000
18 5 1 2 2 False 2021-10-28 10:53:15.470
19 8 3 3 3 False 2021-10-28 11:10:50.573
14 3 4 1 1 waiting for mills False 2021-10-29 08:12:00.000
17 4 2 1 1 True 2021-11-01 10:40:36.707
17 4 2 1 1 True 2021-11-01 10:42:56.150
16 8 10 2 3 Need material False 2021-11-01 10:43:29.930
17 4 2 1 2 False 2021-11-01 10:43:44.047
14 3 4 1 1 False 2021-11-01 10:44:23.317
20 2 4 2 3 False 2021-11-01 11:44:10.257
21 5 3 1 1 Need material True 2021-11-01 11:45:29.927
22 10 1 3 2 False 2021-11-02 11:58:08.220
14 3 4 1 1 True 2021-11-02 12:57:06.683
14 4 2 1 1 waiting for bits False 2021-10-29 00:00:00.000
14 3 4 1 1 wrong mills came. Need to order another ones False 2021-11-01 10:36:42.997
14 3 4 1 1 wrong mills came. Need to order another ones False 2021-11-01 10:37:46.983

CodePudding user response:

Well, I cannot understand how do you calculate the expected results and I am sure your query can be improved a lot (if you explain better your requirements, I can help you to improve it). Meanwhile, this query returns what you want:

select machine, sum(Hours) Hours from (
SELECT change.machine,
(SELECT TOP 1 change.hours 
    FROM change WHERE change.id = part.id
    ORDER BY change.timeStamp DESC) as 'Hours'

FROM change 
INNER JOIN part ON (part.id = change.id)
INNER JOIN  completed ON (part.id = completed.id) 

WHERE part.id NOT IN (SELECT completed.id FROM completed WHERE completed.completed = 1) 
GROUP BY change.id, part.id, change.machine
) as a 
group by machine

It returns:

machine Hours
1 12
2 18
3 18

DBFiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=80ab33a349388896af3ffcb8954c56c9

CodePudding user response:

That's great work, Carlos.

I was able to add to your code to get the results I need

There is input of which machine is displaying. :machine is the input.

SELECT ISNULL((
SELECT sum(Hours)
FROM (SELECT 
        (SELECT TOP 1 change.hours 
            FROM change WHERE change.id = part.id
            ORDER BY change.timeStamp DESC) as 'Hours'
    FROM change 
        INNER JOIN part ON (part.id = change.id)
        INNER JOIN  completed ON (part.id = completed.id) 
    
    WHERE part.id NOT IN (SELECT completed.id FROM completed WHERE completed.completed = 1) 
    and (SELECT TOP 1 change.machine FROM change WHERE part.id = change.id ORDER BY change.timeStamp DESC) = :machine
    
    GROUP BY change.id, part.id) as a), 0)
  • Related