August 20, 2013 at 9:39 am
HI,
I have the following table
EntryID DateInserted InsertedBy
17762852013-06-03 07:46:38.340592
17762862013-06-03 07:47:47.677592
17762872013-06-03 07:48:17.367592
17762882013-06-03 07:49:08.750592
17762892013-06-03 07:49:08.750592
17762902013-06-03 07:49:45.177592
17762912013-06-03 07:54:54.290592
17762922013-06-03 07:57:11.703592
17762932013-06-03 07:57:35.93083
17762942013-06-03 07:58:33.84383
17762952013-06-03 07:58:36.293592
17762962013-06-03 07:58:54.85383
17762972013-06-03 07:59:06.523592
17762982013-06-03 07:59:27.63383
17762992013-06-03 07:59:46.38383
17763002013-06-03 08:00:02.020592
17763012013-06-03 08:00:04.39083
17763022013-06-03 08:00:29.590592
17763032013-06-03 08:01:00.240592
17763042013-06-03 08:01:12.94383
17763052013-06-03 08:01:31.15083
17763062013-06-03 08:01:31.990592
17763072013-06-03 08:01:50.37383
17763082013-06-03 08:02:10.84083
17763092013-06-03 08:02:30.24383
17763102013-06-03 08:03:03.73783
17763112013-06-03 08:04:08.750592
17763122013-06-03 08:04:14.72383
17763132013-06-03 08:04:27.033592
17763142013-06-03 08:05:26.61783
17763152013-06-03 08:05:26.61783
17763162013-06-03 08:05:38.52783
Now I want to get the difference between 2 consecutive dateinserted col in hours or mins into another column say Time diff grouped by on the inserted by col since i want total time taken by each person inserted.like as below
EntryID DateInserted InsertedBy TimeDiff
17762852013-06-03 07:46:38.340592 diff(2013-06-03 07:47:47.677-2013-06-03 07:46:38.340)-- should be the output of this col either in mins or hours.Also if the difference is > than 3 mins I want only 3 mins as the output.
17762862013-06-03 07:47:47.677592
17762872013-06-03 07:48:17.367592
17762882013-06-03 07:49:08.750592
17762892013-06-03 07:49:08.750592
17762902013-06-03 07:49:45.177592
17762912013-06-03 07:54:54.290592
17762922013-06-03 07:57:11.703592
17762932013-06-03 07:57:35.93083
17762942013-06-03 07:58:33.84383
17762952013-06-03 07:58:36.293592
17762962013-06-03 07:58:54.85383
17762972013-06-03 07:59:06.523592
17762982013-06-03 07:59:27.63383
17762992013-06-03 07:59:46.38383
17763002013-06-03 08:00:02.020592
17763012013-06-03 08:00:04.39083
17763022013-06-03 08:00:29.590592
17763032013-06-03 08:01:00.240592
17763042013-06-03 08:01:12.94383
17763052013-06-03 08:01:31.15083
17763062013-06-03 08:01:31.990592
17763072013-06-03 08:01:50.37383
17763082013-06-03 08:02:10.84083
17763092013-06-03 08:02:30.24383
17763102013-06-03 08:03:03.73783
17763112013-06-03 08:04:08.750592
17763122013-06-03 08:04:14.72383
17763132013-06-03 08:04:27.033592
17763142013-06-03 08:05:26.61783
17763152013-06-03 08:05:26.61783
17763162013-06-03 08:05:38.52783
Thanks in advance.
August 20, 2013 at 9:54 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2013 at 8:43 pm
Perhaps something like this?
WITH SampleData (EntryID, DateInserted, InsertedBy) AS (
SELECT 1776285,'2013-06-03 07:46:38.340',592
UNION ALL SELECT 1776286,'2013-06-03 07:47:47.677',592
UNION ALL SELECT 1776287,'2013-06-03 07:48:17.367',592
UNION ALL SELECT 1776288,'2013-06-03 07:49:08.750',592
UNION ALL SELECT 1776289,'2013-06-03 07:49:08.750',592
UNION ALL SELECT 1776290,'2013-06-03 07:49:45.177',592
UNION ALL SELECT 1776291,'2013-06-03 07:54:54.290',592
UNION ALL SELECT 1776292,'2013-06-03 07:57:11.703',592
)
SELECT EntryID, DateInserted, InsertedBy
,seconds=CASE WHEN seconds > 180 THEN 180 ELSE seconds END
FROM (
SELECT a.EntryID, a.DateInserted, a.InsertedBy
,seconds=DATEDIFF(second
,(
SELECT DateInserted
FROM SampleData b
WHERE a.EntryID - 1 = b.EntryID
)
,a.DateInserted)
FROM SampleData a
) a;
I only included a subset of your sample data because, as Sean indicated consumable sample data provided by you would have been nice.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 27, 2013 at 9:04 am
Thank You
Will try this out.
August 27, 2013 at 1:26 pm
You might need to use ROW_NUMBER() instead, if there is any chance of a gap/missing number in the EntryID column (note that by definition identity columns can have gaps in the values).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply