Bdtjtk

We are using below view to generate identity column values using the ROW_NUMBER() function, but how would I change this view to return incremental numbers in identity column, for example if I run view today it will return 100 records and the identity column value is from 1 to 100, if I run it tomorrow, it should return 101 to whatever the records it has in that day.greatly appreciate your help.

CREATE VIEW [dbo].[Data_Old] 

AS

    SELECT 

        ROW_NUMBER() OVER (ORDER BY event_time) AS id,

        event_time, loc, c, d, e, f, Expr1

    FROM

        (SELECT 

             FORMAT(h.ACTIVITYDT, 'yyyy MMM dd hh:mm:ss') AS event_time, 

             ISNULL(h.LOCATION, N'17.51.12.24') loc, 

             ' LEEF:1.0|Manage|WorkSite|9.5|' + CAST(NEWID() AS varchar(50)) c, 

             '|devTime=' + FORMAT(h.ACTIVITYDT, 'MMM dd hh:mm:ss') d, 

             '|usrName=' + ISNULL(h.USER, N'{null}') + '|fullName=' + ISNULL(u.FULLNAME, N'{null}') e, 

             '|activity=' + ISNULL(h.ACTIVITYDT, N'{null}') + '|library=COLLECTIONS' + '|Num=' + CAST(h.NUM AS varchar) + '|version=' + CAST(h.VERSION AS varchar) f, 

             '|pagesPrinted=' + CAST(h.PAGES_PRINTED AS varchar) + '|FTI=' + ISNULL(CAST(d.CBOOL2 AS varchar), N'False') 

                              + '|keyData=case:' + ISNULL(d.C5ALIAS, N'{null}') + ';serialNo:' + ISNULL(d.C16ALIAS, N'{null}') 

                              + ';documentNo:' + ISNULL(CAST(d.CDBL1 AS varchar), N'') AS Expr1

         FROM 

             dbo.HISTORY AS h 

         LEFT OUTER JOIN 

             dbo.MASTERS AS d WITH (nolock) ON d.NUM = h.NUM AND d.VERSION = h.VERSION 

         LEFT OUTER JOIN 

             dbo.USERS AS u WITH (nolock) ON h.USER = u.USERID

         WHERE 

             (h.ACTIVITYDT >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) 

              AND h.ACTIVITYDT < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) 

             AND (h.ACTIVITYDT >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 

                  AND h.ACTIVITYDT < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) 

             AND (h.ACTIVITYDT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) 

                  AND h.ACTIVITYDT < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)) 



         UNION ALL



         SELECT  

             FORMAT(ACTIONDT, 'yyyy MMM dd hh:mm:ss') AS event_time, 

             ' 127.55.82.124' b, 

             ' LEEF:1.0|Manage|WorkSite|9.5|' + CONVERT(VARCHAR, SID) + CONVERT(VARCHAR, DATEDIFF(SECOND, ACTIONDT, '2017-01-01')) c,

             '|devTime=' + FORMAT(ACTIONDT, 'MMM dd hh:mm:ss') d,

             '|usrName=' + ISNULL(USERID, '{null}') e,

             '|activity=' + CASE ACTIONSID       

                               WHEN 1 THEN 'Successful Login' 

                               WHEN 2 THEN 'Unsuccessful Login' 

                               WHEN 3 THEN 'Impersonated Login' 

                               WHEN 4 THEN 'Impersonated Logoff' 

                            END f, 

             '|library=COLLECTIONS' AS LEEF

         FROM 

             dbo.HISTORY

         WHERE 

             (ACTIONDT >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) 

              AND ACTIONDT < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) 

             AND (ACTIONDT >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 

                  AND ACTIONDT < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) 

             AND (ACTIONDT >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) 

                  AND ACTIONDT < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)) 

        ) AS MyResult

Generate the ROW_NUMBER() over the entire table in a CTE without a WHERE clause.

Then to look at only today's data, select from the CTE and use a WHERE clause.

Your current code applies the WHERE clause first (in the derived table) and then uses ROW_NUMBER() over those filtered results, so the Row Number will always start at 1. You need to do the opposite.