Вопрос по cursor, sql-server – Могу ли я сделать это в функции SQL без курсора?

4

Я работаю над базой данных расписаний. Проще говоря, таблица TimesheetEntries имеет четыре столбца

ID int (identity, 1, 1)
StaffID int
ClockedIn datetime
ClockedOut datetime

Меня попросили написать отчет, чтобы показать посещаемость персонала по диапазону дат. Пользователь вводит дату, а в отчете выводится время включения и выключения всех присутствующих сотрудников, а также их продолжительность на месте.

Тем не менее, и именно здесь это становится непростым делом, сотрудники иногда вынуждены покидать сайт на короткие промежутки времени, и отчет должен игнорировать их (когда они покидают сайт менее чем за 2 часа).

Итак, давайте предположим, что следующие записи

ID  StaffID  ClockedIn    ClockedOut
1   4        0900         1200
2   4        1330         1730
3   5        0900         1200
4   5        1409         1730
5   4        1830         1930

Выходные данные отчетаSHOULD быть

StaffID  ClockedIn    ClockedOut
4        0900         1930
5        0900         1200     
5        1409         1730  

Есть ли способ сделать это без курсора или даже курсора, вложенного в курсор (где я сейчас нахожусь!)? Мы здесь не говорим об огромных наборах данных, и производительность не является действительно проблемой (это отчет, а не производственная система), но мне действительно не нравятся курсоры, если я могу их избежать.

Спасибо

Эдвард

Можете ли вы показать свой код курсора? Может быть, мы можем помочь вам улучшить его, чтобы у вас было меньше проблем с использованием курсоров в целом. Я думаю, что есть довольно распространенный страх / ненависть к курсорам, и они действительно имеют свое место (как я обсуждал с Джереми ниже). Я, конечно, считаю, что ваш вложенный курсор может быть выполнен без вложенности. Aaron Bertrand

Ваш Ответ

5   ответов
2

что есть менее сложные способы сделать это, но я смог осуществить это с помощью пары CTE:

declare @TimeSheetEntries table
    (
    ID int identity not null primary key,
    StaffID int not null,
    ClockedIn datetime not null,
    ClockedOut datetime not null
    );

insert into @TimeSheetEntries
    (
    StaffID,
    ClockedIn,
    ClockedOut
    )
select
    4,
    '2012-01-01 09:00:00',
    '2012-01-01 12:00:00'
union all select
    4,
    '2012-01-01 13:30:00',
    '2012-01-01 17:30:00'
union all select
    5,
    '2012-01-01 09:00:00',
    '2012-01-01 12:00:00'
union all select
    5,
    '2012-01-01 14:09:00',
    '2012-01-01 17:30:00'
union all select 
    4, 
    '2012-01-01 18:30:00', 
    '2012-01-01 19:30:00'       
;
with MultiCheckins as (
    select distinct
        StaffID,
        cast(cast(cast(ClockedIn as float) as int) as datetime) as TimeSheetDate,
        rank() over (
            partition by StaffID, 
            cast(cast(cast(ClockedIn as float) as int) as datetime)
            order by ClockedIn
            ) as ordinal,
        ClockedIn,
        ClockedOut
    from @TimeSheetEntries
), Organized as
(
select
    row_number() over (
        order by
            mc.StaffID,
            mc.TimeSheetDate,
            mc.ClockedIn,
            mc.ClockedOut
            ) as RowID,
    mc.StaffID,
    mc.TimeSheetDate,
    case
        when datediff(hour, coalesce(mc3.ClockedOut, mc.ClockedIn), mc.ClockedIn) >= 2
            then mc.ClockedIn 
        else coalesce(mc3.ClockedIn, mc.ClockedIn)
        end as ClockedIn,
    case 
        when datediff(hour, mc.ClockedOut, coalesce(mc2.ClockedIn, mc.ClockedOut)) < 2
            then coalesce(mc2.ClockedOut, mc.ClockedOut)
        else mc.ClockedOut
        end as ClockedOut
from
    MultiCheckins as mc
left outer join
    MultiCheckIns as mc3
        on mc3.StaffID = mc.StaffID
        and mc3.TimeSheetDate = mc.TimeSheetDate
        and mc3.ordinal =  mc.ordinal - 1
left outer join 
    MultiCheckIns as mc2
        on mc2.StaffID = mc.StaffID
        and mc2.TimeSheetDate = mc.TimeSheetDate
        and mc2.ordinal = mc.ordinal + 1
)
select distinct
    o.StaffID,
    o.ClockedIn,
    o.ClockedOut
