SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 MS SQL Cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

singhswat
Starting Member

India
3 Posts

Posted - 03/03/2013 :  14:12:03  Show Profile  Reply with Quote
Hello,
Following is my requirement.

Table rows:
SLN Id StartDate        EndDate                Duration        TimeElapsed
1   1  1/1/2012 10.12   1/1/2012 10.13        0day 00:01:00      0-days 00:01:00
1   1  1/1/2012 10.14   1/1/2012 10.15        0day 00:01:00      0-days 00:03:00
1   1  1/1/2012 10.15   1/2/2012 10.16        1day 00:01:00      1-days 00:04:00 (total- counter reset) 
2   2  1/1/2012 10.12   1/1/2012 10.13        0day 00:01:00      0-days 00:01:00
2   2  1/1/2012 10.14   1/1/2012 10.15        0day 00:01:00      0-days 00:03:00
2   2  1/1/2012 10.15   1/2/2012 10.16        1day 00:01:00      1-days 00:04:00 (total- counter reset) 


I need to calculate Duration and TimeElapsed.

Can somebody please give me sample query for this requirement.... I have all these values in my temp table, so either I use cursor or call a function and use cursor inside it... or is there any other option 

Many thanks

Edited by - singhswat on 03/03/2013 16:26:01

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 03/03/2013 :  17:05:20  Show Profile  Reply with Quote
You don't need to use a cursor. In fact DON'T use a cursor. Here is a way to do the same calculations without using cursors. I have created a test table and script that you can copy to an SSMS window and run to see how it works. I am displaying the results in minutes - it could be formatted to days/hours/minutes if required:
create table #tmp (sln int, id int, startdate datetime, enddate datetime);

insert into #tmp values (1,1,'20120101 10:12:00.000','20120101 10:13:00.000'),
(1,1,'20120101 10:14:00.000','20120101 10:15:00.000'),
(1,1,'20120101 10:15:00.000','20120101 10:16:00.000')

;with cte as
(
	select
		a.*,
		row_number() over (partition by SLN order by startdate) as RN,
		datediff(mi,a.startdate,a.endDate) as Duration
	from
		#tmp a
)
select
	a.*,
	b.TimeElapsed
from
	cte a
	outer apply
	(
		select datediff(mi,b.StartDate,a.endDate) as TimeElapsed
		from cte b
		where b.SLN = a.SLN and b.RN = 1
	) b


drop table #tmp
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000