Let SQL do the work ?
-
Long time reader, 1st time poster here. I'm trying to build a stored procedure that creates a table that looks like an excel table. I have 1 table that contains Operator Names and 35 other tables that contain the operator names and the jobs they have completed. All I need to do is count the amount of times their name appears in each table, referencing the Operator Name table so as to count a zero if their name does not appear in any of the 35 other tables. Here is what I would like the output to look like:
I can create the 1st 2 columns ([OpName], NO TRUCK OUTAGES (NTF)] but can't figure how to insert the 3rd and remaining 34. Here is the query I have so far that works.
IF OBJECT_ID('Dashboard.dbo.RankDashboard') is not null
DROP TABLE Dashboard.dbo.RankDashboard
SELECT
T1.OpName,
COUNT(T2.OpName) as [NO TRUCK OUTAGES (NTF)]
FROM DSTX_Tickets.dbo.RocOps T1
FULL OUTER join [StackRank].[dbo].[NTF_NO_TRUCK_Outages] T2 on T2.OpName = T1.OpName
GROUP BY T1.OpName
I do get this error on the working statement, but I toss a "SET ANSI_WARNINGS OFF" in front of it to suppress the warning. Not sure if good practice.
Warning: Null value is eliminated by an aggregate or other SET operation.
I have tried to add INSERT INTO SELECT statements after the working code like this:
SET ANSI_WARNINGS OFF
IF OBJECT_ID('Dashboard.dbo.RankDashboard') is not null
DROP TABLE Dashboard.dbo.RankDashboardSELECT
T1.OpName,
COUNT(T2.OpName) as [NO TRUCK OUTAGES (NTF)]
INTO Dashboard.dbo.RankDashboard
FROM DSTX_Tickets.dbo.RocOps T1
FULL OUTER join [StackRank].[dbo].[NTF_NO_TRUCK_Outages] T2 on T2.OpName = T1.OpName
GROUP BY T1.OpName
ORDER BY OpNameINSERT INTO Dashboard.dbo.RankDashboard
SELECT
COUNT(T2.OpName) as 'NO TRUCK OUTAGES (ROC POWER)'
--T1.OpName,
FROM DSTX_Tickets.dbo.RocOps T1
FULL OUTER join [StackRank].[dbo].[ROC_Power_NO_TRUCK] T2 on T2.OpName = T1.OpName
GROUP BY T1.OpName
ORDER BY T1.OpNameBut I get the following error:
Msg 213, Level 16, State 1, Line 26
Column name or number of supplied values does not match table definition.Also tried a few other ways like having the second statement be an "Alter Table Add Insert Into Select". But still no luck.
I feel like I'm missing something very simple and just need a second set of eyes. Or a completely different approach idea to take.
Thank you all in advance for any assistance you can provide.
Also wanted to note that all of the tables have another column (Operator ID) that can be used to identify the Operator name. -
This statement looks wrong:
INSERT INTO Dashboard.dbo.RankDashboard
SELECT
COUNT(T2.OpName) as 'NO TRUCK OUTAGES (ROC POWER)'
--T1.OpName,
FROM DSTX_Tickets.dbo.RocOps T1
FULL OUTER join [StackRank].[dbo].[ROC_Power_NO_TRUCK] T2 on T2.OpName = T1.OpName
GROUP BY T1.OpName
ORDER BY T1.OpNameThe table Dashboard.dbo.RankDashboard's definition will be something like this:
CREATE TABLE Dashboard.dbo.RankDashboard
(
OpName nvarchar(100) NULL,
[NO TRUCK OUTAGES (NTF)] int null
)You are trying to insert just one column into that table without listing the column names and you are trying to insert the columns with the int column and text column switched round (also one of them is commented out).
So I think you need to specify the columns in the insert and uncomment the column that's commented out.:INSERT INTO Dashboard.dbo.RankDashboard([NO TRUCK OUTAGES (NTF)], OpName)
SELECT COUNT(T2.OpName),
T1.OpName
FROM DSTX_Tickets.dbo.RocOps T1
FULL OUTER join [StackRank].[dbo].[ROC_Power_NO_TRUCK] T2 on T2.OpName = T1.OpName
GROUP BY T1.OpName
ORDER BY T1.OpName -
The reason I have T1.OpName commented out in the 2nd insert was to not have the Operator Names appended to the same column already existing. I would need to create and name the columns being added in the statements that happen after the table is created. The data in those columns would just be the count of times their names appear in the other tables. I tried what you recommended, but it just produces 2 columns, OpName and NO TRUCK OUTAGES (NTF). I need another column named ROC Power No Truck with counts the Operator Names appear in that table. Like this:
The reason I can't create the table ahead of time and create all of the columns, is because the DSTX_Tickets.dbo.RocOps table that houses all the Operator names, changes all the time.
-
A couple of issues you have with this query. First - you should join on the OperatorID and not the name (in fact - the name should not be in the other tables, they should only have the operator ID. Second - don't use a FULL OUTER JOIN - you can use LEFT OUTER JOIN (or just LEFT JOIN). And finally, since this is being inserted into another table - the ORDER BY isn't doing anything other than forcing an expensive sort.
Also - don't put spaces in column names, it makes it much harder to code and read.
SELECT T1.OpName
, COUNT(T2.OperatorID) AS NTF
, COUNT(T3.OperatorID) AS ROCPower
INTO Dashboard.dbo.RankDashboard
FROM DSTX_Tickets.dbo.RocOps T1
LEFT JOIN StackRank.dbo.NTF_NO_TRUCK_Outages T2 ON T2.OperatorID = T1.OperatorID
LEFT JOIN StackRank.dbo.ROC_Power_NO_TRUCK T3 ON T3.OperatorID = T1.OperatorID
GROUP BY T1.OperatorIDJeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
If you know the names of the 35 tables, you should be able to build your table first, with the 35 column names you want to use.
CREATE -- DROP
TABLE Dashboard.dbo.RankDashboard
(
OpName VARCHAR(100) NOT NULL,
[NO TRUCK OUTAGES (NTF)] INT NOT NULL DEFAULT(0),
[NO TRUCK OUTAGES (ROC POWER)] INT NOT NULL DEFAULT(0),
...
)
I added the DEFAULTS so that the values would always be zero when missing, but that could be handled on the output using ISNULL() if you prefer.Then next step is to add records using MERGE. MERGE allows you to INSERT records that don't already exist and UPDATE records that do exist. Using each of the 35 tables, write a MERGE statement to update the appropriate field with either INSERT or UPDATE:
MERGE Dashboard.dbo.RankDashboard u
USING (
SELECT OpName,
COUNT(OpName) as [NO TRUCK OUTAGES (NTF)]
FROM [StackRank].[dbo].[NTF_NO_TRUCK_Outages]
GROUP BY OpName
) x
ON x.OpName = u.OpName
WHEN MATCHED THEN
UPDATE
SET [NO TRUCK OUTAGES (NTF)] = x.[NO TRUCK OUTAGES (NTF)]
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
OptName, [NO TRUCK OUTAGES (NTF)],
) VALUES (
x.OptName, x.[NO TRUCK OUTAGES (NTF)]
)
;
For each table, change the USING clause to count the new table, then change the column names in the USING, INSERT, and UPDATE clauses to affect the next column.All of this presumes you know the 35 tables and the 35 column names. If that is not the case, you'll need to use dynamic SQL. I have samples, so let me know if that is needed.
-
To Jeffrey Williams 3188 - Thank you, but that originally threw a Group By error (I'm using MSSQL). I fixed that by adding T1.OpName in the Group By. It got rid of the error but the output has duplicate counts in the NTF column and the ROCPower column. The counts are also way off. The values I'm getting are the counts of the first table multiplied by the second.
SELECT
T1.OpName
,COUNT(T2.RepID) AS NTF
, COUNT(T3.RepID) AS ROCPower
INTO Dashboard.dbo.RankDashboard
FROM DSTX_Tickets.dbo.RocOps T1
LEFT JOIN StackRank.dbo.NTF_NO_TRUCK_Outages T2 ON T2.RepID = T1.OpID
LEFT JOIN StackRank.dbo.ROC_Power_NO_TRUCK T3 ON T3.RepID = T1.OpID
group BY T1.OpID,T1.OpName
OutputWas expecting these results:
-
To fahey.jonathan - Yours does give me accurate counts but the OpName column does not have all of the OpNames that are in DSTX_Tickets.dbo.RocOps. I need them all to appear so if their name is not counted in another table, the result entered is a zero. I went on to add the next Merge statement to insert the counts. That resulted in even less names appearing in the OpName column.
I need the OpName column to be Static per say. It would only change if a name was not found in the DSTX_Tickets.dbo.RocOps table. Would the ISNULL() function you mentioned take care of that ?
See. It's missing Jane Smith:
I need:
Thank you all for your assistance with this.
-
Would creating case statements be a better approach? I've looked into Transpose functions and Pivot but still can't seem to get the output. How about looping through the set of names ? I'm lost at this point.
- onemangathers - Thursday, January 17, 2019 10:08 AMTo Jeffrey Williams 3188 - Thank you, but that originally threw a Group By error (I'm using MSSQL). I fixed that by adding T1.OpName in the Group By. It got rid of the error but the output has duplicate counts in the NTF column and the ROCPower column. The counts are also way off. The values I'm getting are the counts of the first table multiplied by the second.
SELECT
T1.OpName
,COUNT(T2.RepID) AS NTF
, COUNT(T3.RepID) AS ROCPower
INTO Dashboard.dbo.RankDashboard
FROM DSTX_Tickets.dbo.RocOps T1
LEFT JOIN StackRank.dbo.NTF_NO_TRUCK_Outages T2 ON T2.RepID = T1.OpID
LEFT JOIN StackRank.dbo.ROC_Power_NO_TRUCK T3 ON T3.RepID = T1.OpID
group BY T1.OpID,T1.OpName
OutputWas expecting these results:
I mis-copied and left out the group by...
Instead of LEFT JOIN which ends up including additional rows - you can use outer apply and calculate the counts...
SELECT T1.OpName
, st1.NTF
, st2.ROCPower
INTO Dashboard.dbo.RankDashboard
FROM DSTX_Tickets.dbo.RocOps T1
OUTER APPLY (SELECT COUNT(*) AS NTF From StackRank.dbo.NTF_NO_TRUCK_Outages T2 WHERE T2.RepID = T1.OpID) As sr1
OUTER APPLY (SELECT COUNT(*) AS ROCPower From StackRank.dbo.ROC_Power_NO_TRUCK T3 WHERE T3.RepID = T1.OpID) As sr2Or - you can use a derived table that groups by operator and left join that:
SELECT T1.OpName
, t2.NTF
, t3.ROCPower
INTO Dashboard.dbo.RankDashboard
FROM DSTX_Tickets.dbo.RocOps T1
LEFT JOIN (SELECT OperatorID, COUNT(*) AS NTF From StackRank.dbo.NTF_NO_TRUCK_Outages GROUP BY OperatorID) t2 ON t2.OperatorID = t1.OperatorID
LEFT JOIN (SELECT OperatorID, COUNT(*) AS ROCPower From StackRank.dbo.ROC_Power_NO_TRUCK GROUP BY OperatorID) t3 ON t3.OperatorID = t1.OperatorIDJeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - onemangathers - Thursday, January 17, 2019 10:42 AMTo fahey.jonathan - Yours does give me accurate counts but the OpName column does not have all of the OpNames that are in DSTX_Tickets.dbo.RocOps. I need them all to appear so if their name is not counted in another table, the result entered is a zero. I went on to add the next Merge statement to insert the counts. That resulted in even less names appearing in the OpName column.
I need the OpName column to be Static per say. It would only change if a name was not found in the DSTX_Tickets.dbo.RocOps table. Would the ISNULL() function you mentioned take care of that ?
See. It's missing Jane Smith:
I need:
Thank you all for your assistance with this.
You could start by loading the list of all names into the table from the base Operator table. That would give you a full list of all operators. Then update each column from the other 35 tables. You could use the MERGE just in case, but doing a normal UPDATE would work if every operator from the 35 tables is already in your base table.
-
Guys, thank you both for your insight and direction. Jeffrey Williams 3188, that did the trick. The Query is working just the way I need it to.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply