/***********************************************************************
    ProductName = СуперХП
    Version = 0.0.0.0
    CompanyName = "Рога и копыта"
    LegalCopyright = © Рога и копыта, 2003
    ProcedureName = sp_stGetDistribByWeekDays
    Description = Возвращает распределение интервалов
   простоя по дням  недели с учетом указанного интервала
***********************************************************************/
CREATE PROCEDURE [dbo].[sp_stGetDistribByWeekDays] 

-- Входные параметры - ModuleId и время начало/конец интервала
@ModuleId int,
@startdt datetime,
@enddt datetime = NULL

AS

set dateformat dmy
set @enddt = isNull(@enddt, GETUTCDATE())
declare @Hcount integer, @HStart integer, @i int
declare @res table(dw int, c int)
set @i= 1 
while @i< 8  begin 
 insert into @res(dw,c) values (@i, 0 )
 set @i=@i+ 1 
end

declare @tmpdt1 datetime, @tmpdt2 datetime, @ii int, @cc int
-- переведем время в локальное 
declare @LTable table(id int identity, IdleTimeStart datetime,IdleTimeEnd datetime)
insert into @LTable(IdleTimeStart,IdleTimeEnd)
select IdleTimeStart,IdleTimeEnd from OIKArch..S_StatInfResults where ModuleId = @ModuleId
--select * from @LTable
set @ii= 1 
select @cc=max(ID) from @LTable
while @ii<=@cc begin
 select @tmpdt1=IdleTimeStart, @tmpdt2=IdleTimeEnd from @LTable where ID=@ii
 exec OIKARCH..OutputGMTToLocalTime @ReturnDT=@tmpdt1 output
 if @tmpdt2 is not null exec OIKARCH..OutputGMTToLocalTime @ReturnDT=@tmpdt2 output
 update @LTable set IdleTimeStart=@tmpdt1 ,IdleTimeEnd=@tmpdt2 where ID=@ii
 set @ii=@ii+ 1 
end
--select * from @LTable -- получили туже же таблицу с локальным временем
-- заданный интервал переведем тоже в локальное время 
exec OIKARCH..OutputGMTToLocalTime @ReturnDT=@startdt output
exec OIKARCH..OutputGMTToLocalTime @ReturnDT=@enddt output
--select @startdt,@enddt

declare curr cursor  for 
Select HStart = DATEPART(dw,CASE when (IdleTimeStart < @startdt AND isNull(IdleTimeEnd,@enddt) > @startdt) then @startdt else IdleTimeStart END), 
HCount = (DATEDIFF(d,
CASE when (IdleTimeStart < @startdt AND isNull(IdleTimeEnd,@enddt) > @startdt) then @startdt else IdleTimeStart END,
CASE when (IdleTimeStart < @enddt AND isNull(IdleTimeEnd,@enddt) > @enddt) then @enddt else isNull(IdleTimeEnd,@enddt) END )) 
--  from OIKArch..S_StatInfResults where ModuleId = @ModuleId AND isNull(IdleTimeEnd,@enddt) > @startdt  AND IdleTimeStart < @enddt
 from @LTable where  isNull(IdleTimeEnd,@enddt) > @startdt  AND IdleTimeStart < @enddt

open curr
FETCH NEXT FROM curr INTO @HStart, @Hcount
WHILE @@FETCH_STATUS= 0  begin
update @res set c=c+ 1  where dw=@HStart
   while @Hcount> 0  begin
    set @HStart=@HStart+ 1 
    if @HStart> 7  set @HStart= 0 
    update @res set c=c+ 1  where dw=@HStart
    set @Hcount=@Hcount- 1 
   end
   FETCH NEXT FROM curr INTO @HStart, @Hcount
end
Close curr
DEALLOCATE curr
select * from @res

GO