-5

I have two columns ID and FLAG as :

ID  FLAG
1   Y
1   N
1   Y
1   N
1   N
1   N
1   N
1   Y
1   N
2   N
2   Y
2   N
2   N
2   Y
2   Y
2   N

Required Output:

ID  FLAG    REQ_COL 
1   Y       null
1   N       1
1   Y       null
1   N       1
1   N       2
1   N       3
1   N       4
1   Y       null
1   N       1
2   N       null
2   Y       null
2   N       1
2   N       2
2   Y       null
2   Y       null
2   N       1

Logic for required column :

  1. For first occurence of ID , REQ_COL is null
  2. If FLAG=Y then REQ_COL is null
  3. IF previous value of FLAG is Y and there are consecutive N in FLAG , then the REQ_COL values are 1,2,3,4, 5.....
  4. Else Null

I am trying to apply conditional row_number but not getting idea how to proceed. Please help.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Anuranjan Chauhan
  • 1
  • 2
  • 1
    Can you please edit the question with the stuff that what you're trying to apply... – Barbaros Özhan Aug 29 '22 at 08:40
  • What do "first occurence" _[sic]_ and "previous value" mean? The rows in a table have no order and you don't appear to have a column that provides a meaningful order. – HABO Aug 29 '22 at 18:14

1 Answers1

1

This might give you the result you are searching for...

Data Preparation

DECLARE @SOURCE_TABLE AS TABLE (realid int identity (1,1), ID int, FLAG CHAR(1))
INSERT INTO @SOURCE_TABLE (ID, FLAG)
VALUES (1, 'Y'),(1, 'N'),(1, 'Y'),(1, 'N'),(1, 'N'),(1, 'N'),
       (1, 'N'),(1, 'Y'),(1, 'N'),(2, 'N'),(2, 'Y'),(2, 'N'),
       (2, 'N'),(2, 'Y'),(2, 'Y'),(2, 'N')

Actual Select

SELECT ST2.ID, ST2.FLAG,
NULLIF(NULLIF (CHARINDEX('Y', REVERSE(
(
    SELECT ST1.FLAG AS [text()]
    FROM @SOURCE_TABLE ST1
    WHERE ST1.ID = ST2.ID AND ST1.realid <= ST2.realid
    ORDER BY ST1.realid
    FOR XML PATH (''), TYPE
).value('text()[1]','VARCHAR(MAX)')
)), 1) - 1, -1) AS [REQ_COL]
FROM @SOURCE_TABLE AS ST2
ORDER BY ST2.realid
Helio
  • 621
  • 1
  • 4
  • 24