Sample table (customer) have the following data,

As per below query, will get the similar customer with rowNumber(). Think the problem is the the comparison of data set with createdDate.

select
    RecID, createdDate, UserID,
    row_number() over (partition by UserID order by UserID) as "ROWNUMBER",
    toCount
from (
    select
       *,
       (case when datediff(day, lag(createdDate,50,createdDate) over (partition by UserID order by UserID), createdDate) <= 1 
             then 'true'
             else 'false' 
        end) as toCount
    from customer
) t

The problem: All users should receive a flag (count), who had not registered in the last 50 days. like this:

CodePudding user response：

Producing your demo data as an object is a great way to share it.

DECLARE @customers TABLE (RecID INT IDENTITY, CreatedDate DATE, UserID NVARCHAR(10))
INSERT INTO @customers (CreatedDate, UserID) VALUES 
('10-25-2022', 'User01'),('10-14-2022', 'User01'),
('01-25-2020', 'User01'),('10-19-2022', 'User02')

I'm not sure if you intended all the rows for User01 to have a false flag or if it was intentional that only the first row have it.

This produces the result set you asked for, but if you did want all the rows for User01 to be false you can just remove the third line AND ROW_NUMBER... from the query.

SELECT RecID, CreatedDate, UserID, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY CreatedDate) AS RowNumber, 
CASE WHEN MIN(CreatedDate) OVER (PARTITION BY UserID ORDER BY CreatedDate) < DATEADD(DAY,-50,CURRENT_TIMESTAMP) 
AND ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY CreatedDate) = 1
     THEN CAST(0 AS BIT)
     ELSE CAST(1 AS BIT)
 END AS ToCount
  FROM @customers
 ORDER BY RecID

RecID   CreatedDate UserID  RowNumber   ToCount
-----------------------------------------------
1       2022-10-25  User01  3           1
2       2022-10-14  User01  2           1
3       2020-01-25  User01  1           0
4       2022-10-19  User02  1           1

CodePudding user response：

First, here's a query that I think gives you what you're looking for (let me know):

select RecId
     , createdDate
     , UserId
     , row_number() over (partition by UserId order by createdDate desc) as ROWNUMBER
     , case
          when datediff(day,lag(createdDate) over (partition by UserId order by createdDate),createdDate) <= 50 then 'false'
          else 'true'
       end as toCount
  from customer
order by RecId;

A couple of observations:

row_number() over (partition by UserID order by UserID) as "ROWNUMBER" UserID is not distinct and doesn't make a good candidate for the order by in this row_number function. It's good for partition, not for order by.

lag(createdDate,50,createdDate) That 50 in there is an offset, so you're asking to skip fifty rows, not 50 days.