| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
FuKoMatic
Starting Member
4 Posts |
 Posted - 03/01/2003 : 13:58:44
|
are these run and streaks at all related to the ones in my underwear?
|
 |
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 03/01/2003 : 14:06:05
|
quote:
are these run and streaks at all related to the ones in my underwear?
I'm not too sure but you must have a very small database....
DavidM
"SQL-3 is an abomination.." |
|
|
|
FuKoMatic
Starting Member
4 Posts |
Posted - 03/02/2003 : 01:36:57
|
hey now, it's not how big the database is, it's how you use the stored procedures
|
|
|
|
dstaikos
Starting Member
Greece
1 Posts |
Posted - 07/25/2003 : 07:34:11
|
Cool exercise :-)
I like doing things with SQL2000 functions, so here is a function that will return the length of a run at any random record.
Using the table definition:
create table TS(AA int not null identity, v int not null)
where AA is the order key and v the value the function is :
create function TSLen(@AA int) returns int
as
begin
declare @ret int, @V int
SELECT @V = v FROM TS WHERE AA = @AA
select @ret = count(*)+1 from ts as TS1
where TS1.AA < @AA
and TS1.v = @V
and TS1.AA > ISNULL(
(
select max(AA) from ts as TS1
where TS1.AA < @AA
and TS1.v <> @V
), 0)
return @ret
end
Then with a query like:
select AA, v, dbo.TSLen(AA) from ts order by AA ASC
you can see the data, and with a query like:
select AA, dbo.TSLen(AA) from ts where dbo.TSLen(AA) = (select max(dbo.TSLen(AA)) from ts)
you can see the AA of the records where the maximum run(s) have come to an end.
Enjoy!
Go with THE flow... |
|
|
|
schuhtl
Posting Yak Master
USA
102 Posts |
Posted - 10/18/2010 : 10:55:46
|
I realize that this is a fairly old artilce but any help I could get would be appreciated. The data that I am working with is very similar to the data used in this article however my data contains multiple results for the same day, player and event. I would like to find the winning streak of a player(s) that spans multiple events/dates. For example...the data below should show that player 1000 had a 4 game winning streak, player 2000 had a 5 game winning streak and player 3000 had a 3 game winning streak.
CREATE TABLE [dbo].[GameResults](
[TournamentId] [int] NULL,
[PlayerId] [int] NULL,
[GameDate] [date] NULL,
[Result] [varchar](5) NULL
)
INSERT INTO GameResults (TournamentId, PlayerId, GameDate,Result)
VALUES
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'W'),
(1, 1000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'L'),
(1, 2000,'1/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'W'),
(2, 1000,'2/1/2000', 'L'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'W'),
(2, 2000,'2/1/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'L'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 3000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'L'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W'),
(3, 2000,'2/15/2000', 'W') |
|
|
|
empire3
Starting Member
USA
1 Posts |
Posted - 05/03/2012 : 23:13:13
|
This is a great algorithm by Jeff Smith. Wondering if he has tried modifying it for numeric data to find runs numbers below some threshold. We need something to detect runs of daily temperatures below some cutoff.
For example, for every calendar year, show the runs of temperatures less than 32 degrees. Seems to me you need a subquery using case to convert the number data into high or low, based on the temperature cutoff. But I can't get this to work. |
|
|
| |
Topic |
|
|
|
Article: Detecting "Runs" or "Streaks" in your data
