SELECT TOP from CTE based on subtraction of 2 column group by another

March 3, 2023

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.

>Solution :

This is relatively straight forward with ROW_NUMBER as that just gives you ascending sequential integers from 1 that you can use in a WHERE clause.

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