question

Frank Zappa avatar image
Frank Zappa asked

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.
sql-server-2008syntaxsequence
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
Thank you Scott - For each Symbol would be ideal. Just the number of DSeqKeys is fine.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I am actively digesting this article thank you.
1 Like 1 ·
Scot Hauder avatar image
Scot Hauder answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for object names :)
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.