November 14, 2017 at 9:26 am
I have these tables: LineItemSubParts, CostCodeSubCategories, CostCodes and LineItems
In the LineItemSubParts table there are fields named Quantity, MQuantity and Cost
In the CostCodes table there are field named CostCodeID and CostCode
In the LineItems table there is a field named JobId
I want to calculate the sum of the costs for a specific JobId but I want all the costcodeIDs to show up.
If the cost is not present for particular jobid then count should be 0 corresponding to that jobid.
Here is the query I wrote
SELECT CostCodeID, CostCode, CONVERT(money, SUM(Quantity * MQuantity * Cost)) As TotalCost
FROM tblLineItemSubParts
INNER JOIN tblCostCodeSubCategories ON tblLineItemSubParts.CostCodeSubCategoryID=tblCostCodeSubCategories.CostCodeSubCategoryID
INNER JOIN tblCostCodes ON tblCostCodeSubCategories.CostCodeID=tblCostCodes.CostCodeID
WHERE LineItemID IN (SELECT LineItemID FROM tblLineItems WHERE FKJobID=47)
GROUP BY PKCostCodeID, CostCode
Here is the result I got:
| CostCodeID | CostCode | TotalCost |
| 1 | 18-10-101 | 854.1248 |
| 3 | 18-10-103 | 386.67 |
| 4 | 18-10-104 | 1577.775 |
| 7 | 18-10-201 | 9026.675 |
| 15 | 58-10-100 | 100 |
| 25 | 18-10-405 | 0 |
What I want is to show all the CostCodeIDs show on the table even If the cost is not present for particular jobid then count should be 0 corresponding to that jobid.
Attached is the sample tbl
November 14, 2017 at 1:44 pm
You are missing some data there... or I misunderstand the request.
is the attached table the expected output, the sample input or something else? If it is the sample input, where are you getting the total cost from? All I see is a cost code and a cost code ID. If it is expected output, it is missing the total cost. I expect that it is one of the 3 tables in your original query (likely tblCostCodes). It is that particular join that is likely causing you greif though. You are doing an inner join so the only rows that will be returned are the ones where there is a match. Try changing your last inner join to a full outer join.
But without seeing some sample data, I am really just taking stabs in the dark.
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.
November 14, 2017 at 2:35 pm
Sorry I thought I attached all the tables.
Here are the other tables attached.
What I want to get is something like this:
| CostCodeID | CostCode | TotalCost |
| 1 | 18-10-101 | 854.1248 |
| 2 | 18-10-102 | 0 |
| 3 | 18-10-103 | 386.67 |
| 4 | 18-10-104 | 1577.775 |
| 5 | 18-10-105 | 0 |
| 6 | 18-10-106 | 0 |
| 7 | 18-10-201 | 9026.675 |
| 8 | 18-10-202 | 0 |
| 9 | 18-10-203 | 0 |
| 10 | 18-10-204 | 0 |
| 11 | 18-10-205 | 0 |
| 12 | 18-10-240 | 0 |
| 13 | 18-10-400 | 0 |
| 14 | 18-10-800 | 0 |
| 15 | 58-10-100 | 100 |
| 16 | 58-10-200 | 0 |
-
-
-upto 54 67-10-665 0
November 14, 2017 at 2:57 pm
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 14, 2017 at 4:24 pm
Thank you for the tip.
I will post it using the guide
November 15, 2017 at 8:17 am
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.
November 15, 2017 at 8:27 am
rosarozina - Tuesday, November 14, 2017 9:26 AMWhat I want is to show all the CostCodeIDs show on the table even If the cost is not present for particular jobid then count should be 0 corresponding to that jobid.
Is this not as simple as changing the first INNER JOIN to a LEFT JOIN?
A word of advice - alias your tables, and use the aliases to qualify every column name, whether in the SELECT list, the WHERE clause or a join predicate. It'll make your query easier to ready and guard against ambiguous column names in the future.
John
November 15, 2017 at 10:55 am
bmg002 - Wednesday, November 15, 2017 8:17 AMfor 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?
Thank you for the reply, it gave me the total of the Sum, What I wanted was the cost for each costcode id for the specific jobid. I will try to change the filtering and hopefully it will work. Thanks again
November 15, 2017 at 10:56 am
rosarozina - Tuesday, November 14, 2017 4:24 PMThank you for the tip.
I will post it using the guide
look forward to it....
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply