asked on

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.

ASKER CERTIFIED SOLUTION

Rajkumar Gs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajkumar Gs

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

Open in new window

Qlemo

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
?

urir10

ASKER

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.

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajkumar Gs

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

Open in new window

urir10

ASKER

Thanks alot guys! i used both of those solution to get what i needed.