x

T-SQL Question - Find largest sequence

Here is my table:

CREATE TABLE [dbo].[TEMP_PCT_GAIN_DAILY](
[Symbol] [varchar](10) NOT NULL,
[PctGain] [real] NULL,
[DSeqkey] [int] NOT NULL
) ON [PRIMARY]

5 rows of sample data (from a total of 15.5K rows):

 !GSPC   -0.02209521     20881  
 !GSPC   -0.004140842    20880  
 !GSPC    0.0186485      20879  
 !GSPC   -0.0003946786   20878  
 !GSPC    0.01679995     20877

Symbol varies, PctGain maybe pos/neg and DSeqkey is an arbitrary unique integer representing each trading day.

I want SQL that will return the number of DSeqKeys for the longest contiguous sequence of negative PctGains.
more ▼

asked Nov 26, 2011 at 07:16 AM in Default

Frank Zappa gravatar image

Frank Zappa
21 1 1 1

Do you want the longest sequence for each symbol, or should we ignore the symbol? Second, do you simply want the number of DSeqKeys or do you actually want to return the DSeqKeys that make up the sequence?
Nov 26, 2011 at 12:17 PM Scot Hauder
Thank you Scott - For each Symbol would be ideal. Just the number of DSeqKeys is fine.
Nov 26, 2011 at 01:32 PM Frank Zappa
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Test this on your data, I can make adjustments as needed.

;WITH MoonUnit AS (
    SELECT Symbol, PctGain, DSeqKey,
           rn = ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY DSeqKey) 
    FROM dbo.TEMP_PCT_GAIN_DAILY
) 
, Dweezil AS (
    SELECT rn, Symbol, PctGain, DSeqKey, sgn = SIGN(PctGain), Streak = 1
    FROM MoonUnit r1
    WHERE rn = 1
    UNION ALL
    SELECT MoonUnit.rn, MoonUnit.Symbol, MoonUnit.PctGain, MoonUnit.DSeqkey, SIGN(MoonUnit.PctGain),
        CASE WHEN SIGN(MoonUnit.PctGain) <> SIGN(Dweezil.PctGain)
             THEN 1
             ELSE Streak + 1 
             END
    FROM Dweezil
    JOIN MoonUnit ON (MoonUnit.rn = Dweezil.rn + 1)
)
    SELECT Symbol, MAX(Streak) [Streak]
    FROM Dweezil
    WHERE sgn = -1
GROUP BY Symbol
more ▼

answered Nov 26, 2011 at 02:25 PM

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

+1 for object names :)
Nov 28, 2011 at 11:12 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Take a look at the logic here ( http://msdn.microsoft.com/en-us/library/aa175780(v=sql.80).aspx) and see if you can apply it to your problem. If not then come on back and tell us how far you got and we'll help further.
more ▼

answered Nov 26, 2011 at 08:45 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

I am actively digesting this article thank you.
Nov 26, 2011 at 01:33 PM Frank Zappa
(comments are locked)
10|1200 characters needed characters left

This will give you the answer. I can modify it to tell you where the start of the sequence was if you wish!

  CREATE TABLE #TEMP_PCT_GAIN_DAILY(
  [Symbol] [varchar](10) NOT NULL,
  [PctGain] [real] NULL,
  [DSeqkey] [int] NOT NULL
  )
  Delete FROM #TEMP_PCT_GAIN_DAILY
  INSERT INTO #TEMP_PCT_GAIN_DAILY
  SELECT 
                   '!GSPC',   -0.02209521,     20881 
  UNION ALL SELECT '!GSPC',   -0.004140842,    20880  
  UNION ALL SELECT '!GSPC',    0.0186485,      20879  
  UNION ALL SELECT '!GSPC',   -0.0003946786 ,  20878  
  UNION ALL SELECT '!GSPC',    0.01679995,     20877
  UNION ALL SELECT '!GSPC',    -0.03679995,     20876
  UNION ALL SELECT '!GSPC',    -0.02479995,     20875
  UNION ALL SELECT '!GSPC',    -0.01179995,     20874

  DECLARE @COUNT INT, @Max int
  SELECT @Count=0,@Max=0

  SELECT @COUNT=CASE WHEN pctGain<0 THEN @Count+1 ELSE 0 END,
         @Max=CASE WHEN @Count>@Max THEN @Count ELSE @Max END
   FROM #TEMP_PCT_GAIN_DAILY ORDER BY DseqKey

  SELECT @Count, @Max
more ▼

answered Dec 01, 2011 at 04:06 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x17
x6

asked: Nov 26, 2011 at 07:16 AM

Seen: 1830 times

Last Updated: Nov 26, 2011 at 08:42 AM