Is there a way to identify all rows within the same date with data that only occurs on one row?-CodePudding

this one's got me stumped. Am I missing something or do the team need to change the way the data gets dumped into my database?

I'm calculating time spent in "Extended Worktime" but want to exclude all of the users that have been logged in to "Tier 2" on that day.

The issue being that column "Class Name" only populates when users log in and not when they go on "Extended Worktime". My question: is there a query I can script that will identify that Agent is on Tier 2 for that day and exclude that from my report?

If there is a gentle nudge in the right direction would be much appreciated.

ROWID	Agent_First_Name	Type	AgentID	Exno	Duration	LoginDateTime	BreakReason	Class	ClassName
61272	Dave	L	100	3382	0	17/11/2021 08:00:24		10	Tier 1
61273	Dave	W	100		142	17/11/2021 08:07:24	Extended Worktime	0
61274	Dave	W	100		112	17/11/2021 08:15:24	Extended Worktime	0
61275	John	L	320	4201	0	17/11/2021 09:04:24		9	Tier 2
61276	John	W	320		100	17/11/2021 10:13:24	Extended Worktime	0
61277	John	W	320		85	17/11/2021 11:15:24	Extended Worktime	0

CodePudding user response：

You can fill the gaps in ClassName this way:

with cte 
as
(
  select ROW_NUMBER() OVER (PARTITION BY AgentID ORDER BY ROWID) rn,
  * from YourTable
)
select 
  ROWID, Agent_First_Name, [Type], BreakReason, 
  case
    when [Type] = 'L' then ClassName
    else lag(ClassName, rn-1, 0) OVER (PARTITION BY AgentID ORDER BY ROWID)
    END
from cte

This would return:

ROWID	Agent_First_Name	Type	BreakReason	ClassName
61272	Dave	L		Tier 1
61273	Dave	W	Extended Worktime	Tier 1
61274	Dave	W	Extended Worktime	Tier 1
61275	John	L		Tier 2
61276	John	W	Extended Worktime	Tier 2
61277	John	W	Extended Worktime	Tier 2

Once you got ClassName filled, you can use a WHERE ClassName != 'Tier 2' clause or whatever you need:

with cte 
as
(
  select ROW_NUMBER() OVER (PARTITION BY AgentID ORDER BY ROWID) rn,
  * from YourTable
), final
as(
  select 
    ROWID, Agent_First_Name, [Type], BreakReason, 
    case
      when [Type] = 'L' then ClassName
      else lag(ClassName, rn-1, 0) OVER (PARTITION BY AgentID ORDER BY ROWID)
    END ClassName
  from cte
)
select * 
from final
where ClassName != 'Tier 2'

You have a DBFiddle here to play with it: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=56255540560c74b583af2e9b61df0fd9

CodePudding user response：

You can use a conditional windowed count for this

SELECT *
FROM (
    SELECT *,
      Tier2 = COUNT(CASE WHEN t.ClassName = 'Tier 2' THEN 1 END) OVER (PARTITION BY AgentID, CAST(LoginDateTime AS date))
    FROM YourTable t
) t
WHERE t.BreakReason = 'Extended Worktime'
  AND t.Tier2 = 0;

Casting to date means the count is partitioned by each date