Complex T-SQL query

Question

Complex T-SQL query

kevin_nikolai

SSCarpal Tunnel

Points: 4343
More actions
March 3, 2010 at 11:09 am

#386425

Need to write a T-SQL query to retrieve the following information:
In the case below, I require Failures after the first Failure stats up to the first Success stats,
so in the 3 rows below I need to skip the first row (or Stats Failure) and get the (count of Stats and duration) after it,
(in this case the Stats count is 2, the duration is 2010-02-03 09:59:24 (EntryDate)
- 2010-02-03 09:49:24 (EntryDate) = 10 minutes).
IDStatsEntryDate
13Failure2010-02-03 09:39:24
13Failure2010-02-03 09:49:24
13Success2010-02-03 09:59:24
The following only lists Success, so these entries I want to ignore.
IDStatsEntryDate
13Success2010-02-03 10:09:24
13Success2010-02-03 10:19:24
13Success2010-02-03 10:29:24
13Success2010-02-03 10:39:24
13Success2010-02-03 10:49:24
13Success2010-02-03 10:59:24
13Success2010-02-03 11:09:24
In the case below, I require Failures after the first Failure stats up to the first Success stats,
so in the 4 rows below I need to skip the first row (or Stats Failure) and get the (count of Stats and duration) after it,
(in this case the Stats count is 3, the duration is 2010-03-03 09:09:27 (EntryDate)
- 2010-03-03 08:49:27 (EntryDate) = 20 minutes).
IDStatsEntryDate
13Failure2010-03-03 08:39:27
13Failure2010-03-03 08:49:27
13Failure2010-03-03 08:59:28
13Success2010-03-03 09:09:27
In the case below, I require Failures after the first Failure stats up to the first Success stats,
since there is no 2nd Failure (Stats) I want to ignore the Failure & Success. (so skip/ignore the following 2 rows).
IDStatsEntryDate
13Failure2010-03-03 09:19:27
13Success2010-03-03 09:29:27
--===== Create the test table
CREATE TABLE #mytable
(
ID INT not null,
Stats varchar(15) not null,
EntryDate DATETIME not null
)
--===== Insert data into test table
INSERT INTO #mytable
(ID, Stats, EntryDate)
SELECT '13','Failure','2010-02-03 09:39:24'
UNION ALL
SELECT '13','Failure','2010-02-03 09:49:24'
UNION ALL
SELECT '13','Success','2010-02-03 09:59:24'
UNION ALL
SELECT '13','Success','2010-02-03 10:09:24'
UNION ALL
SELECT '13','Success','2010-02-03 10:19:24'
UNION ALL
SELECT '13','Success','2010-02-03 10:29:24'
UNION ALL
SELECT '13','Success','2010-02-03 10:39:24'
UNION ALL
SELECT '13','Success','2010-02-03 10:49:24'
UNION ALL
SELECT '13','Success','2010-02-03 10:59:24'
UNION ALL
SELECT '13','Success','2010-02-03 11:09:24'
UNION ALL
SELECT '13','Failure','2010-03-03 08:39:27'
UNION ALL
SELECT '13','Failure','2010-03-03 08:49:27'
UNION ALL
SELECT '13','Failure','2010-03-03 08:59:27'
UNION ALL
SELECT '13','Success','2010-03-03 09:09:27'
UNION ALL
SELECT '13','Failure','2010-03-03 09:19:27'
UNION ALL
SELECT '13','Success','2010-03-03 09:29:27'
Note: In the examples shown above, the ID is 13 for all rows.
On the database, their is various ID's (14,17,21,33,51, etc.)
The reason why I chose to use only ID 13 is for illustration simplicity.

