I need help generating a unique row number based on column values.
Input data
I need to generate a row number to group records based on the transaction type = CNCL. What I mean is
- rows 1-3 are group 1
- rows 4-5 are group 2
Expected op
Script to generate sample date
CREATE TABLE #tmpTable (
policy_number varchar(50),
transaction_type_code varchar(10),
gross_premium decimal(10, 2),
transaction_created_timestamp datetime2)
INSERT INTO #tmpTable (policy_number, transaction_type_code, gross_premium, transaction_created_timestamp)
SELECT 'UHC2000003082', 'NB', 212.91, '2024-03-04 17:05:34.0000000' UNION
SELECT 'UHC2000003082', 'ADJ', 212.91, '2024-03-05 17:05:34.0000000' UNION
SELECT 'UHC2000003082', 'CNCL', -211.75, '2024-03-05 17:07:39.0000000' UNION
SELECT 'UHC2000003082', 'RE', 211.75, '2024-03-14 14:16:14.0000000' UNION
SELECT 'UHC2000003082', 'CNCL', -199.50, '2024-03-14 16:47:32.0000000' UNION
SELECT 'UHC2000003000', 'Nb', -199.50, '2024-03-11 16:47:32.0000000' union
SELECT 'UHC2000003000', 'CNCL', -199.50, '2024-03-14 16:47:32.0000000'