Hi ,
I am having table in below format. I want to calculate difference between each two cons rows for each ID using datedifference. Date column will be a datetime. how to do that?
below is sample data. in Date there will be a date along with time.
ID Status Date
111111-0010Start20200701
111111-0010Pause20200701
111111-0010Resume20200701
111111-0010Resume20200702
111111-0010Pause20200702
111111-0010Pause20200703
111111-0010Resume20200703
111111-0010Stop20200703
222222-0020Start20200701
222222-0020Pause20200701
222222-0020Resume20200701
222222-0020Resume20200702
222222-0020Pause20200702
222222-0020Pause20200703
222222-0020Resume20200703
222222-0020Stop20200703
Thanks
Abhas
July 8, 2020 at 8:09 am
Please provide expected result
July 8, 2020 at 10:41 am
Hi,
Below is expected result. i want to compare each with next row on datetimefield and fetch the difference between two. Again comparision is ID wise. If next row contains different id then it will not compare. in short group on ID.
ID Status Date Hours
111111-0010Start202007012
111111-0010Pause202007014
111111-0010Resume2020070115
111111-0010Resume202007021
111111-0010Pause202007021.5
111111-0010Pause202007030.3
111111-0010Resume202007032
111111-0010Stop20200703
Just small tip, if you add sample data then it is more useful for people helping you if you provide it by DDL and data inserts. In that case the person helping you would spend the time just on helping you. Anyway, this probably the script you want:
DECLARE @table AS TABLE (
id VARCHAR(20)
,[Status] VARCHAR(10)
,[Date] DATETIME2(7)
)
INSERT INTO @table
VALUES ('111111-0010', 'Start', '20200701 00:00:00')
, ('111111-0010', 'Pause', '20200701 02:00:00')
, ('111111-0010', 'Resume', '20200701 06:00:00')
, ('111111-0010', 'Resume', '20200701 21:00:00')
, ('222222-0020', 'Start', '20200701 00:00:00')
SELECT
id
,Status
,Date
,DATEDIFF(HOUR, [Date], LEAD([Date]) OVER (PARTITION BY id ORDER BY [Date]))
FROM @table
July 8, 2020 at 1:11 pm
Hi,
Below is expected result. i want to compare each with next row on datetimefield and fetch the difference between two. Again comparision is ID wise. If next row contains different id then it will not compare. in short group on ID.
ID Status Date Hours
111111-0010Start202007012
111111-0010Pause202007014
111111-0010Resume2020070115
111111-0010Resume202007021
111111-0010Pause202007021.5
111111-0010Pause202007030.3
111111-0010Resume202007032
111111-0010Stop20200703
This is a joke, right? You've provided dates without any time component, making the calculation of hours impossible. You have nearly 5,000 points, you should know better.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 8, 2020 at 2:14 pm
Thank you friends.
Regards,
Abhas.
July 9, 2020 at 5:07 pm
Well, that's a co-incidence! I've not been in here for weeks and weeks, just posted a very convoluted post, then I see this post about comparing columns, row by row.
Looks like the method I came up with wasn't too out there after all, as LEAD/LAG is the method recommended here 🙂
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply