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

что есть менее сложные способы сделать это, но я смог осуществить это с помощью пары 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
            )
        )

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

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-минутного барьера и удаляет эти записи расписания, оставляя только значимые строки расписания сотрудника, которые будут использоваться в ваших последующих отчетах.

но решил проблему совершенно по-другому. При этом используется рекурсивный 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 для обеих строк.

Возможно, вставьте его во временную таблицу, а затем используйте левое соединение, чтобы построить таблицу результатов (если они могут входить и выходить дважды только в течение дня, это будет работать, если у вас будет 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

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

-- 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

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