sqlsql-serversubqueryflagssql-scripts

How to set in SQL a Flag based on days range


Sample table (customer) have the following data,

RecID createdDate UserID ROWNUMBER toCount
1 10-25-2022 User01 1 true
2 10-14-2022 User01 2 true
3 01-25-2020 User01 3 true
4 10-19-2022 User02 1 true

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:

RecID createdDate UserID ROWNUMBER toCount
1 10-25-2022 User01 1 false
2 10-14-2022 User01 2 true
3 01-25-2020 User01 3 true
4 10-19-2022 User02 1 true

Solution

  • 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.