March 7, 2010 at 1:51 am
lmu92 (3/6/2010)
@Chris: You're using a CTE? How comes?
I've been studying your solutions mate - finally saw the light 😛
You've posted a third method for resolving this job, late on a saturday night after a few beers because you had a spare 5 minutes. Puts it all into perspective 😎
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 7, 2010 at 2:31 am
ChrisM@home (3/7/2010)
...You've posted a third method for resolving this job, late on a saturday night after a few beers because you had a spare 5 minutes. Puts it all into perspective 😎
Do you think it's that bad or rather creative? Both can be read into your post...
Btw: How did you know I had to use my CWI login? Do I have to fear my system being hijacked?
March 7, 2010 at 3:20 am
lmu92 (3/7/2010)
ChrisM@home (3/7/2010)
...You've posted a third method for resolving this job, late on a saturday night after a few beers because you had a spare 5 minutes. Puts it all into perspective 😎
Do you think it's that bad or rather creative? Both can be read into your post...
Btw: How did you know I had to use my CWI login? Do I have to fear my system being hijacked?
Heh sorry for the ambiguity, Lutz. Creative of course!
I knew you were on your CWI login because you were in the "Who's On" list - since about 5pm GMT 😀
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 7, 2010 at 8:44 am
Hi LMU92
I refer to your post:
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.
I refer to my post:
Would like to hear from the likes of Jeff Moden, Lynn Pettis, etc. if they have a solution for this.
Seems like you misunderstood or misinterpreted me:
You will notice I mentioned Jeff Moden, Lynn Pettis, etc.
etc. pretty means anyone out there.
I meant that I would like some feedback from them, and whoever else.
Next time I will watch my wording at bit more.
But eh guys/girls, if you're not clear what I meant, just ask.
Lets please not turn this forum into a blame game or witch-hunt.
After reading a few replies about my post I think I need a tot of rum & grappa & vodka. Hmm....
Anyways, thanks to Kingston, ChrisM, LMU92 for your solution, much appreciated.
March 7, 2010 at 12:20 pm
Hi lmu92 and ChrisM,
The main purpose for creating the query was to get the Stats count and Time Difference
between 2nd failure and 1st success.
Below is listed the result of your queries (also attached in case the columns are not displayed ok).
CountMe KeepMe ExecSeq RowsPerID ID Stats EntryDate
00116 13Failure2010-02-03 09:39:24.000
10216 13Failure2010-02-03 09:49:24.000
11316 13Success2010-02-03 09:59:24.000
00416 13Success2010-02-03 10:09:24.000
00516 13Success2010-02-03 10:19:24.000
00616 13Success2010-02-03 10:29:24.000
00716 13Success2010-02-03 10:39:24.000
00816 13Success2010-02-03 10:49:24.000
00916 13Success2010-02-03 10:59:24.000
001016 13Success2010-02-03 11:09:24.000
001116 13Failure2010-03-03 08:39:27.000
101216 13Failure2010-03-03 08:49:27.000
101316 13Failure2010-03-03 08:59:27.000
111416 13Success2010-03-03 09:09:27.000
001516 13Failure2010-03-03 09:19:27.000
001616 13Success2010-03-03 09:29:27.000
00176 14Failure2010-03-03 08:39:27.000
10186 14Failure2010-03-03 08:49:27.000
10196 14Failure2010-03-03 08:59:27.000
11206 14Success2010-03-03 09:09:27.000
Below the CountMe and KeepMe is supposed to be 0, just as for ID 13.
00216 14Failure2010-03-03 09:19:27.000
11226 14Success2010-03-03 09:29:27.000
March 7, 2010 at 12:35 pm
No problem...
;WITH OrderedSource AS (
SELECT ExecSeq = ROW_NUMBER() OVER (ORDER BY ID, EntryDate),
RowOfID = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, EntryDate),
RowsPerID = COUNT(ID) OVER(PARTITION BY ID), *
FROM #mytable),
Calculator AS (
SELECT 0 AS CountMe,
0 AS KeepMe,
ExecSeq, RowOfID, 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.RowOfID <> cr.RowsPerID THEN 1
ELSE 0 END,
KeepMe = CASE
WHEN lr.Stats = 'Failure' AND cr.Stats = 'Success' AND cr.RowOfID <> cr.RowsPerID THEN 1
ELSE 0 END,
cr.ExecSeq, cr.RowOfID, cr.RowsPerID, cr.ID, cr.Stats, cr.EntryDate
FROM OrderedSource cr
INNER JOIN Calculator lr ON lr.ExecSeq+1 = cr.ExecSeq
)
SELECT * FROM Calculator
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 7, 2010 at 12:51 pm
clive-421796 (3/7/2010)
...Would like to hear from the likes of Jeff Moden, Lynn Pettis, etc. if they have a solution for this.
...
You will notice I mentioned Jeff Moden, Lynn Pettis, etc.
etc. pretty means anyone out there.
Unusual interpretation of set theory... 😉
Anyway, you've got more than one working solution. There was no blame game as far as I can see.
Feel free to come back to this site whenever you need help on SQL stuff. We'll be glad to help if we can. All of us. 'Nuff said as far as I'm concerned.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply