Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 splitting the records

Author  Topic 

deviji
Starting Member

7 Posts

Posted - 2011-01-17 : 01:36:37
Hi,

I have two set of tables,
one is having the break timings like

b1 10:00 10:15
b2 13:00 14:00
b3 16:00 16:15
b4 17:00 17:15

the another table is having the start time and the number of hours to complete

8:00 6:40

i need a query which gives output like start time, end time

8:00 10:00
10:15 13:00
14:00 15:55

i'm using sql server 2005

can anyone help on this??

--Ami



sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-17 : 05:00:43
Try this once

create table #t1(BreakeID varchar(10),StartTime datetime,EndTime datetime)
insert into #t1
select 'b2','13:00','14:00' union all
select 'b3','16:00','16:15' union all
select 'b4','17:00','17:15'

create table #t2(WorkStartTime datetime,TotalHours datetime)
insert into #t2
select '8:00','6:40'union all
select '9:00','7:00'union all
select '9:30','6:15'

declare @duration int
select @duration=SUM(datediff(minute,starttime,endtime)) from #t1
select WorkstartTime,WorkstartTime+dateadd(minute,@duration,totalHours) as endtime from #T2
Go to Top of Page

deviji
Starting Member

7 Posts

Posted - 2011-01-18 : 02:03:21
hi,

i found the answer but not in the sql way.
[CODE]
DECLARE @tempActualTaskTime TABLE(
StartTime DATETIME,
NoOfHours Int,
FinishingTime DATETIME
)

DECLARE @tempTasks TABLE(
id INT,
StartTime DATETIME,
NoOfHours Int,
FinishingTime DATETIME
)

INSERT INTO @tempTasks

SELECT 19,'01/01/1900 08:00:00 ',400,'' UNION ALL
SELECT 20,'01/01/1900 08:00:00 ',520,'' UNION ALL
SELECT 21,'01/01/1900 10:00:00 ',520,''
;

DECLARE @tempBreaks TABLE(
id INT,
StartTime DATETIME,
FinishingTime DATETIME,
NoOfHours Decimal(10,2)
)

insert into @tempBreaks

SELECT 3, '10:00', '10:15',0.15 UNION ALL
SELECT 3, '13:00', '14:00',1.00 UNION ALL
SELECT 3, '16:00', '16:15',0.15 UNION ALL
SELECT 4, '17:00', '17:15',0.15



-- select * from @tempBreaks
-- select * from tempTasks
-- select * from @tempActualTaskTime
Delete from @tempActualTaskTime

Declare @dtExpectedStartTime as datetime, @dtExpectedNewStartTime as datetime
Declare @dtExpectedEndTime as datetime, @dtExpectedNewEndTime as datetime
Declare @intTotalTimeDiff as integer, @intBreakTimeDiff as integer, @intTimeDiff as integer, @intTimeCompleted as integer

select top 1 @dtExpectedStartTime=StartTime, @intTotalTimeDiff=NoOfHours from @tempTasks where id=20

select @dtExpectedEndTime = DATEADD(minute,@intTotalTimeDiff,@dtExpectedStartTime)
--select @dtExpectedStartTime,@dtExpectedEndTime

--starttime and finishingtime
set @intTimeCompleted=0
set @dtExpectedNewStartTime=@dtExpectedStartTime
set @dtExpectedNewEndTime=@dtExpectedEndTime

while (@dtExpectedNewStartTime is not null)
Begin
select Top 1 @dtExpectedNewEndTime=starttime, @dtExpectedNewStartTime= finishingtime, @intBreakTimeDiff=NoOfHours from @tempBreaks where (starttime between @dtExpectedStartTime and @dtExpectedEndTime ) order by starttime

if(@@rowcount<>0)
begin
select @dtExpectedStartTime, @dtExpectedNewEndTime,@dtExpectedNewStartTime, DateDiff(Minute,@dtExpectedStartTime,@dtExpectedNewEndTime)

if(DateDiff(Minute,@dtExpectedStartTime,@dtExpectedNewEndTime)>0)
insert into @tempActualTaskTime values(@dtExpectedStartTime,DateDiff(Minute,@dtExpectedStartTime,@dtExpectedNewEndTime),@dtExpectedNewEndTime)

set @intTimeCompleted=@intTimeCompleted + DateDiff(Minute,@dtExpectedStartTime,@dtExpectedNewEndTime)
select @intTimeDiff=(@intTotalTimeDiff-(DateDiff(Minute,@dtExpectedStartTime,@dtExpectedNewEndTime))-@intBreakTimeDiff)
set @dtExpectedEndTime=DATEADD(minute,@intTimeDiff,@dtExpectedNewStartTime)
set @dtExpectedStartTime=@dtExpectedNewStartTime

end
else
begin

select @intTimeDiff=(@intTotalTimeDiff-@intTimeCompleted)
set @dtExpectedNewEndTime=DATEADD(minute,@intTimeDiff,@dtExpectedNewStartTime)
if(@intTimeDiff>0)
insert into @tempActualTaskTime values(@dtExpectedNewStartTime, @intTimeDiff,@dtExpectedNewEndTime)
set @dtExpectedNewStartTime= null
end

End




select * from @tempActualTaskTime
[/CODE]
Go to Top of Page
   

- Advertisement -