How to set in SQL a Flag based on days range

Question

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

score 0 · Answer 1 · answered Nov 04 '22 at 15:03

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

Based on the original query `lag` function, it looks like the 50 day threshold is within the createdDate column, not a comparison to a current time. — Brian Stork, Nov 04 '22 at 15:13

score 0 · Accepted Answer · answered Nov 04 '22 at 15:07

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.

Because `UserId` is not unique, anytime you use `order by UserId`, you're setting up a non-deterministic result. — Brian Stork, Nov 04 '22 at 15:12
Thank you very much! This is exactly what I needed. Good hint with the lag() :) Have a good day! — KING, Nov 06 '22 at 13:53

How to set in SQL a Flag based on days range

2 Answers2