Production Ticket(ST) #11617
Updated by Muhammad Arif 5 months ago
total missing collection
select CAST(RIGHT(Max(x.col_no), 6) AS INT) - Max(row_num) as total_missing, Max(col_no) as col_no, Max(row_num) as row_num from (SELECT SUBSTRING(col_no, 1, LEN(col_no) - 7) AS col_no,
ROW_NUMBER() OVER (PARTITION BY created_by ORDER BY col_no) AS row_num, created_by
FROM txn_mobile_collection_header
WHERE org_id = 'snekku' and created_by <>'INTEGRATION_API') x group by x.created_by having CAST(RIGHT(Max(x.col_no), 6) AS INT) - Max(row_num) >0
By row number & running number comparison
select * from (SELECT SUBSTRING(col_no, 1, LEN(col_no) - 7) AS col_no,
ROW_NUMBER() OVER (PARTITION BY created_by ORDER BY col_no) AS row_num, created_by
FROM txn_mobile_collection_header
WHERE org_id = 'snekku' and created_by <>'INTEGRATION_API') x
select CAST(RIGHT(Max(x.col_no), 6) AS INT) - Max(row_num) as total_missing, Max(col_no) as col_no, Max(row_num) as row_num from (SELECT SUBSTRING(col_no, 1, LEN(col_no) - 7) AS col_no,
ROW_NUMBER() OVER (PARTITION BY created_by ORDER BY col_no) AS row_num, created_by
FROM txn_mobile_collection_header
WHERE org_id = 'snekku' and created_by <>'INTEGRATION_API') x group by x.created_by having CAST(RIGHT(Max(x.col_no), 6) AS INT) - Max(row_num) >0
By row number & running number comparison
select * from (SELECT SUBSTRING(col_no, 1, LEN(col_no) - 7) AS col_no,
ROW_NUMBER() OVER (PARTITION BY created_by ORDER BY col_no) AS row_num, created_by
FROM txn_mobile_collection_header
WHERE org_id = 'snekku' and created_by <>'INTEGRATION_API') x