I have the following data in a SQL Table:
I need to query the data so I can get a list of missing "familyid" per employee.
For example, I should get for Employee 1021 that is missing in the sequence the IDs: 2 and 5 and for Employee 1027 should get the missing numbers 1 and 6.
Any clue on how to query that?
Appreciate any help.
Find the first missing value
I would use the ROW_NUMBER window function to assign the "correct" sequence ID number. Assuming that the sequence ID restarts every time the employee ID changes:
SELECT
e.id,
e.name,
e.employee_number,
e.relation,
e.familyid,
ROW_NUMBER() OVER(PARTITION BY e.employeeid ORDER BY familyid) - 1 AS sequenceid
FROM employee_members e
Then, I would filter the result set to only include the rows with mismatching sequence IDs:
SELECT *
FROM (
SELECT
e.id,
e.name,
e.employee_number,
e.relation,
e.familyid,
ROW_NUMBER() OVER(PARTITION BY e.employeeid ORDER BY familyid) - 1 AS sequenceid
FROM employee_members e
) a
WHERE a.familyid <> a.sequenceid
Then again, you should easily group by employee_number and find the first missing sequence ID for each employee:
SELECT
a.employee_number,
MIN(a.sequence_id) AS first_missing
FROM (
SELECT
e.id,
e.name,
e.employee_number,
e.relation,
e.familyid,
ROW_NUMBER() OVER(PARTITION BY e.employeeid ORDER BY familyid) - 1 AS sequenceid
FROM employee_members e
) a
WHERE a.familyid <> a.sequenceid
GROUP BY a.employee_number
Finding all the missing values
Extending the previous query, we can detect a missing value every time the difference between familyid and sequenceid changes:
-- Warning: this is totally untested :-/
SELECT
b.employee_number,
MIN(b.sequence_id) AS missing
FROM (
SELECT
a.*,
a.familyid - a.sequenceid AS displacement
SELECT
e.*,
ROW_NUMBER() OVER(PARTITION BY e.employeeid ORDER BY familyid) - 1 AS sequenceid
FROM employee_members e
) a
) b
WHERE b.displacement <> 0
GROUP BY
b.employee_number,
b.displacement
Here is one approach. Calculate the maximum family id for each employee. Then join this to a list of numbers up to the maximum family id. The result has one row for each employee and expected family id.
Do a left outer join from this back to the original data, on the familyid and the number. Where nothing matches, those are the missing values:
with nums as (
select 1 as n
union all
select n+1
from nums
where n < 20
)
select en.employee, n.n as MissingFamilyId
from (select employee, min(familyid) as minfi, max(familyid) as maxfi
from t
group by employee
) en join
nums n
on n.n <= maxfi left outer join
t
on t.employee = en.employee and
t.familyid = n.n
where t.employee_number is null;
Note that this will not work when the missing familyid is that last number in the sequence. But it might be the best that you can do with your data structure.
Also the above query assumes that there are at most 20 family members.
This will work, you select all "Dependents" and left join on the prior row. If that row is not there then you show the result:
SELECT 'Missing Prior', t1.*
FROM employee_members t1
LEFT JOIN employee_members t2 ON t1.employee_number = t2.employee_number
AND (t1.familyid-1) = t2.familyid
WHERE t2.employee_number is null and t1.relation == 'Dependent'
Another version that shows you the missing number:
SELECT t1.employee_number, t1.familyid-1 as Missing_Member
FROM employee_members t1
LEFT JOIN employee_members t2 ON t1.employee_number = t2.employee_number
AND (t1.familyid-1) = t2.familyid
WHERE t2.employee_number is null and t1.relation == 'Dependent'
Another solution: Build up a table with all possible values from the sequence (can play with identity for this). Then left join on the table where source table is null.
DECLARE @Seq TABLE (id INT IDENTITY(1, 1))
DECLARE @iter INT = 1
WHILE @iter <= (
SELECT MAX([your ID column])
FROM [Offending Table]
)
BEGIN
INSERT @Seq DEFAULT
VALUES
SET @iter = @iter + 1
END
SELECT id
FROM @seq s
LEFT JOIN [Offending Table] ot ON s.id = ot.[your ID column]
WHERE ot.[your ID column] IS NULL
This select will retrieve list of missing "familyid" per employee using CTE approach.
QUERY :
WITH emp_grp (
EmployeeID
,MaxFamilyID
)
AS (
SELECT e2.EmployeeID
,MAX(e2.FamilyID) MaxFamilyID
FROM employee_number e2
GROUP BY e2.EmployeeID
)
,emp_mem
AS (
SELECT EmployeeID
,0 AS FamilyID
,MaxFamilyID
FROM emp_grp
UNION ALL
SELECT EmployeeID
,FamilyID + 1 AS FamilyID
,MaxFamilyID
FROM emp_mem
WHERE emp_mem.FamilyID < MaxFamilyID
)
SELECT emp_mem.EmployeeID
,emp_mem.FamilyID
FROM emp_mem
LEFT JOIN employee_number emp_num ON emp_mem.EmployeeID = emp_num.EmployeeID
AND emp_mem.FamilyID = emp_num.FamilyID
WHERE emp_num.EmployeeID IS NULL
ORDER BY emp_mem.EmployeeID
,emp_mem.FamilyID
OPTION ( MAXRECURSION 32767)
OUTPUT :
EmployeeID FamilyID
----------- -----------
1021 2
1021 5
1027 1
1027 6
