SQL Server How to define a view on a table that has a date column to add a new column to indicate that tuple wasn't present at previous dates

d8tt03nd 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(115)

Say schema is

EntryDate
,Dim1
,Dim2
,Dim3

And I want to create a view

EntryDate
,Dim1
,Dim2
,Dim3
,IsNew

Where IsNew is true if that tuple of Dim1,Dim2,Dim3 hadn't existed for earlier EntryDate.

Not concerned about performance as I will eventually regenerate the base table. But I want to test some business logic beforehand using a view.

sql-server

来源：https://stackoverflow.com/questions/76408314/how-to-define-a-view-on-a-table-that-has-a-date-column-to-add-a-new-column-to-in

1条答案

按热度按时间

k3fezbri1#

One method would be to use a ROW_NUMBER ; if the value is 1 it's the first row and is new, if not it isn't new.

SELECT EntryDate,
       Dim1,
       Dim2,
       Dim3,
       CONVERT(bit,CASE ROW_NUMBER() OVER (PARTITION BY Dim1, Dim2, Dim3 ORDER BY EntryDate ASC) WHEN 1 THEN 1
                        ELSE 0
                   END) AS IsNew
FROM dbo.YourTable;

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server How to define a view on a table that has a date column to add a new column to indicate that tuple wasn't present at previous dates

1条答案

相关问题

热门标签

最新问答