How to calculate difference in Minutes/Seconds between two date in the same column

Hi all

I have a table that looks like this:

TicketID              SetStatus                                      SetDate                                       Count
100601BG15      Acknowledged                      2010-06-01 14:02:45.330                   1
100601BG15      Waiting on Business      2010-06-01 14:07:28.820                   0
100601BG15      Pending                              2010-06-01 14:07:40.597                   1
100601BG15      Pending                              2010-06-01 14:34:43.737                   1
100601BG15      Pending                           2010-06-03 18:29:47.120                   1
100601BG15      Post Dated                      2010-06-04 09:00:52.587                   0
100601BG15      Post Dated                      2010-06-07 12:42:37.960                   0
100601BG15      Fulfilled                              2010-06-07 14:38:29.677                   1

I also have a function  CalcMinutesSLA(Date1,Date2) that will calculate the difference between two dates excluding weekends and holidays.

Basically what i need is to calculate the difference in minutes between each date and the one before it and only the ones that have count set to 1.

So from the example above i would need the difference between Fulfilled and Post Date then from Post Dated to Pending and so on.
LVL 7
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Software EngineerCommented:
Please see the attached script with your sample data and final query

Raj
``````create table #table
(
TicketID              varchar(20),
SetStatus             varchar(20),
SetDate               datetime,
Count				int
)

insert into #table
select '100601BG15',      'Acknowledged',                      '2010-06-01 14:02:45.330',                   1
union all
select '100601BG15',      'Waiting on Business',      '2010-06-01 14:07:28.820',                   0
union all
select '100601BG15',      'Pending',                              '2010-06-01 14:07:40.597',                   1
union all
select '100601BG15',      'Pending',                              '2010-06-01 14:34:43.737',                   1
union all
select '100601BG15',      'Pending',                           '2010-06-03 18:29:47.120',                   1
union all
select '100601BG15',      'Post Dated',                      '2010-06-04 09:00:52.587',                   0
union all
select '100601BG15',      'Post Dated',                      '2010-06-07 12:42:37.960',                   0
union all
select '100601BG15',      'Fulfilled',                              '2010-06-07 14:38:29.677',                   1

with cte as
(
select *, row_number() over (order by SetDate) row_no
from #table
)
select *, datediff(mi, a.SetDate, b.SetDate) as DiffMinute from cte a
inner join cte b
on a.row_no = b.row_no - 1
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Software EngineerCommented:
Oh! In my query, give the alias 'a'. Here is the modified query.

Raj
``````with cte as
(
select *, row_number() over (order by SetDate) row_no
from #table
)
select a.*, datediff(mi, a.SetDate, b.SetDate) as DiffMinute from cte a
inner join cte b
on a.row_no = b.row_no - 1
``````
"Batchelor", Developer and EE Topic AdvisorCommented:
The "count" condition is not accounted for, and it is far from being clear what you mean. In the above CTE, would the added condition be
a.count = 1 and b.count = 0 or a.count = 0 and b.count = 1
or
a.count = 1 or b.count = 1
?
Author Commented:
The count condition only there to say whether the status assosiated to the row should be counted since when the ticket is set to 'Waiting on business' for example it should not count the time towards the SLA value.
Commented:
;with cte as
(select ticketid,setstatus,setdate
.row_number() over (partition by (ticketid) order by setdate asc) as rn
from yourtable
where [count]=1
)
select a.ticketid,dbo.calcminutessla(a.setdate,b.setdate)
from cte as a
inner join cte as b
on a.ticketid=b.ticketid
and a.rn=b.rn-1
order by 1
Software EngineerCommented:
Check this new query. I verified and seems to be fine.

Raj
``````select b.*, datediff(mi, PreviousDate, Setdate) as DiffInMinutes
from
(
select a.*, (Select max(SetDate) from #table where SetDate < a.SetDate and count = 1) as PreviousDate
from #table a
) b
``````
Author Commented:
Thanks alot guys! i used both of those solution to get what i needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.