July 8, 2012 at 3:15 am
Hi Guys,
Have got great help from this website over the years ,so first of all want to thank everyone here 🙂 and this is my first post as a member.
I have a below data in a table
ID Userid Startdate enddate
1 6005 14-Apr-12 13:10 14-Apr-12 13:12
1 6005 14-Apr-12 13:12 14-Apr-12 13:13
1 6005 14-Apr-12 13:39 14-Apr-12 13:42
1 6005 14-Apr-12 14:52 14-Apr-12 15:32
1 6005 14-Apr-12 15:32 14-Apr-12 15:33
What is required , I want the start date and the end date of row 1 should be <=60 mins of start date of row +1
So the sample output of above would look like
ID Userid Startdate enddate
1 6005 14-Apr-12 13:10 14-Apr-12 13:42
1 6005 14-Apr-12 14:52 14-Apr-12 15:32
Basically divide each by a duration of 60 mins.. if there is any way of getting this without any cursor/loops?
Any help on the above would be appreciated..thanks.
July 8, 2012 at 1:11 pm
GonnaCatchIT (7/8/2012)
Have got great help from this website over the years ...
Welcome aboard!
Shifting gears a bit, if what you say is true, then you should already know how to post readily consumable data to get tested answers by now. Help us help you in the future. Please see the first link in my signature line below for how to do that properly.
I would also ask, what do you want returned for the following?
ID Userid Startdate enddate
1 6005 14-Apr-12 13:10 14-Apr-12 14:12 --62 minutes
1 6005 14-Apr-12 14:12 14-Apr-12 14:13 --1 minute
1 6005 14-Apr-12 14:13 14-Apr-12 15:15 --62 minutes
1 6005 14-Apr-12 15:15 14-Apr-12 15:16 --1 minute
1 6005 14-Apr-12 15:16 14-Apr-12 15:17 --1 minutes
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2012 at 12:24 am
CREATE TABLE #CASE_DATA(CASE_ID INT,[USER_ID] INT,BEGIN_DATE DATETIME,END_DATE DATETIME)
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:10:00.000','2012-04-19 13:12:00.000')
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:10:00.000','2012-04-19 13:12:00.000')
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:10:00.000','2012-04-19 13:12:00.000')
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:12:00.000','2012-04-19 13:12:00.000')
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:12:00.000','2012-04-19 13:12:00.000')
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:39:00.000','2012-04-19 13:42:00.000')
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 13:42:00.000','2012-04-19 13:42:00.000')
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 14:51:00.000','2012-04-19 15:18:00.000')
INSERT INTO #CASE_DATA_ID VALUES (1295222,1716,'2012-04-19 15:18:00.000','2012-04-19 15:18:00.000')
Expected output
1295222 17164/19/2012 13:104/19/2012 13:42
129522217164/19/2012 14:514/19/2012 15:18
When somone is working on this case, it is not possible for another to do so at the same time, meaning we can assume that all consecutive rows with the same user id is the same action. The only exception would be if there is more than 60minutes between the End_date of Row X and Start_date of Row X+1 it will be another action.
July 9, 2012 at 4:34 pm
-- comparing rows
SELECT B.CASE_ID, B.[USER_ID] , E.END_DATE, B.BEGIN_DATE, datediff(MINUTE,E.END_DATE,B.BEGIN_DATE) as Diff
INTO #WORK
FROM (select ROW_NUMBER() OVER (ORDER BY [USER_ID]) AS 'RowNumber', *
from CASE_DATA) B
inner join (select (ROW_NUMBER() OVER (ORDER BY [USER_ID])+1) AS 'NextRowNumber', *
from CASE_DATA) E ON B.RowNumber = E.NextRowNumber
where datediff(MINUTE,E.END_DATE,B.BEGIN_DATE) >= 60
-- grabbing records matching begin and end
Select DISTINCT cd.*
from CASE_DATA cd
where cd.BEGIN_DATE in (SELECT BEGIN_DATE from #WORK) or cd.BEGIN_DATE in (SELECT END_DATE from #WORK)
-- close to what you want
didn't have much time but figured you might be able to cluge together this type of thing
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply