August 7, 2012 at 3:38 pm
I have a table
TableA (ParentDocID, DocID, CreatedDate)
With Records
123 3 2010-05-25 11:18:14.000
123 2 2010-05-24 11:18:14.000
123 1 2010-05-23 11:18:14.000
245 4 2010-05-25 11:18:14.000
245 5 2010-05-24 11:18:14.000
245 6 2010-05-23 11:18:14.000
For each ParentDocId, I need to take two docID at a time (ordered by created date) and find the difference in hours. And then
move to the next record and do the same.
Is there an efficient way of doing this without cursor?
August 7, 2012 at 4:03 pm
First, you should know by now what you need to post and how to post it. If not, read the first article I reference below in my signature block.
That said, is this what you are looking for, and if not you need to provide a bit more detail. One thing to ask yourself, based on what was posted, would I be able to answer the quetion.
with SampleData as (
select
ParentDocID,
DocID,
CreatedDate
from
(values (123,3,'2010-05-25 11:18:14.000'),
(123,2,'2010-05-24 11:18:14.000'),
(123,1,'2010-05-23 11:18:14.000'),
(245,4,'2010-05-25 11:18:14.000'),
(245,5,'2010-05-24 11:18:14.000'),
(245,6,'2010-05-23 11:18:14.000')) dt(ParentDocID,DocID,CreatedDate)
),
BaseData as (
select
row_number() over (partition by ParentDocID order by CreatedDate desc) rn,
ParentDocID,
DocID,
CreatedDate
from
SampleData
)
select
bd1.ParentDocID,
bd1.DocID,
bd1.CreatedDate,
datediff(hh,bd2.CreatedDate,bd1.CreatedDate) HrsDiff
from
BaseData bd1
left outer join BaseData bd2
on (bd1.rn = bd2.rn - 1
and bd1.ParentDOcID = bd2.ParentDocID)
order by
bd1.ParentDocID,
bd1.CreatedDate desc;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply