SQL Server - Select with Group By together Raw_Number

Question

I'm using SQL Server 2000 (80). So, it's not possible to use the LAG function.

I have a code a data set with four columns:

Purchase_Date

Facility_no

Seller_id

Sale_id

I need to identify missing Sale_ids. So every sale_id is a 100% sequential, so the should not be any gaps in order.

This code works for a specific date and store if specified. But i need to work on entire data set looping looping through every facility_id and every seller_id for ever purchase_date

declare @MAXCOUNT int

set @MAXCOUNT = 
(
    select MAX(Sale_Id) 
    from #table 
    where
        Facility_no in (124) and
        Purchase_date = '2/7/2020'
        and Seller_id = 1
)

;WITH TRX_COUNT AS
(
SELECT 1 AS Number
union all
select Number + 1 from TRX_COUNT
where Number < @MAXCOUNT
)
select * from TRX_COUNT 
where 
Number NOT IN 
(
    select Sale_Id
    from #table 
    where
    Facility_no in (124) 
    and Purchase_Date = '2/7/2020' 
    and seller_id = 1
)   
    order by Number
    OPTION (maxrecursion 0)

My Dataset

Answer 1

This one worked out

; WITH cte_Rn AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Facility_no, Purchase_Date, Seller_id ORDER BY Purchase_Date) AS [Rn_Num] 
FROM (
        SELECT 
            Facility_no,
            Purchase_Date,
            Seller_id,
            Sale_id
        FROM  MyTable WITH (NOLOCK)
    ) a
)
, cte_Rn_0 as (
SELECT 
    Facility_no,
    Purchase_Date,
    Seller_id,
    Sale_id, 
--  [Rn_Num] AS 'Skipped Sale'
--  , case when Sale_id = 0 Then [Rn_Num] - 1 Else [Rn_Num] End AS 'Skipped Sale for 0'
    , [Rn_Num] - 1 AS 'Skipped Sale for 0'
FROM cte_Rn a
)
SELECT 
    Facility_no,
    Purchase_Date,
    Seller_id,
    Sale_id, 
--  [Skipped Sale],
    [Skipped Sale for 0]
FROM cte_Rn_0 a
WHERE NOT EXISTS 
        (  
            select * from cte_Rn_0 b 
            where b.Sale_id = a.[Skipped Sale for 0]
            and a.Facility_no = b.Facility_no 
            and a.Purchase_Date = b.Purchase_Date 
            and a.Seller_id = b.Seller_id
        )
--ORDER BY Purchase_Date ASC