from Organized as o
where
    not exists (
        select null from Organized as o2
        where o2.RowID <> o.RowID
        and o2.StaffID = o.StaffID
        and 
            (
            o.ClockedIn between o2.ClockedIn and o2.ClockedOut
            and o.ClockedOut between o2.ClockedIn and o2.ClockedOut
            )
        )
Ну, это было за пределами ваших требований, но здесь вы идете.
Это, по-видимому, не относится к сотруднику, который отключается более одного раза в течение короткого периода времени, например обед и ужин.
+1 молодец Но я думаю, что курсор проще. :-)
@ user92546: как это? он возвращает те же результаты, которые вы хотели увидеть. Пожалуйста, опишите ситуацию, которую он не охватывает.
Попробуйте добавитьunion all select 4, '2012-01-01 18:30:00', '2012-01-01 19:30:00' к образцу данных.
0

что вы можете сделать это довольно легко, просто вернувшись к себе и сыграв одноразовый матч. Ниже приведена не полная реализация, а скорее подтверждение концепции:

create table #TimeSheetEntries 
    ( 
    ID int identity not null primary key, 
    StaffID int not null, 
    ClockedIn datetime not null, 
    ClockedOut datetime not null 
    ); 

insert into #TimeSheetEntries 
    ( 
    StaffID, 
    ClockedIn, 
    ClockedOut 
    ) 
select 
    4, 
    '2012-01-01 09:00:00', 
    '2012-01-01 12:00:00' 
union all select 
    4, 
    '2012-01-01 13:30:00', 
    '2012-01-01 17:30:00' 
union all select 
    5, 
    '2012-01-01 09:00:00', 
    '2012-01-01 12:00:00' 
union all select 
    5, 
    '2012-01-01 14:09:00', 
    '2012-01-01 17:30:00'
union all select
    4,
    '2012-01-01 18:30:00', 
    '2012-01-01 19:30:00'
union all select 4, '2012-01-01 18:30:00', '2012-01-01 19:30:00';


select * from #timesheetentries tse1
left outer join #timesheetentries tse2 on tse1.staffid = tse2.staffid 
  and tse2.id = 
  (
      select MAX(ID) 
      from #timesheetentries ts_max 
      where ts_max.id < tse1.id and tse1.staffid = ts_max.staffid
  )
  outer apply   
  (
  select DATEDIFF(minute, tse2.clockedout, tse1.clockedin) as BreakTime
  ) as breakCheck

where BreakTime > 120 or BreakTime < 0 or tse2.id is null

order by tse1.StaffID, tse1.ClockedIn


   GO
   drop table #timesheetentries
   GO

Идея в том, что у вас есть исходная таблица расписаний.tse1, а затем вы делаетеleft join к той же таблице расписания, с псевдонимомtse2 и соответствующие строки, когдаstaffID то же самое иtse2.ID самое высокое значение идентификатора, которое по-прежнему меньше, чемtse1.ID, Это явно плохая форма - вы, вероятно, хотите использоватьROW_NUMBER() для этого сравнения ID, разделены и упорядоченыStaffID и вашClockedIn/ClockedOut значения, так как времена могли быть введены в хронологическом порядке.

На этом этапе строка из соединенных таблиц теперь содержит данные о времени из текущей записи расписания, а также предыдущую запись. Это означает, что мы можем сделать сравнение междуClockedIn/ClockedOut значения для последовательных записей времени ... и использованияDATEDIFF()мы можем выяснить, сколько времени пользователь провел в промежутке междуClockedout и более свежийClockedIn ценности. Я использовалOUTER APPLY просто потому, что это делает код чище, но вы, вероятно, могли бы упаковать его в подзапрос.

Как только мы сделаемDATEDIFF()тривиально находить случаи, когда индивидуумBreakTime не превышает 120-минутного барьера и удаляет эти записи расписания, оставляя только значимые строки расписания сотрудника, которые будут использоваться в ваших последующих отчетах.

1

но решил проблему совершенно по-другому. При этом используется рекурсивный CTE, который, как мне кажется, требует SQL Server 2005. Он точно сообщает о результатах (я полагаю), а также сообщает количество включений, записанных в течение таймфрейма, и общее количество отключенных минут (может быть больше 120, поскольку ограничение состоит в том, что каждый период вне офиса составляет менее двух часов).

declare @TimeSheetEntries table 
    ( 
    ID int identity not null primary key, 
    StaffID int not null, 
    ClockedIn datetime not null, 
    ClockedOut datetime not null 
    ); 

insert into @TimeSheetEntries 
    ( 
    StaffID, 
    ClockedIn, 
    ClockedOut 
    ) 
select 
    4, 
    '2012-01-01 09:00:00', 
    '2012-01-01 12:00:00' 
union all select 
    4, 
    '2012-01-01 13:30:00', 
    '2012-01-01 17:30:00' 
union all select 
    5, 
    '2012-01-01 09:00:00', 
    '2012-01-01 12:00:00' 
union all select 
    5, 
    '2012-01-01 14:09:00', 
    '2012-01-01 17:30:00'
union all select
    4,
    '2012-01-01 18:30:00', 
    '2012-01-01 19:30:00';


WITH ClockData AS
(
    SELECT ID, StaffID, ClockedIn, ClockedOut AS EffectiveClockout, 1 AS NumClockIns, 0 AS MinutesOff
    FROM @TimeSheetEntries ts
    WHERE NOT EXISTS (SELECT ID FROM @TimeSheetEntries tsWhere WHERE tsWhere.ClockedOut BETWEEN DATEADD(hour, -2, ts.ClockedIn) AND ts.ClockedIn)

    UNION ALL

    SELECT cd.ID, cd.StaffID, cd.ClockedIn, ts.ClockedOut AS EffectiveClockout, cd.NumClockIns + 1 AS NumClockIns, cd.MinutesOff + DateDiff(minute, cd.EffectiveClockout, ts.ClockedIn) AS MinutesOff
    FROM @TimeSheetEntries ts
    INNER JOIN ClockData cd
        ON ts.StaffID = cd.StaffID
            AND ts.ClockedIn BETWEEN cd.EffectiveClockout AND dateadd(hour, 2, cd.EffectiveClockout)
)
SELECT *
FROM ClockData cd
WHERE NumClockIns = (SELECT MAX(NumClockIns) FROM ClockData WHERE ID = cd.ID)

Это возвращает:

ID   StaffID   ClockedIn                 EffectiveClockout        NumClockIns   MinutesOff
3    5         2012-01-01 09:00:00.000   2012-01-01 12:00:00.000  1             0
4    5         2012-01-01 14:09:00.000   2012-01-01 17:30:00.000  1             0
1    4         2012-01-01 09:00:00.000   2012-01-01 19:30:00.000  3             150

UPDATE

В случае, если это не ясно, MinutesOff - это только «надбавка»; время или количество времени, "съеденного"; между ClockedIn и EffectiveClockout, показанными в одной строке. Таким образом, для StaffID 5 между синхронизированными периодами времени потребовалось 129 минут, но без учета допустимого времени, поэтому MinutesOff равен 0 для обеих строк.

