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

user_12704989 0
Viewed 40

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
sql-server database
2 Answers

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

user_148689970

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

user_22328680
Related