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:15b2 13:00 14:00b3 16:00 16:15b4 17:00 17:15the another table is having the start time and the number of hours to complete8:00 6:40i need a query which gives output like start time, end time8:00 10:0010:15 13:0014:00 15:55i'm using sql server 2005can anyone help on this??--Ami |
|
sathishmangunuri
Starting Member
32 Posts |
Posted - 2011-01-17 : 05:00:43
|
Try this oncecreate table #t1(BreakeID varchar(10),StartTime datetime,EndTime datetime)insert into #t1select 'b2','13:00','14:00' union allselect 'b3','16:00','16:15' union allselect 'b4','17:00','17:15'create table #t2(WorkStartTime datetime,TotalHours datetime)insert into #t2select '8:00','6:40'union allselect '9:00','7:00'union allselect '9:30','6:15'declare @duration intselect @duration=SUM(datediff(minute,starttime,endtime)) from #t1select WorkstartTime,WorkstartTime+dateadd(minute,@duration,totalHours) as endtime from #T2 |
|
|
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 @tempTasksSELECT 19,'01/01/1900 08:00:00 ',400,'' UNION ALLSELECT 20,'01/01/1900 08:00:00 ',520,'' UNION ALLSELECT 21,'01/01/1900 10:00:00 ',520,'';DECLARE @tempBreaks TABLE(id INT,StartTime DATETIME,FinishingTime DATETIME,NoOfHours Decimal(10,2))insert into @tempBreaksSELECT 3, '10:00', '10:15',0.15 UNION ALLSELECT 3, '13:00', '14:00',1.00 UNION ALLSELECT 3, '16:00', '16:15',0.15 UNION ALLSELECT 4, '17:00', '17:15',0.15-- select * from @tempBreaks-- select * from tempTasks-- select * from @tempActualTaskTimeDelete from @tempActualTaskTimeDeclare @dtExpectedStartTime as datetime, @dtExpectedNewStartTime as datetimeDeclare @dtExpectedEndTime as datetime, @dtExpectedNewEndTime as datetimeDeclare @intTotalTimeDiff as integer, @intBreakTimeDiff as integer, @intTimeDiff as integer, @intTimeCompleted as integerselect top 1 @dtExpectedStartTime=StartTime, @intTotalTimeDiff=NoOfHours from @tempTasks where id=20select @dtExpectedEndTime = DATEADD(minute,@intTotalTimeDiff,@dtExpectedStartTime)--select @dtExpectedStartTime,@dtExpectedEndTime--starttime and finishingtimeset @intTimeCompleted=0set @dtExpectedNewStartTime=@dtExpectedStartTimeset @dtExpectedNewEndTime=@dtExpectedEndTimewhile (@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 Endselect * from @tempActualTaskTime[/CODE] |
|
|
|
|
|