February 26, 2019 at 1:25 pm
Hi Everyone,
Reaching out for some help solving a SQL problem. I have a list of active servers and a list of packages and which servers they were deployed to. I am trying to return a list of missing servers per package.
I have tried a number of methods but each one seems kind of kludgy. Hoping someone has an elegant solution for me.
Thanks in advance!
DECLARE @PackageServers TABLE
(
PackageID INT
,ServerName VARCHAR(50)
)
DECLARE @DeploymentServers TABLE
(
ServerName VARCHAR(50)
)
INSERT INTO @DeploymentServers(ServerName)
SELECT 'Server-1'
UNION ALL SELECT 'Server-2'
UNION ALL SELECT 'Server-3'
INSERT INTO @PackageServers (PackageID, ServerName)
SELECT 1, 'Server-1'
UNION ALL SELECT 2, 'Server-1'
UNION ALL SELECT 3, 'Server-1'
UNION ALL SELECT 4, 'Server-1'
UNION ALL SELECT 1, 'Server-2'
UNION ALL SELECT 1, 'Server-3'
UNION ALL SELECT 2, 'Server-2'
-- Expected Output
/*
PackageID | MissingServers
2 | 'Server-3'
3 | 'Server-2,Server-3'
4 | 'Server-2,Server-3'
*/
February 26, 2019 at 1:37 pm
I think you're missing a table.
Package (PackageID*…)
Server(ServerID*,..)
ServerPkg(ServerID*, PackageID*) (basically the "contents"/inventory of each server)
One way to do it is
(Package CROSS JOIN Server) MINUS ServerPkg
February 26, 2019 at 2:04 pm
It's easy enough to get a list of the missing values. But getting a concatenated list is somewhat more difficult.
;WITH cte_packages AS (
SELECT DISTINCT PackageID
FROM @PackageServers
)
SELECT cp.PackageID, DS.ServerName AS MissingServer
FROM cte_packages cp
CROSS JOIN @DeploymentServers DS
LEFT OUTER JOIN @PackageServers PS ON PS.PackageID = cp.PackageID AND
PS.ServerName = DS.ServerName
WHERE PS.ServerName IS NULL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 26, 2019 at 4:31 pm
ScottPletcher - Tuesday, February 26, 2019 2:04 PMIt's easy enough to get a list of the missing values. But getting a concatenated list is somewhat more difficult.
;WITH cte_packages AS (
SELECT DISTINCT PackageID
FROM @PackageServers
)
SELECT cp.PackageID, DS.ServerName AS MissingServer
FROM cte_packages cp
CROSS JOIN @DeploymentServers DS
LEFT OUTER JOIN @PackageServers PS ON PS.PackageID = cp.PackageID AND
PS.ServerName = DS.ServerName
WHERE PS.ServerName IS NULL
This CROSS JOIN/OUTER JOIN is pretty much the solution I came up with. I am looking for something more elegant.
February 26, 2019 at 5:43 pm
Elegant? That's the standard pattern for this kind of question. AFAIK, that's the way to do it.
February 26, 2019 at 6:37 pm
Another way of getting the results:SELECT x.PackageID, d.ServerName MissingServer
FROM @DeploymentServers d
CROSS APPLY(SELECT DISTINCT p.PackageID
FROM @PackageServers p
WHERE NOT EXISTS(SELECT *
FROM @PackageServers p2
WHERE p2.PackageID = p.PackageID
AND p2.ServerName = d.ServerName)) x
February 27, 2019 at 10:44 am
Goldie Lesser - Tuesday, February 26, 2019 4:31 PMScottPletcher - Tuesday, February 26, 2019 2:04 PMIt's easy enough to get a list of the missing values. But getting a concatenated list is somewhat more difficult.
;WITH cte_packages AS (
SELECT DISTINCT PackageID
FROM @PackageServers
)
SELECT cp.PackageID, DS.ServerName AS MissingServer
FROM cte_packages cp
CROSS JOIN @DeploymentServers DS
LEFT OUTER JOIN @PackageServers PS ON PS.PackageID = cp.PackageID AND
PS.ServerName = DS.ServerName
WHERE PS.ServerName IS NULLThis CROSS JOIN/OUTER JOIN is pretty much the solution I came up with. I am looking for something more elegant.
Interesting. Please let me know if you find it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply