Update dates for auditing purposes

Spunny 326 Reputation points
2021-02-02T20:29:38.277+00:00

Hi, We have SQL Server 2016. Our database has auditing table which keeps track of who logged into asp.net application, what they did and when they logged out. I need to send an email to the user if user didn't log into the application for 30 days on 30th day of inactivity. I wrote a query to get all the users whose login date is equal to 30 days. In order to test this, on dev environment I need to set up data so that everyday some user will be having 30th day.

So, I need to take few users whose login date is greater than 30 days and loop through each user and increment login date in such a way data every day an user will be there to send an email. This email should be sent for 10 days. That means I need to set up users for 10 days from today and update their last login date to suit for 30 days.

How can I do that.

Step 1: get the date of 30th day from today backwords (today is 2/2/2021 - Jan 3rd was 30 days ago). That means I need to set up users whose last login date will be 30th day from today.
For example:
User1 - LastLogindate 01/03/2021 (30 days)
User2 - LastLogindate for tomorrow 01/04/2021 (30 days)
So, I think it should be in cursor or loop.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-02-02T21:18:13.797+00:00

    What you describe is simply every user who has not logged in 30-40 days ago should get an email.

    You don't need a loop.
    DECLARE @Bluemchen TABLE (UserName VARCHAR(100), LastLoginDate DATE) 

    INSERT INTO @tbl  VALUES  
('AAAAA','1/3/2021'),  
('BBBBB','2/1/2021'),  
('CCCC','12/15/2020'),  
('DDDD','12/25/2020')  
  
SELECT *  
FROM @tbl  
WHERE DATEDIFF(DAY,LastLoginDate,GETDATE()) BETWEEN 30 AND 40
    1 person found this answer helpful.
  2. MelissaMa-MSFT 24,176 Reputation points
    2021-02-03T03:06:59.533+00:00

    Hi @Spunny ,

    Welcome to Microsoft Q&A!

    Please refer below and check whether it is helpful to you.

    If it is not working, please provide the exact expected output and more details about your requirement. 

    DECLARE @dt date = DATEADD(DAY,-30, CONVERT(Date,GETDATE()));   
  
;with cte as (  
SELECT LoginUser, MAX(LoginDate) as LoginDate,ROW_NUMBER() OVER( ORDER BY Max(LoginDate) desc) RN  
FROM @Audit  
WHERE type = 'Login'  
GROUP BY LoginUser)  
select LoginUser,dateadd(DAY,datediff(DAY,LoginDate,@dt)+rn-1,LoginDate) LoginDate  
from cte

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. Spunny 326 Reputation points
    2021-02-02T23:25:06.947+00:00

    Hi YitzhakKhabinsky,
    Thank you for the response. Usually I give DDL statement. here are the details:
    DECLARE @Audit TABLE
    (
    ID smallint,
    type varchar(100),
    LoginDate datetime,
    LoginUser varchar(7)

    )

    INSERT INTO @Audit (ID, type, LoginUser, LoginDate)
    SELECT 1, 'Login', 'JQUA123', '2020-10-06 10:14:11.570'
    UNION
    SELECT 1, 'Logout - Session End', 'JQUA123', '2020-10-06 10:14:11.663'
    UNION
    SELECT 1, 'Login', 'JQUA123', '2020-10-06 10:36:08.460'
    UNION
    SELECT 1, 'Table 1 column1 update', 'JQUA123', '2020-10-06 10:37:12.000'
    UNION
    SELECT 1, 'Table 1 column2 update', 'JQUA123', '2020-10-06 10:37:12.000'
    UNION
    SELECT 1, 'Table 1 column3 update', 'JQUA123', '2020-10-06 10:37:12.000'
    UNION
    SELECT 1, 'Table 1 column4 update' , 'JQUA123', '2020-10-06 10:37:49.183'
    UNION
    SELECT 1, 'Table 1 column5 update', 'JQUA123', '2020-10-06 10:37:49.183'
    UNION
    SELECT 1, 'Table 1 column6 update', 'JQUA123', '2020-10-06 10:37:49.183'
    UNION
    SELECT 1, 'Table 1 column7 update', 'JQUA123', '2020-10-06 10:38:27.627'
    UNION
    SELECT 1, 'Table 1 column8 update', 'JQUA123', '2020-10-06 10:38:27.627'
    UNION
    SELECT 1, 'Table 1 column9 Insert', 'JQUA123', '2020-10-06 10:38:27.627'
    UNION
    SELECT 1, 'Logout - Session End', 'JQUA123', '2020-10-06 10:53:43.363'
    UNION
    SELECT 1, 'Login', 'JQUA123', '2020-10-07 10:58:38.403'
    UNION
    SELECT 1, 'Logout - Session End', 'JQUA123', '2020-10-07 10:58:38.497'
    UNION
    SELECT 2, 'Login', 'ABCD123', '2020-11-09 11:53:38.410'
    UNION
    SELECT 2, 'Table 1 column1 insert', 'ABCD123', '2020-11-09 11:55:07.783'
    UNION
    SELECT 2, 'Table 1 column2 insert', 'ABCD123', '2020-11-09 11:55:07.783'
    UNION
    SELECT 2, 'Table 1 column3 insert', 'ABCD123', '2020-11-09 11:55:07.783'
    UNION
    SELECT 2, 'Table 1 column4 insert' , 'ABCD123', '2020-11-09 11:55:07.783'
    UNION
    SELECT 2, 'Logout - Session End', 'ABCD123', '2020-11-09 11:55:07.785'
    UNION
    SELECT 3, 'Login', 'EFGH123', '2020-11-15 10:58:38.403'
    UNION
    SELECT 3, 'Logout - Session End', 'EFGH123', '2020-11-15 10:59:43.497'

    --select * from @Audit

    SELECT LoginUser, MAX(LoginDate) as LoginDate
    FROM @Audit
    WHERE type = 'Login'
    GROUP BY LoginUser
    ORDER BY Max(LoginDate) desc

    --THis gives me unique user with max login date. In our example JQUA123 last login was on 7th.
    Above select statement gives 3 rows.
    LoginUser LoginDate
    EFGH123 2020-11-15 10:58:38.403
    ABCD123 2020-11-09 11:53:38.410
    JQUA123 2020-10-07 10:58:38.403

    -- now get the date of 30 days ago from current day
    DECLARE @dt date = DATEADD(DAY,-30, CONVERT(Date,GETDATE())); -- which is 01/03/2021

    -- I need a query that sets or updates login date for each row.
    -- In this EFGH123 Login date will be updated with 2020-01-03 10:58:38.403 -- see only date part is updated to 30 days ago date
    -- next row ABCD123 Login date should to update with a date which is 30 days to tomorrow. So, tomorrow another email will be sent to this user.
    -- next row JQUA123 login date should be updated with a date which is 30 days from day after tomorrow
    ---So, that way every day there will a user with logindate of 30 days from that current day.
    Output
    LoginUser LoginDate New Update to LoginDate
    EFGH123 2021-01-03 10:58:38.403 30 days from today (so update to 01/03/2021)
    ABCD123 Don’t know what date it will be 11:53:38.410 Tomorrow this one should be 30 days (guessing 01/04/2021)
    JQUA123 Don’t know what date it will be 10:58:38.403 Day after tomorrow this one should be 30 days

    Thank You,