كيفية الحصول على الرقم المفقود لكل اسم/كود

اول كود

--الارقام المقودة بدلالة عدد السجلات الحالية او الترقيم الصحيح
DECLARE @Table Table (Name nvarchar(50),Code nvarchar(4),Number int) 
INSERT INTO @Table (Name,Code,Number) 
SELECT 'Mohamed','X',3 UNION 
SELECT 'Mohamed','X',5 UNION 
SELECT 'Mohamed','X',6 UNION 
SELECT 'Mohamed','X',10 UNION 
SELECT 'Mohamed','Y',1 UNION 
SELECT 'Mohamed','Y',2 UNION 
SELECT 'Mohamed','Y',9 UNION 
SELECT 'Mohamed','Y',11 UNION 
SELECT 'Mohamed','DK',6 UNION 
SELECT 'Mohamed','DK',9 UNION 
SELECT 'Mohamed','DK',10 UNION 
SELECT 'Mohamed','DK',11 UNION 
SELECT 'Mohamed','DK',12 UNION 
SELECT 'Mohamed','DK',13 UNION 
SELECT 'Ali' ,'Z',1 UNION 
SELECT 'Ali' ,'Z',2 UNION 
SELECT 'Ali' ,'Z',3 UNION 
SELECT 'Ali' ,'Z',4 UNION 
SELECT 'Ali' ,'Z',6 UNION 
SELECT 'Ali' ,'X',15 UNION 
SELECT 'Ali' ,'X',16 UNION 
SELECT 'Ali' ,'X',17 UNION 
SELECT 'Ali' ,'X',50 UNION 
SELECT 'Ali' ,'H',12 UNION 
SELECT 'Ali' ,'H',13 UNION 
SELECT 'Ali' ,'H',18 UNION 
SELECT 'Ali' ,'H',19 UNION 
SELECT 'Ali' ,'BI',5 UNION 
SELECT 'Ali' ,'BI',12 UNION 
SELECT 'Salim' ,'X',1 UNION 
SELECT 'Salim' ,'X',3 UNION 
SELECT 'Salim' ,'X',4 UNION 
SELECT 'Salim' ,'X',5 UNION 
SELECT 'Salim' ,'X',6 UNION 
SELECT 'Salim' ,'X',9 UNION 
SELECT 'Salim' ,'X',10 UNION 
SELECT 'Salim' ,'KH',1 UNION 
SELECT 'Salim' ,'KH',3 UNION 
SELECT 'Salim' ,'KH',5 UNION 
SELECT 'Salim' ,'KH',8 UNION 
SELECT 'Salim' ,'KH',10 UNION 
SELECT 'Salim' ,'KH',11 UNION 
SELECT 'Salim' ,'KH',12 UNION 
SELECT 'Salim' ,'KH',13 UNION 
SELECT 'Salim' ,'KH',14 UNION 
SELECT 'Salim' ,'KH',15 UNION 
SELECT 'Salim' ,'DR',15 UNION 
SELECT 'Salim' ,'DR',16 UNION 
SELECT 'Salim' ,'DR',60 UNION 
SELECT 'Salim' ,'DR',61 UNION 
SELECT 'Salim' ,'DR',62

select *
from(
SELECT * ,row_number() over (partition by Name,Code ORDER BY Number) as sn
FROM @Table 
) as tmp
where Number<>sn
ORDER BY Name,Code,sn

الكود الثاني

--الارقام المقودة بدلالة عدد السجلات الحالية او الترقيم الصحيح
DECLARE @Table Table (Name nvarchar(50),Code nvarchar(4),Number int) 
INSERT INTO @Table (Name,Code,Number) 
SELECT 'Mohamed','X',3 UNION 
SELECT 'Mohamed','X',5 UNION 
SELECT 'Mohamed','X',6 UNION 
SELECT 'Mohamed','X',10 UNION 
SELECT 'Mohamed','Y',1 UNION 
SELECT 'Mohamed','Y',2 UNION 
SELECT 'Mohamed','Y',9 UNION 
SELECT 'Mohamed','Y',11 UNION 
SELECT 'Mohamed','DK',6 UNION 
SELECT 'Mohamed','DK',9 UNION 
SELECT 'Mohamed','DK',10 UNION 
SELECT 'Mohamed','DK',11 UNION 
SELECT 'Mohamed','DK',12 UNION 
SELECT 'Mohamed','DK',13 UNION 
SELECT 'Ali' ,'Z',1 UNION 
SELECT 'Ali' ,'Z',2 UNION 
SELECT 'Ali' ,'Z',3 UNION 
SELECT 'Ali' ,'Z',4 UNION 
SELECT 'Ali' ,'Z',6 UNION 
SELECT 'Ali' ,'X',15 UNION 
SELECT 'Ali' ,'X',16 UNION 
SELECT 'Ali' ,'X',17 UNION 
SELECT 'Ali' ,'X',50 UNION 
SELECT 'Ali' ,'H',12 UNION 
SELECT 'Ali' ,'H',13 UNION 
SELECT 'Ali' ,'H',18 UNION 
SELECT 'Ali' ,'H',19 UNION 
SELECT 'Ali' ,'BI',5 UNION 
SELECT 'Ali' ,'BI',12 UNION 
SELECT 'Salim' ,'X',1 UNION 
SELECT 'Salim' ,'X',3 UNION 
SELECT 'Salim' ,'X',4 UNION 
SELECT 'Salim' ,'X',5 UNION 
SELECT 'Salim' ,'X',6 UNION 
SELECT 'Salim' ,'X',9 UNION 
SELECT 'Salim' ,'X',10 UNION 
SELECT 'Salim' ,'KH',1 UNION 
SELECT 'Salim' ,'KH',3 UNION 
SELECT 'Salim' ,'KH',5 UNION 
SELECT 'Salim' ,'KH',8 UNION 
SELECT 'Salim' ,'KH',10 UNION 
SELECT 'Salim' ,'KH',11 UNION 
SELECT 'Salim' ,'KH',12 UNION 
SELECT 'Salim' ,'KH',13 UNION 
SELECT 'Salim' ,'KH',14 UNION 
SELECT 'Salim' ,'KH',15 UNION 
SELECT 'Salim' ,'DR',15 UNION 
SELECT 'Salim' ,'DR',16 UNION 
SELECT 'Salim' ,'DR',60 UNION 
SELECT 'Salim' ,'DR',61 UNION 
SELECT 'Salim' ,'DR',62

SELECT z.Name,z.Code,SeqID
FROM(
		SELECT 
					tTableMax.Name,
					tTableMax.Code,
					tTableMax.nNumber,
					ROW_NUMBER() OVER (partition by tTableMax.Name,tTableMax.Code ORDER BY tValues.number) SeqID 
		from  
				master..spt_values as tValues,
				(
				SELECT Name,Code,MAX(number) AS nNumber
				FROM @Table 
				GROUP BY Name,Code
				) as tTableMax
	) AS Z
left join
		@Table as tTable
	on
		tTable.name=z.Name 
	and
		tTable.code=z.Code
	and
		tTable.number=z.SeqID
where 	
		SeqID<nNumber
	and
		tTable.number is null
order by 1,2,3

كل المقود

--هذا السطر له حد
SELECT distinct number
FROM master..spt_values

تسلم يا خبير :) 

الكود الثاني بالظبط هو اللي عاوزه 

متشكرين على المتابعة والإهتمام

اكرمك الله