Using cross join with multiple variable in cte

Question

I have some order numbers and want to check if any number has been skipped. I'll use left table method which is:

select * from 
    #CreatedCrossTable (which has all possibilities)
    Left Join #MainTableWhichHaveRealSerialNo mt
where
    mt is null

Order no structure is: "CodeType.Year.Month.SerialNo". For instance: "DP.21.07.001","DP.21.07.002".. or not DP, but "FB.21.07.001", "FB.21.07.002" etc.

I want to create a cross table schema for determine the skipped SerilNo values (CreatedCrossTable above):

(Serial number is reset every month)

CodeType | Year | Month | SerialNo
DP         21       1        1
DP         21       1        2
DP         21       1        3
DP         21       1        4
...
(All SerialNos must increase max serial number of the original table's SerialNo (MainTableWhichHaveRealSerialNo) Also codeType,year and month values must match)

DP         21       2        1
DP         21       2        2
...

FB         21       1        1
FB         21       1        2
...
FB         21       1        1
FB         21       1        2
FB         21       1        3
...

Each Codes' and Month's serial number have a different Maximum Number for creating CrossTable. I've written that code:

;WITH cteSerialNo AS
        (
            SELECT 1 AS ORDERNO
            UNION ALL
            SELECT (ORDERNO+1) AS ORDERNO FROM cteSerialNo WHERE ORDERNO < MAX_ORDER_NO
        )
,cteMonthYear AS
        (
            SELECT CAST('2021.01.01' AS DATE) AS Dt
 
            UNION ALL

            SELECT DATEADD(MONTH , 1, Dt) AS Dt
            FROM cteMonthYear
            WHERE DATEADD (MONTH, 1, Dt) < GETDATE()
        )
SELECT
*
FROM
        (
            SELECT
                CODES.CODETYPE,
                YEAR(Dts.Dt) AS 'YEAR', 
                MONTH(Dts.Dt) AS 'MONTH'
            FROM
                ##KK_TBL_CODETYPES AS CODES
                CROSS JOIN cteMonthYear AS Dts
        ) AS CROSSTABLE
        CROSS JOIN cteSerialNo AS cSN

How can i enter (MAX_ORDER_NO) for each variable in this code?

Answer 1

Assuming that the max SerialNo value is based on the existing values in the SerialNo column, you would want to just find all possible combinations up to that SerialNo value and then remove those that have a match in the source data:

-- Define test data
declare @t table(CodeType varchar(2),[Year] int,[Month] int,SerialNo int);
insert into @t values
 ('DP',21,1,1)
,('DP',21,1,2)
,('DP',21,1,3)
--,('DP',21,1,4) -- Missing so should be in Output
,('DP',21,1,5)
,('DP',21,2,1)
,('DP',21,2,2)
,('FB',21,1,1)
,('FB',21,1,2)
,('FB',21,2,1)
,('FB',21,2,2)
--,('FB',21,2,3) -- Missing so should be in Output
,('FB',21,2,4)
;

with m as  -- Get Max SerialNo for each grouping
(
    select CodeType
          ,[Year]
          ,[Month]
          ,max(SerialNo) as MaxSerialNo
    from @t
    group by CodeType
            ,[Year]
            ,[Month]
)
,t as  -- Create a table with 10 rows in
(
    select t
    from(values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as t(t)
)
,n as  -- Self join those 10 rows 5 times to generate a possible 10*10*10*10*10 = 100,000 incrementing numbers using row_number
(
    select top(select max(MaxSerialNo) from m) row_number() over (order by (select null)) as n
    from t,t t2,t t3,t t4,t t5
)
-- Join from the numbers table to the source data to generate all possible SerialNo values up to the Max
select m.CodeType
      ,m.[Year]
      ,m.[Month]
      ,n.n as SerialNo
from n
    left join m
        on n.n <= m.MaxSerialNo

except -- Then exclude any that are in the source data

select CodeType
      ,[Year]
      ,[Month]
      ,SerialNo
from @t

order by CodeType
        ,[Year]
        ,[Month]
        ,SerialNo

Output

CodeType	Year	Month	SerialNo
DP	21	1	4
FB	21	2	3