Viewing 15 posts - 1 through 15 (of 21 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide table def and rsample data in a ready to use format. For details on how to do that please see the first link in my signature.

Furthermore, your code after the line

The following only lists Success, so ignore, go to the bottom of the list for more Failures.

is just wasted. You don't have to provide dozens of rows that we should ignore... Reduce it to the amount required to sjow the concept.

Also, please include what you've tried so far and where you got stuck.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 2

kevin_nikolai

SSCarpal Tunnel

Points: 4343

March 3, 2010 at 1:08 pm

#1128057

Thanks Lutz,updated as requested.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Unfortunately, your sample code won't run since you defined ID as being identity but you're trying to insert value 13 for every row.

Please clean up your sample code and run a test on your system to make sure t works as expected from your side.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 4

prob shouldn’t do this since it isn’t my code or problem and I run risk of steering the problem and solution in the wrong direction but now i too am interested in the solution. I think he means.

--drop table #mytable

--===== Create the test table

CREATE TABLE #mytable

(

ID INT ,

Stats varchar(15) not null,

EntryDate DATETIME not null

)

--===== Insert data into test table

--SET IDENTITY_INSERT #mytable ON

INSERT INTO #mytable

(ID, Stats, EntryDate)

SELECT '13','Failure','2010-02-03 09:39:24'

UNION ALL

SELECT '13','Failure','2010-02-03 09:49:24'

UNION ALL

SELECT '13','Success','2010-02-03 09:59:24'

UNION ALL

SELECT '14','Failure','2010-02-03 10:09:24'

UNION ALL

SELECT '14','Failure','2010-02-03 10:19:24'

UNION ALL

SELECT '14','Failure','2010-02-03 10:29:24'

UNION ALL

SELECT '14','Failure','2010-02-03 10:39:24'

UNION ALL

SELECT '14','Failure','2010-02-03 10:49:24'

UNION ALL

SELECT '14','Success','2010-02-03 10:59:24'

LutzM SSC Guru Points: 107049 More actions · Answer 5

BaldingLoopMan (3/3/2010)
... I think he means. ...

That's one possible direction. But instead of guessing I'd like to know.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

Hope this helps...

DECLARE@tiCounter TINYINT

DECLARE@iGroupID INT

SET@tiCounter = 0

SET@iGroupID = 1

--===== Create the test table

CREATE TABLE #mytable

(

ID INT not null,

Stats varchar(15) not null,

EntryDate DATETIME not null

)

--===== Insert data into test table

INSERT INTO #mytable

(ID, Stats, EntryDate)

SELECT '13','Failure','2010-02-03 09:39:24'

UNION ALL

SELECT '13','Failure','2010-02-03 09:49:24'

UNION ALL

SELECT '13','Success','2010-02-03 09:59:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:09:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:19:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:29:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:39:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:49:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:59:24'

UNION ALL

SELECT '13','Success','2010-02-03 11:09:24'

UNION ALL

SELECT '13','Failure','2010-03-03 08:39:27'

UNION ALL

SELECT '13','Failure','2010-03-03 08:49:27'

UNION ALL

SELECT '13','Failure','2010-03-03 08:59:27'

UNION ALL

SELECT '13','Success','2010-03-03 09:09:27'

UNION ALL

SELECT '13','Failure','2010-03-03 09:19:27'

UNION ALL

SELECT '13','Success','2010-03-03 09:29:27'

ALTER TABLE #mytable ADD GroupID INT;

ALTER TABLE #mytable ADD Counter TINYINT;

UPDATE#mytable

SET@tiCounter = CASE

WHEN @tiCounter = 0 AND Stats = 'Failure' THEN 1

WHEN @tiCounter = 1 AND Stats = 'Failure' THEN 2

WHEN @tiCounter = 2 AND Stats = 'Success' THEN 3

WHEN @tiCounter = 3 THEN 0

ELSE @tiCounter

END,

@iGroupID = CASE WHEN @tiCounter = 0 THEN @iGroupID + 1 ELSE @iGroupID END,

Counter = @tiCounter,

GroupID = @iGroupID

SELECTID, COUNT(*) - 1 StatsCount, DATEDIFF( MINUTE, MIN( EntryDate ), MAX( EntryDate ) ) TimeDiff

FROM#mytable

WHERECounter <> 0

GROUP BY ID, GroupID

DROP TABLE #mytable

Also go through the following link to know about the method used and the situations when this might create problems.

http://www.sqlservercentral.com/articles/T-SQL/68467/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 7

Thanks, will give it a try and post the results on this forum.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 8

Hi Dhasian, thanks for your prompt reply, please view below some issues I have.

Your example only caters for either 1 or 2 Status Failure, not for 3 or more, view below:

SELECT '13','Failure','2010-02-03 09:39:24'

UNION ALL

SELECT '13','Failure','2010-02-03 09:49:24'

UNION ALL

SELECT '13','Success','2010-02-03 09:59:24'

------------------------------------------------------------------------------------

Below is an example of 3 Status Failure:

SELECT '13','Failure','2010-03-03 08:39:27'

UNION ALL

SELECT '13','Failure','2010-03-03 08:49:27'

UNION ALL

SELECT '13','Failure','2010-03-03 08:59:27'

UNION ALL

SELECT '13','Success','2010-03-03 09:09:27'

------------------------------------------------------------------------------------

The last part of your query below, calculates the TimeDiff from first failure to first success,

it should be from second failure to first success, so the datediff below won't work.

SELECT ID, COUNT(*) -1 StatsCount, DATEDIFF( MINUTE, MIN( EntryDate ), MAX( EntryDate ) ) TimeDiff

FROM mytable

WHERE Counter <> 0

GROUP BY ID, GroupID

Would like to hear from the likes of Jeff Moden, Lynn Pettis, etc. if they have a solution for this.

LutzM SSC Guru Points: 107049 More actions · Answer 9

clive-421796 (3/6/2010)
Would like to hear from the likes of Jeff Moden, Lynn Pettis, etc. if they have a solution for this.

I'm by far not at the level of Jeff or Lynn, but I'll have a look at it if you're interested.

But since you specifically requested one of the guru's I'm not sure if it's worth the effort... :Whistling:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

clive-421796 (3/6/2010)
Would like to hear from the likes of Jeff Moden, Lynn Pettis, etc. if they have a solution for this.

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/19/how-to-ask-a-forums-question.aspx

I quote from Jonathan's post:

While answering posts on the MSDN Forums in the last year, I have noted a number of times where people have made posts with statements like:[snip]
"I would like MVP or product engineer level confirmation that my ordering assumption is correct."
While this might seem like an acceptable request, to someone who works the forums and knows a good bit of information about SQL Server, this could seem like an arrogant statement. [snip] In fact, it will actually turn people who might otherwise have the right answer away from your post simply because your question makes it seem like you aren't interested in what they have to say, or what they might know.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 11

lmu92 (3/6/2010)
clive-421796 (3/6/2010)
Would like to hear from the likes of Jeff Moden, Lynn Pettis, etc. if they have a solution for this.
I'm by far not at the level of Jeff or Lynn, but I'll have a look at it if you're interested.
But since you specifically requested one of the guru's I'm not sure if it's worth the effort... :Whistling:

Clive - you're overestimating the difficulty of this challenge, and underestimating Lutz' skill.

This should get you started...

drop table #mytable

CREATE TABLE #mytable

(

ID INT not null,

Stats varchar(15) not null,

EntryDate DATETIME not null

)

--===== Insert data into test table

INSERT INTO #mytable

(ID, Stats, EntryDate)

SELECT '13','Failure','2010-02-03 09:39:24'

UNION ALL

SELECT '13','Failure','2010-02-03 09:49:24'

UNION ALL

SELECT '13','Success','2010-02-03 09:59:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:09:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:19:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:29:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:39:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:49:24'

UNION ALL

SELECT '13','Success','2010-02-03 10:59:24'

UNION ALL

SELECT '13','Success','2010-02-03 11:09:24'

UNION ALL

SELECT '13','Failure','2010-03-03 08:39:27'

UNION ALL

SELECT '13','Failure','2010-03-03 08:49:27'

UNION ALL

SELECT '13','Failure','2010-03-03 08:59:27'

UNION ALL

SELECT '13','Success','2010-03-03 09:09:27'

UNION ALL

SELECT '13','Failure','2010-03-03 09:19:27'

UNION ALL

SELECT '13','Success','2010-03-03 09:29:27'

-- some extra rows to examine effect of introducing another ID

UNION ALL

SELECT '14','Failure','2010-03-03 08:39:27'

UNION ALL

SELECT '14','Failure','2010-03-03 08:49:27'

UNION ALL

SELECT '14','Failure','2010-03-03 08:59:27'

UNION ALL

SELECT '14','Success','2010-03-03 09:09:27'

UNION ALL

SELECT '14','Failure','2010-03-03 09:19:27'

UNION ALL

SELECT '14','Success','2010-03-03 09:29:27'

;WITH OrderedSource AS (

SELECT ExecSeq = ROW_NUMBER() OVER (ORDER BY ID, EntryDate),

RowsPerID = COUNT(ID) OVER(PARTITION BY ID), *

FROM #mytable),

Calculator AS (

SELECT 0 AS CountMe,

0 AS KeepMe,

ExecSeq, RowsPerID, ID, Stats, EntryDate

FROM OrderedSource

WHERE ExecSeq = 1

UNION ALL

SELECT CountMe = CASE

WHEN lr.Stats = 'Failure' AND cr.Stats = 'Failure' THEN 1

WHEN lr.Stats = 'Failure' AND cr.Stats = 'Success' AND cr.ExecSeq <> cr.RowsPerID THEN 1

ELSE 0 END,

KeepMe = CASE

WHEN lr.Stats = 'Failure' AND cr.Stats = 'Success' AND cr.ExecSeq <> cr.RowsPerID THEN 1

ELSE 0 END,

cr.ExecSeq, cr.RowsPerID, cr.ID, cr.Stats, cr.EntryDate

FROM OrderedSource cr

INNER JOIN Calculator lr ON lr.ExecSeq+1 = cr.ExecSeq

)

SELECT * FROM Calculator

Not bad for a beginner hey?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

clive-421796 (3/6/2010)
Would like to hear from the likes of Jeff Moden, Lynn Pettis, etc. if they have a solution for this.

Yowch! Really bad form, Clive. You've just insulted 1.2 million other members on this forum. :sick:

--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)

LutzM SSC Guru Points: 107049 More actions · Answer 13

@chris-2: You're using a CTE? How comes?

I would have used a CTE, too. But not as a recursive CTE.

Here's how I'd do it (return the identical data like your query does):

;WITH cte AS (

SELECT ExecSeq = ROW_NUMBER() OVER (ORDER BY ID, EntryDate),

RowsPerID = COUNT(ID) OVER(PARTITION BY ID), *

FROM #mytable)

SELECT CountMe = CASE

WHEN cte1.Stats = 'Failure' AND cte2.Stats = 'Failure' THEN 1

WHEN cte2.Stats = 'Failure' AND cte1.Stats = 'Success' AND cte1.ExecSeq <> cte1.RowsPerID THEN 1

ELSE 0 END,

KeepMe = CASE

WHEN cte2.Stats = 'Failure' AND cte1.Stats = 'Success' AND cte1.ExecSeq <> cte1.RowsPerID THEN 1

ELSE 0 END,

cte1.*

FROM cte cte1

LEFT OUTER JOIN cte cte2

ON cte1.ExecSeq = cte2.ExecSeq + 1

Maybe we can get Jeff or Lynn (sorry, Gail, you seem to be excluded as well 😉 ) to grab one of our solutions and re-post it under their name so it will get noticed...

@Clive: if my answer from above sounds a little sarcastic to you: that's intentional.

Just imagine the following scenario: Neither Chris nor Kingston nor myself replied to your question because it seems like you're not interested in our solutions and neither Jeff nor Lynn would have answered either.

What would you have done?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

It would appear that an update to the "Forum Etiquette" article would be both appropriate and long overdue. :Whistling:

--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)