I have data which is the results from a bunch of CTE and a query and this is where I am at.
Query (not including all previous CTE tables):
SELECT CustomerCode, SUBSTRING(SerialNumber, CHARINDEX('-', SerialNumber) + 1, 6) SerialNumber, nbServer, ISNULL(nbImaging, 0) nbImaging FROM Magic WHERE CS_MagicNUmber <> I_MagicNumber OR CS_MagicNUmber IS NULL OR I_MagicNUmber IS NULL
Sample of resulting data:
What I want is to filter those results for each group of records sharing the same
CustomerCode I.E. to get the
TOP (nbServer - nbImaging).
In the sample shown, for
CustomerCode 20032455 is it simple as the difference is 3 (3-0) and I have 2 rows. But for the following
CustomerCode, 20032625, I want the 5 first rows (12-7), so those with
SerialNumber 805779, 805782, 805781, 805778, 805783 .
Of course there are a lot more but I think that shows what I want as a result. I have read multiple examples and also try
PARTITION OVER but I must confess, nothing works.
This is relatively straight forward with
ROW_NUMBER as that just gives you ascending sequential integers from
1 that you can use in a
I do also tuck the calculation of SerialNumber into a
CROSS APPLY so I can reference it twice without repeating the expression.
WITH T AS ( SELECT CustomerCode ,ca.SerialNumber ,nbServer ,isnull(nbImaging,0) nbImaging ,ROW_NUMBER() over (partition by CustomerCode ORDER BY ca.SerialNumber) rn FROM Magic cross apply (select SUBSTRING(SerialNumber,CHARINDEX('-',SerialNumber)+1,6) SerialNumber) ca WHERE CS_MagicNUmber <> I_MagicNumber OR CS_MagicNUmber IS NULL OR I_MagicNUmber IS NULL ) SELECT * FROM T WHERE rn <= nbServer - nbImaging