How can i add flag on every new record?-CodePudding

i have this view has been update every single day and i want to add a new column called [Flag] to indicate whether if the record is new or existing record,(new is 1,old is 0) for eg: the order is from top to bottom, started from 01/01/2020

old view:

ID	Date
1	01/01/2020
2	01/01/2020
3	01/02/2020
1	01/02/2020
2	01/03/2020

new view:

ID	Flag	Date
1	1	01/01/2020
2	1	01/01/2020
3	1	01/02/2020
1	0	01/02/2020
2	0	01/03/2020
4	1	01/03/2020

I tried to use self-join but the result does not showing correctly, So, im wandering how should i do this? Much Appreciated!!!

CodePudding user response：

You can compute [flag] with a window function

select id, [date], sign(row_number() over(patition by id order by [date] desc) - 1) [flag]
from yourtable

Not sure if DESC is required, I follow your sample data rather then comments. You may want to revert it to ASC.

CodePudding user response：

You could check if the date matches the max(date) per grouping of your choice.

create view some_view as

select *, case when date=max(date) over (partition by id, category) then 1 else 0 end as flag
from yourtable;

You also might want to use a different column name for date, which is a reserved keyword

CodePudding user response：

You can use FIRST_VALUE to create a flag column that returns the first Date and then a CASE statement to compare it to your current Date.

If your current row Date value matches the first Date it finds (by ID, category, whatever else you want to group by), it'll return the flag with 1, otherwise it'll return 0.

Then, ALTER your view to include the flag logic (be sure to get your views current logic, appending the flag column to it).

ALTER VIEW sample_view -- Change to your view name here
AS  
-- Write your query logic here
SELECT *,
  CASE WHEN "Date" = FIRST_VALUE("Date") OVER (PARTITION BY ID, category ORDER BY "Date")
    THEN 1
    ELSE 0
  END AS flag
FROM sample_table -- Change to your table name here
GO

db<>fiddle here.

Result:

ID	Flag	Date
1	1	01/01/2020
2	1	01/01/2020
3	1	01/02/2020
1	0	01/02/2020
2	0	01/03/2020
4	1	01/03/2020

Also, as noted previously, your column Date is a Reserved Word. It is advised to change it to something else.