Complicated query

Question

Post reply

Complicated query

jenyaalt

Grasshopper

Points: 12
More actions
April 16, 2012 at 1:11 am

#256411

Hi all !
i have the following table structure :
id(Identity) time action
1 10:00 1
2 10:05 1
3 10:10 2
4 10:15 2
5 10:20 2
7 10:30 1
8 10:35 1
9 10:40 1
10 10:45 1
11 10:50 2
12 10:55 2
13 11:00 2
14 11:05 1
15 11:10 1
i need for each group with action id 2 (column 'action')
to find time difference. the result set should look like the following:
action id timediff
2 15min
2 10min
(the logic should be : calculate time diff between find the last action id 2 and the first action id 2 for each group of action id 2.)
id(Identity) time action
1 10:00 1
2 10:05 1
3 10:10 2 * from
4 10:15 2
5 10:20 2 * to
7 10:30 1
i need to do it without any loops or cursors , i it possible , i will appreciate any help , tnx .

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

How about this?

Setting up the sample data

DECLARE @tab TABLE

(

ID INT

,Tym TIME

,Axn INT

)

INSERT @tab

SELECT 1 ,'10:00', 1

UNION ALL SELECT 2 ,'10:05', 1

UNION ALL SELECT 3 ,'10:10', 2

UNION ALL SELECT 4 ,'10:15' ,2

UNION ALL SELECT 5 ,'10:20' ,2

UNION ALL SELECT 7 ,'10:30' ,1

UNION ALL SELECT 8 ,'10:35' ,1

UNION ALL SELECT 9 ,'10:40' ,1

UNION ALL SELECT 10,'10:45' ,1

UNION ALL SELECT 11,'10:50',2

UNION ALL SELECT 12 ,'10:55' ,2

UNION ALL SELECT 13 ,'11:00' ,2

UNION ALL SELECT 14 ,'11:05',1

UNION ALL SELECT 15 ,'11:10' ,1

And the query

; WITH CTE AS

(

SELECT T.ID , T.Tym , T.Axn

,(ROW_NUMBER() OVER(ORDER BY T.Tym) -

ROW_NUMBER() OVER(PARTITION BY T.Axn ORDER BY ID)) As Diff

FROM @tab T

)

SELECT T.Axn ,TimeDiff = DATEDIFF(MI, MIN(T.Tym) , MAX(T.Tym))

FROM CTE T

WHERE T.Axn = 2

GROUP BY T.Axn , T.Diff

vinu512 SSCoach Points: 15729 More actions · Answer 2

ColdCoffee (4/16/2012)
How about this?
Setting up the sample data
DECLARE @tab TABLE
(
ID INT
,Tym TIME
,Axn INT
)
INSERT @tab
SELECT 1 ,'10:00', 1
UNION ALL SELECT 2 ,'10:05', 1
UNION ALL SELECT 3 ,'10:10', 2
UNION ALL SELECT 4 ,'10:15' ,2
UNION ALL SELECT 5 ,'10:20' ,2
UNION ALL SELECT 7 ,'10:30' ,1
UNION ALL SELECT 8 ,'10:35' ,1
UNION ALL SELECT 9 ,'10:40' ,1
UNION ALL SELECT 10,'10:45' ,1
UNION ALL SELECT 11,'10:50',2
UNION ALL SELECT 12 ,'10:55' ,2
UNION ALL SELECT 13 ,'11:00' ,2
UNION ALL SELECT 14 ,'11:05',1
UNION ALL SELECT 15 ,'11:10' ,1
And the query
; WITH CTE AS
(
SELECT T.ID , T.Tym , T.Axn
,(ROW_NUMBER() OVER(ORDER BY T.Tym) -
ROW_NUMBER() OVER(PARTITION BY T.Axn ORDER BY ID)) As Diff
FROM @tab T
)
SELECT T.Axn ,TimeDiff = DATEDIFF(MI, MIN(T.Tym) , MAX(T.Tym))
FROM CTE T
WHERE T.Axn = 2
GROUP BY T.Axn , T.Diff

+1 ColdCoffee

You beat me to it.

Did the exact same stuff. Gudjob

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

jenyaalt Grasshopper Points: 12 More actions · Answer 3

Thank you very much for your help vinu 512 , your solution was brilliant and very helpful , i would never figure it out by myself. TNX ALLOT 🙂

vinu512 SSCoach Points: 15729 More actions · Answer 4

thats really sweet of you jennyaalt. But I think ColdCoffee was the one who gave the solution.

You're welcome anyways 😀

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

ColdCoffee (4/16/2012)
How about this?
Setting up the sample data
DECLARE @tab TABLE
(
ID INT
,Tym TIME
,Axn INT
)
INSERT @tab
SELECT 1 ,'10:00', 1
UNION ALL SELECT 2 ,'10:05', 1
UNION ALL SELECT 3 ,'10:10', 2
UNION ALL SELECT 4 ,'10:15' ,2
UNION ALL SELECT 5 ,'10:20' ,2
UNION ALL SELECT 7 ,'10:30' ,1
UNION ALL SELECT 8 ,'10:35' ,1
UNION ALL SELECT 9 ,'10:40' ,1
UNION ALL SELECT 10,'10:45' ,1
UNION ALL SELECT 11,'10:50',2
UNION ALL SELECT 12 ,'10:55' ,2
UNION ALL SELECT 13 ,'11:00' ,2
UNION ALL SELECT 14 ,'11:05',1
UNION ALL SELECT 15 ,'11:10' ,1
And the query
; WITH CTE AS
(
SELECT T.ID , T.Tym , T.Axn
,(ROW_NUMBER() OVER(ORDER BY T.Tym) -
ROW_NUMBER() OVER(PARTITION BY T.Axn ORDER BY ID)) As Diff
FROM @tab T
)
SELECT T.Axn ,TimeDiff = DATEDIFF(MI, MIN(T.Tym) , MAX(T.Tym))
FROM CTE T
WHERE T.Axn = 2
GROUP BY T.Axn , T.Diff

Nicely done, CC. Just a couple of performance related suggestions.

First, you can drastically cut down on the number of rows if you filter for t.Axn=2 in the CTE. In this case, it would cut down processing from 14 rows to just 6.

Second, you can get rid of 5 extra blocks in the execution plan, including one relatively expensive "SORT" using a little trick with dates and times.

WITH

cteTemporalGroup AS

(

SELECT TimeGroup = DATEADD(mi,-ROW_NUMBER() OVER (ORDER BY Tym)*5,Tym),

Tym

FROM @tab

WHERE Axn = 2

)

SELECT Axn = 2 ,TimeDiff = DATEDIFF(mi, MIN(tg.Tym), MAX(tg.Tym))

FROM cteTemporalGroup tg

GROUP BY TimeGroup

;

Of course, this relies on having times every 5 minutes apart.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)