Query to get Status

Transact-SQL
1

I have a table of this format which has Employee ID, Snapshot Date and 'Opt In' status (Opt in status is response from Employees whether they want to receive text alerts or not), they can change their response and each day status get snapshotted in table like this. If they don't change their status will be same as previous day. To begin with they will have response of 'TRUE'.

I am looking for ways how to write query to see Employee IDs who chosen to opt in to receive texts atleast twice. e.g. Employee 102 should be part of this list where he changed his status from TRUE>FALSE>TRUE

Table format

Employee ID Snapshot Date Opt In Status
101 12-Jul FALSE
102 12-Jul TRUE
103 12-Jul FALSE
104 12-Jul TRUE
105 12-Jul TRUE
101 13-Jul FALSE
102 13-Jul FALSE
103 13-Jul FALSE
104 13-Jul TRUE
105 13-Jul TRUE
101 14-Jul FALSE
102 14-Jul TRUE
103 14-Jul FALSE
104 14-Jul TRUE
105 14-Jul TRUE
2

You can use the function ROW_NUMBER() to get what you want

Something like this:

;WITH LastOptInEmployee AS
(
SELECT
[EmployeeID],
[SnapShot Date],
[Opt In Status],
ROW_NUMBER () OVER (Partition BY [Employee ID], ORDER BY [Snapshot Date] DESC) AS RowNumber
FROM
[a Table]
) SELECT * FROM
LastOptInEmployee
WHERE RowNumber=1;

ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs

3

@RogierPronk Thanks for checking this. But that gives only latest status of all the employees. I was looking for Employees who opted in alteast twice.

In this example Employee=102 changed his status from TRUE>FALSE>TRUE , so need to find a way how to get such employee list using query.

4

I did something like this, not sure if this can be done better

select eid,count(distinct (current_ind||prev_ind||next_ind)) from
(select eid, snapshot_date,opt_ind current_ind, lag(opt_ind,1) over(partition by eid order by snapshot_date asc) as prev_ind,
lead(opt_ind,1) over(partition by eid order by snapshot_date asc) as next_ind from emp
)
where prev_ind is not null and next_ind is not null
group by eid
having count(distinct (current_ind||prev_ind||next_ind))>2

6

hi

this is a different way of doing it ... hope you like it ... fingers crossed its better

create sample data

declare @temp table(emp_id int , SnapShot_Date date , Opt_in_Status varchar(10))

insert into @temp select '101','12-Jul-2022','FALSE'
insert into @temp select '102','12-Jul-2022','TRUE'
insert into @temp select '103','12-Jul-2022','FALSE'
insert into @temp select '104','12-Jul-2022','TRUE'
insert into @temp select '105','12-Jul-2022','TRUE'
insert into @temp select '101','13-Jul-2022','FALSE'
insert into @temp select '102','13-Jul-2022','FALSE'
insert into @temp select '103','13-Jul-2022','FALSE'
insert into @temp select '104','13-Jul-2022','TRUE'
insert into @temp select '105','13-Jul-2022','TRUE'
insert into @temp select '101','14-Jul-2022','FALSE'
insert into @temp select '102','14-Jul-2022','TRUE'
insert into @temp select '103','14-Jul-2022','FALSE'
insert into @temp select '104','14-Jul-2022','TRUE'
insert into @temp select '105','14-Jul-2022','TRUE'

select *from @temp order by emp_id,SnapShot_Date

; with cte as 
( select *,	ROW_NUMBER() over(partition by emp_id order by emp_id,snapshot_date) as rn from  @temp )
select 
   * 
from 
  cte a join cte b on a.rn+1 = b.rn join cte c on a.rn+2 =c.rn
where 
   a.Opt_in_Status <> b.Opt_in_Status and a.emp_id = b.emp_id 
   and  
   b.Opt_in_Status <> c.Opt_in_Status and b.emp_id = c.emp_id

image
image895×515 8.38 KB