Get difference from a sequence table

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)

Get difference from a sequence table

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-04-14 : 07:40:56

I have the below table:
Gp Sq StarTime EndTime Operation
1 1 10:30:00 10:31:00 A
1 2 10:34:00 10:55:00 B
1 3 10:43:45 10:44:45 C
2 1 10:45:00 10:47:00 D
2 2 10:49:00 10:59:00 E
2 3 11:32:45 11:44:45 F
2 4 11:35:00 11:37:00 G
3 1 11:39:00 11:51:00 H
3 2 11:52:45 11:59:45 J

The query result should be as below:

Gp StartTime EndTime Op_Start Op_End TotalTime
1 10:30:00 10:44:45 A C 00:14:45
1 10:45:00 11:37:00 D G 00:52:00
1 11:39:00 11:59:45 H J 00:20:45

Thanks for any help

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 07:53:08

[code]
select Gp,min(StarTime),max(EndTime),min(Operation)as Op_Start ,max(Operation)as Op_End, "timediff function"(min(StarTime),max(EndTime))as TotalTime from table group by Gp
[/code]
You can find "timediff function" here http://visakhm.blogspot.com/2010/03/time-difference-function.html

PBUH

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-04-14 : 08:06:17

Thank you for the help, but The result should show the first operation and the last operation it is basically the fisrt and the last and may not be min and max.

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 08:14:32

[code]

select Gp,min(StarTime),t3.EndTime,min(Operation)as Op_Start ,max(Operation)as Op_End,
"timediff function"(min(StarTime),t3.EndTime)as TotalTime from yourtable t1
cross apply
(
select top 1 EndTime as EndTime from yourtable t2 where t2.Gp=t1.Gp
order by Sq desc

)t3
group by Gp,t3.EndTime

[/code]

PBUH

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-04-14 : 08:27:34

Thank you so much

coagulance
Yak Posting Veteran

78 Posts

Posted - 2010-04-14 : 08:33:29

I would like to add the below to the query result
Gp StartTime EndTime Op_Start Op_End TotalTime HoldTime
1 10:30:00 10:44:45 A C 00:14:45 00:00:15
2 10:45:00 11:37:00 D G 00:52:00 00:02:00
3 11:39:00 11:59:45 H J 00:20:45
The last column being the difference of the EndTime of Group1 and StartTime of Group2 and so on except the last column
Thanks,
T

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 09:09:23

[code]
;with cte
as
(
select Gp,min(StarTime),t3.EndTime,min(Operation)as Op_Start ,max(Operation)as Op_End,
"timediff function"(min(StarTime),t3.EndTime)as TotalTime from yourtable t1
cross apply
(
select top 1 EndTime as EndTime from yourtable t2 where t2.Gp=t1.Gp
order by Sq desc

)t3
group by Gp,t3.EndTime
)

select *,"timediff function"(t1.EndTime,t2.StartTime) from cte t1 left join cte t2 where t1.Gp=t2.Gp+1

[/code]

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources