for others wanting to help, here is the DDLDECLARE @tblLineItems TABLE
(
[LineItemID] INT ,
[JobID] INT
);
DECLARE @tblCostCodes TABLE
(
[costCodeID] INT ,
[costCode] VARCHAR(25)
);
DECLARE @tblCostCodeSubCategories TABLE
(
[CostCodeSubCategoryID] INT ,
[CostCodeID] INT
);
DECLARE @tblLineItemSubParts TABLE
(
[LineItemSubPartID] INT ,
[LineItemID] INT ,
[Quantity] INT ,
[MQuantity] NUMERIC ,
[Cost] NUMERIC ,
[CostCodeSubCategoryID] INT
);
INSERT INTO @tblLineItems (
[LineItemID] ,
[JobID]
)
VALUES (
681, 46
) ,
(
682, 46
) ,
(
692, 47
) ,
(
694, 47
) ,
(
695, 47
) ,
(
696, 47
) ,
(
706, 48
) ,
(
707, 48
) ,
(
708, 48
) ,
(
709, 48
) ,
(
711, 50
) ,
(
712, 49
) ,
(
713, 49
) ,
(
743, 54
) ,
(
744, 54
) ,
(
745, 54
) ,
(
746, 54
) ,
(
747, 54
) ,
(
748, 54
) ,
(
749, 54
) ,
(
750, 54
) ,
(
751, 54
) ,
(
752, 54
) ,
(
753, 54
) ,
(
754, 54
) ,
(
755, 55
) ,
(
756, 55
) ,
(
768, 56
) ,
(
771, 57
) ,
(
772, 58
) ,
(
773, 58
);
INSERT INTO @tblLineItemSubParts (
[LineItemSubPartID] ,
[LineItemID] ,
[Quantity] ,
[MQuantity] ,
[Cost] ,
[CostCodeSubCategoryID]
)
VALUES (
293784, 400, 1, 200, 0, NULL
) ,
(
294194, 400, 1, 1, 500, NULL
) ,
(
294195, 400, 1, 1, 200, NULL
) ,
(
323271, 694, 1, 0, 0, NULL
) ,
(
323272, 694, 1, 0, 0, NULL
) ,
(
323273, 694, 1, 5.39, 3.095, 3
) ,
(
323274, 694, 1, 0, 0, NULL
) ,
(
323275, 694, 1, 8.98, 3.095, 3
) ,
(
323276, 694, 1, 0, 0, NULL
) ,
(
323277, 694, 1, 2.92, 3.095, 3
) ,
(
323278, 694, 1, 0, 0, NULL
) ,
(
323279, 694, 1, 2.92, 3.095, 3
) ,
(
323280, 694, 1, 0, 0, NULL
) ,
(
323281, 694, 1, 5.18, 3.095, 3
) ,
(
323282, 694, 1, 0, 0, NULL
) ,
(
323283, 694, 1, 2.63, 3.15, 3
) ,
(
323284, 694, 6, 0, 0, NULL
) ,
(
323285, 694, 6, 0, 0, NULL
) ,
(
323286, 694, 18, 1.11, 3.095, 3
) ,
(
323287, 694, 6, 0, 0, NULL
) ,
(
323288, 694, 6, 0.29, 3.095, 3
) ,
(
323289, 694, 6, 0, 0, NULL
) ,
(
323290, 694, 18, 1.11, 3.095, 3
) ,
(
323291, 694, 6, 0, 0, NULL
) ,
(
323292, 694, 18, 4.85, 3.095, 3
) ,
(
323293, 694, 6, 0, 0, NULL
) ,
(
323294, 694, 18, 4.85, 3.095, 3
);
INSERT INTO @tblCostCodeSubCategories (
[CostCodeSubCategoryID] ,
[CostCodeID]
)
VALUES (
1, 1
) ,
(
2, 1
) ,
(
3, 1
) ,
(
4, 2
) ,
(
5, 2
) ,
(
6, 3
) ,
(
7, 3
) ,
(
8, 3
) ,
(
9, 3
) ,
(
10, 3
) ,
(
11, 3
) ,
(
12, 3
) ,
(
13, 4
) ,
(
14, 5
) ,
(
15, 5
) ,
(
16, 6
) ,
(
17, 6
) ,
(
18, 6
) ,
(
19, 6
) ,
(
20, 6
) ,
(
21, 6
) ,
(
22, 6
) ,
(
23, 6
) ,
(
24, 6
) ,
(
25, 7
) ,
(
26, 8
) ,
(
27, 9
) ,
(
28, 10
) ,
(
29, 11
) ,
(
30, 12
) ,
(
31, 13
) ,
(
32, 14
) ,
(
33, 15
) ,
(
34, 16
) ,
(
35, 17
) ,
(
36, 18
) ,
(
37, 19
) ,
(
38, 20
) ,
(
39, 21
) ,
(
40, 22
) ,
(
41, 23
) ,
(
42, 24
) ,
(
43, 25
) ,
(
44, 29
) ,
(
45, 30
) ,
(
46, 30
) ,
(
47, 31
) ,
(
48, 31
) ,
(
49, 31
) ,
(
50, 31
) ,
(
51, 31
) ,
(
52, 31
) ,
(
53, 31
) ,
(
54, 31
) ,
(
55, 31
) ,
(
56, 32
) ,
(
57, 33
) ,
(
58, 34
) ,
(
59, 35
) ,
(
60, 36
) ,
(
61, 37
) ,
(
62, 38
) ,
(
63, 39
) ,
(
64, 40
) ,
(
65, 41
) ,
(
66, 42
) ,
(
67, 43
) ,
(
68, 44
) ,
(
69, 45
) ,
(
70, 46
) ,
(
71, 48
) ,
(
72, 50
) ,
(
73, 51
) ,
(
74, 52
) ,
(
75, 26
) ,
(
76, 26
) ,
(
77, 26
) ,
(
78, 27
) ,
(
79, 27
) ,
(
80, 28
) ,
(
81, 28
) ,
(
82, 28
) ,
(
83, 28
) ,
(
84, 28
) ,
(
85, 28
) ,
(
86, 28
) ,
(
87, 47
) ,
(
88, 49
) ,
(
89, 53
) ,
(
90, 54
) ,
(
91, 25
) ,
(
92, 52
) ,
(
93, 25
) ,
(
94, 52
) ,
(
95, 3
) ,
(
96, 28
);
INSERT INTO @tblCostCodes
VALUES (
1, '18-10-101'
) ,
(
2, '18-10-102'
) ,
(
3, '18-10-103'
) ,
(
4, '18-10-104'
) ,
(
5, '18-10-105'
) ,
(
6, '18-10-106'
) ,
(
7, '18-10-201'
) ,
(
8, '18-10-202'
) ,
(
9, '18-10-203'
) ,
(
10, '18-10-204'
) ,
(
11, '18-10-205'
) ,
(
12, '18-10-240'
) ,
(
13, '18-10-400'
) ,
(
14, '18-10-800'
) ,
(
15, '58-10-100'
) ,
(
16, '58-10-200'
) ,
(
17, '58-20-350'
) ,
(
18, '68-10-100'
) ,
(
19, '68-10-200'
) ,
(
20, '68-10-300'
) ,
(
21, '68-20-200'
) ,
(
22, '78-10-130'
) ,
(
23, '58-10-212'
) ,
(
24, '18-10-401'
) ,
(
25, '18-10-405'
) ,
(
26, '14-10-101'
) ,
(
27, '14-10-102'
) ,
(
28, '14-10-103'
) ,
(
29, '14-10-104'
) ,
(
30, '14-10-105'
) ,
(
31, '14-10-106'
) ,
(
32, '14-10-201'
) ,
(
33, '14-10-202'
) ,
(
34, '14-10-203'
) ,
(
35, '14-10-204'
) ,
(
36, '14-10-205'
) ,
(
37, '14-10-240'
) ,
(
38, '14-10-400'
) ,
(
39, '14-10-800'
) ,
(
40, '54-10-100'
) ,
(
41, '54-10-200'
) ,
(
42, '54-20-350'
) ,
(
43, '64-10-100'
) ,
(
44, '64-10-200'
) ,
(
45, '64-10-300'
) ,
(
46, '64-20-200'
) ,
(
47, '64-20-300'
) ,
(
48, '74-10-130'
) ,
(
49, '74-99-700'
) ,
(
50, '54-10-212'
) ,
(
51, '14-10-401'
) ,
(
52, '14-10-405'
) ,
(
53, '??-??-???'
) ,
(
54, '??-??-???'
);
I'm still working on the query to get what you want, but the query you provided does not produce the results you stated with the input provided best I can tell. When I run the query I get 1 result.
EDIT:
is this the results you were looking for:SELECT
[CC].[costCodeID] ,
[CC].[costCode] ,
CONVERT(
MONEY ,
SUM([lisp].[Quantity] * [lisp].[MQuantity] * [lisp].[Cost])
) AS [TotalCost]
FROM
@tblLineItemSubParts [lisp]
FULL OUTER JOIN @tblCostCodeSubCategories [CCSC]
ON [CCSC].[CostCodeSubCategoryID] = [lisp].[CostCodeSubCategoryID]
FULL OUTER JOIN @tblCostCodes [CC]
ON [CCSC].[CostCodeID] = [CC].[costCodeID]
WHERE
[CC].[costCodeID] IS NOT NULL
GROUP BY
[CC].[costCodeID] ,
[CC].[costCode]
ORDER BY
[CC].[costCodeID];
I believe that is returning what you were looking for, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.