CODEWITHSUNDEEP
sqldateselecttimecount
Radek Tarant
Radek Tarant

Reputation: 227

SQL: Select time period COUNT() of employees

Good day,

The "SELECT" below shows the number of employees and their arrival and departure times.

In this data, I need to find out the total number of employees (COUNT) at each time (I do not need to deal with seconds). How many people worked at the same time in a given period of time.

Example:

SELECT 
    linka.xLinka
    , linka.xDoklad
    , zam.xPracovnik
    , FORMAT(zam.xCasOd, 'HH:mm') as cas_od
    , FORMAT(zam.xCasDo, 'HH:mm') as cas_do
FROM [K2CA_CA].[dbo].[_OV_Data01] as linka
LEFT OUTER JOIN dbo._OV_Data03 as zam ON zam.xLinka = linka.xLinka and zam.xDoklad = linka.xDoklad
WHERE linka.xRok = 2021
    --AND linka.xDen  >= '2021-10-20' and linka.xDen <= '2021-10-26'
    AND (zam.xPozice like '%Bale%' or zam.xPozice like '%Plnič%')

Result:

Line                   Document       Employee            Arrival   Departure
--------------------------------------------------------------------------------------------
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   Vaněček Karel       |   22:00 06:00
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   Levá Pavlína        |   23:30 06:00
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   Škorňová Alena      |   01:30 06:00
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   Trofimov Jurii      |   01:30 06:00
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   Besahina Olena      |   01:30 06:00
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   Havel Zdeněk        |   01:30 06:00
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   Schmiedlová Ilona   |   01:30 06:00
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   Kulenová Jinřiška   |   01:30 06:00
 --------------------------------------------------------------------------------------------

Result, which i need earn:

Line                   Document       COUNT(*)            Arrival   Departure
--------------------------------------------------------------------------------------------
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   1               |   22:00 23:30 (22 - 23:30) work only one employee)
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   2               |   23:30 01:30 (23:30 - 1:30 works two employees - first arived at 22:00, second at 23:30))
 | Balíčky, výroba nových NV   |   VL/2021/4072   |   8               |   01:30 06:00 (8 employes work here from 1:30 to 06:00)
 --------------------------------------------------------------------------------------------

How to get the result? Or how do I get the timeline definition? For example, that employee 1 was alone from and to and another employee was alone from and to and etc. Thank you in advance.

Upvotes: 0

Views: 206

Answers (1)

Tom&#225;š Z&#225;lusk&#253;
Tom&#225;š Z&#225;lusk&#253;

Reputation: 12000

Your question is more likely to be answered if you

  • specify your db vendor (instead of unnecessary select tag)
  • provide minimalistic example (no underlying tables or unrelated where conditions)
  • provide db fiddle
  • bonus: anonymize sensitive data

To answer itself:

  1. You should first improve representation of times since string "HH:mm" format does not give a clue how to sort 01:30 after 23:30. Probably you want datetime type truncated or rounded to minutes. Let's name it cas_od, cas_do like in your code snippet.

  2. Then define ordered set of all ranges:

with original as (
  ... your select with cas_od, cas_do and other preprocessed data
), times(cas_od,cas_do) as (
  select cas, lag(cas) over (order by cas) 
  from (
    select distinct cas_od as cas from original
    union
    select distinct cas_do as cas from original
  )
)
  1. Then count number of working employes for each range (there might be boundary errors but I hope you get the idea):
select t.cas_od, t.cas_do
     , (select count(*)
        from original o
        where o.cas_od<=t.cas_od and o.cas_do>=t.cas_do
       )
from times t

Upvotes: 1

Related Questions