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)