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 2008 Forums
 Transact-SQL (2008)
 MS SQL Cursor

Author  Topic 

singhswat
Starting Member

4 Posts

Posted - 2013-03-03 : 14:12:03
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 17:05:20
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
   

- Advertisement -