First instance 0 rest other become 1 in SQL Server-CodePudding

I have a scenario where IF ID, Code and InsertDate is same and if it is 1st entry then it will be 0 else 1.

Demo data:

CREATE TABLE #test 
(
     ID int,
     code int,
     InsertDate datetime2
)

Insert into #test values (1,1,'2019-09-17 03:19:00.0000000')
Insert into #test values (1,1,'2019-09-17 03:19:00.0000000')
Insert into #test values (1,1,'2019-09-17 03:19:00.0000000')
Insert into #test values (2,1,'2019-09-17 03:19:00.0000000')
Insert into #test values (3,1,'2019-09-17 03:19:00.0000000')

Expected o/p

ID  code    InsertDate              flag   
-----------------------------------------
1    1  2019-09-17 03:19:00.0000000  0     
1    1  2019-09-17 03:19:00.0000000  1     
1    1  2019-09-17 03:19:00.0000000  1     
2    1  2019-09-17 03:19:00.0000000  0     
3    1  2019-09-17 03:19:00.0000000  0

What I tried

SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY ID, Code, Insertdate ORDER BY InsertDate) flag 
FROM #test

With this I got the initial logic but what to do next I need help.

CodePudding user response：

A simple CASE expression may help:

SELECT
   *,
   CASE 
      WHEN ROW_NUMBER() OVER (PARTITION BY ID, Code, Insertdate ORDER BY InsertDate) = 1 THEN 0 
      ELSE 1 
   END AS Flag
FROM #test

CodePudding user response：

Different route, you could use SIGN, which returns -1 for negative numbers, 0 for 0, and 1 for positive numbers:

SELECT ID,
       code,
       InsertDate,
       SIGN(ROW_NUMBER() OVER (PARTITION BY ID, code, InsertDate ORDER BY InsertDate) - 1) AS flag
FROM #test;

db<>fiddle

CodePudding user response：

This should do it:

Create table #test (ID int,code int,InsertDate datetime2)

Insert into #test values (1,1,'2019-09-17 03:19:00.0000000')
Insert into #test values (1,1,'2019-09-17 03:19:00.0000000')
Insert into #test values (1,1,'2019-09-17 03:19:00.0000000')
Insert into #test values (2,1,'2019-09-17 03:19:00.0000000')
Insert into #test values (3,1,'2019-09-17 03:19:00.0000000')

with cte as (
    SELECT
        ROW_NUMBER() over (partition by ID,Code,Insertdate order by InsertDate) as RowNumber,
        ID,
        code,
        InsertDate
    FROM #test
)
SELECT 
    ID,
    code,
    InsertDate, 
    IIF(RowNumber = 1,0,1) as flag
FROM cte

Also here is shorter version you can use:

SELECT
    ID,
    code,
    InsertDate,
    IIF(ROW_NUMBER() over (partition by ID,Code,Insertdate order by InsertDate) = 1,0,1) as Flag
FROM #test