Извините, Андрей и другие - сделали так, как просили! Edwardo
Спасибо @Dominic P и всем остальным, кто, очевидно, приложил немало усилий, чтобы ответить на вопрос. Я реализовал подход Доминика, и он работает. Edwardo
На самом деле, ваше решение не имеет более высоких требований, чем Джереми, в отношении версии SQL Server: ему также требуется SQL Server 2005 или более поздняя версия, поскольку оно использует CTE и ссылается на них несколько раз (таким образом, CTE не может & ' ; вместо этого переписать в виде простых вложенных вложенных элементов). В любом случае, хорошо, что между вами по меньшей мере один упомянул минимально требуемую версию. :)
0

Возможно, вставьте его во временную таблицу, а затем используйте левое соединение, чтобы построить таблицу результатов (если они могут входить и выходить дважды только в течение дня, это будет работать, если у вас будет 3 результата, но это не так).

select *
from timesheet ts
left join timesheet tss on tss.id = ts.id

После этого вы можете просто получить минимальное и максимальное значения или даже получить более надежный отчет.

Вариант 2:

create #TimeTable Table (UserID int, InTime int, OutTime int)

insert into #TimeTable (UserID) select distinct StaffID

Update #TimeTable set InTime = (select Min(InTime) from #TimeTable where StaffID = s.StaffID)  from #TimeTAble s

Update #TimeTable set OutTime = (Select Max(OutTime) from #TimeTable where StaffID = s.StaffID) from #TimeTable s

Если бы мне понадобилось больше времени, я бы объединил их в два быстрых запроса, но три из них будут работать, чтобы не беспокоиться о производительности.

Спасибо, Алекс. Количество часов не ограничено, поэтому вариант 1 не будет работать. Проблема, связанная с вариантом 2, заключается в том, что не учитывается допущение на то, что & lt; = 2 часа нахождения за пределами площадки. Edwardo
Извини за это. Я знаю, что вы можете сделать это с парой запросов, не делая этого в курсоре, хотя ... И это можно сделать, сверяя время друг с другом. Отметьте его на этом первом проходе, затем в следующем запросе, по крайней мере, очистите то, что сделал первый запрос. Это позволит избежать петли в некотором смысле.
0

-- Sample data.
declare @TimesheetEntries as Table ( Id Int Identity, StaffId Int, ClockIn DateTime, ClockOut DateTime )
insert into @TimesheetEntries ( StaffId, ClockIn, ClockOut ) values
  ( 4, '2012-05-03 09:00', '2012-05-03 12:00' ),
  ( 4, '2012-05-03 13:30', '2012-05-03 17:30' ), -- This falls within 2 hours of the next two rows.
  ( 4, '2012-05-03 17:35', '2012-05-03 18:00' ),
  ( 4, '2012-05-03 19:00', '2012-05-03 19:30' ),
  ( 4, '2012-05-03 19:45', '2012-05-03 20:00' ),
  ( 5, '2012-05-03 09:00', '2012-05-03 12:00' ),
  ( 5, '2012-05-03 14:09', '2012-05-03 17:30' ),
  ( 6, '2012-05-03 09:00', '2012-05-03 12:00' ),
  ( 6, '2012-05-03 13:00', '2012-05-03 17:00' )
select Id, StaffId, ClockIn, ClockOut from @TimesheetEntries

-- Find all of the periods that need to be coalesced and start the process.
declare @Bar as Table ( Id Int Identity, StaffId Int, ClockIn DateTime, ClockOut DateTime )
insert into @Bar
  select TSl.StaffId, TSl.ClockIn, TSr.ClockOut
    from @TimesheetEntries as TSl inner join
      -- The same staff member and the end of the left period is within two hours of the start of the right period.
      @TimesheetEntries as TSr on TSr.StaffId = TSl.StaffId and DateDiff( ss, TSl.ClockOut, TSr.ClockIn ) between 0 and 7200

-- Continue coalescing periods until we run out of work.
declare @Changed as Bit = 1
while @Changed = 1
  begin
  set @Changed = 0
  -- Coalesce periods.
  update Bl
    -- Take the later   ClockOut   time from the two rows.
    set ClockOut = case when Br.ClockOut >= Bl.ClockOut then Br.ClockOut else Bl.ClockOut end
    from @Bar as Bl inner join
      @Bar as Br on Br.StaffId = Bl.StaffId and
        -- The left row started before the right and either the right period is completely contained in the left or the right period starts within two hours of the end of the left.
        Bl.ClockIn < Br.ClockIn and ( Br.ClockOut <= Bl.ClockOut or DateDiff( ss, Bl.ClockOut, Br.ClockIn ) < 7200 )
  if @@RowCount > 0
    set @Changed = 1
  -- Delete rows where one period is completely contained in another.
  delete Br
    from @Bar as Bl inner join
      @Bar as Br on Br.StaffId = Bl.StaffId and
        ( ( Bl.ClockIn < Br.ClockIn and Br.ClockOut <= Bl.ClockOut ) or ( Bl.ClockIn <= Br.ClockIn and Br.ClockOut < Bl.ClockOut ) )
  if @@RowCount > 0
    set @Changed = 1
  end

-- Return all of the coalesced periods ...
select StaffId, ClockIn, ClockOut, 'Coalesced Periods' as [Type]
  from @Bar
union all
-- ... and all of the independent periods.
select StaffId, ClockIn, ClockOut, 'Independent Period'
  from @TimesheetEntries as TS
  where not exists ( select 42 from @Bar where StaffId = TS.StaffId and ClockIn <= TS.ClockIn and TS.ClockOut <= ClockOut )
order by ClockIn, StaffId

Я уверен, что есть некоторые оптимизации, которые должны быть сделаны.

Похожие вопросы