T-SQL Question - Find largest sequence

Question

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.

Answer 1

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

Answer 2

0

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.

answered Nov 26 '11 at 08:45 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

I am actively digesting this article thank you.

Nov 26 '11 at 01:33 PM Frank Zappa

(comments are locked)

Answer 3

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

T-SQL Question - Find largest sequence

Follow this question

Related Questions