Counting maximum number of consecutive wins/losses

• Wayne-950947

  Ten Centuries

  Points: 1122

  More actions

  May 23, 2010 at 12:14 pm

  #221437

  Hi folks

  A simple question, but after several hours of searching I don't have a workable solution - and this is something I'm convinced I've solved before. For a trading system, I need to count the maximum consecutive number of winners and losers. Data below.

  The P_OR_L column is not strictly necessary. I added it when trying the partition by function.

  The PNL column should be sufficient, where a profit is >=0 and a loss is < 0

  CREATE TABLE #t_TRDS(

  TRD_EXT_DT DATE,

  COMM_SYMB NVARCHAR(3),

  PNL MONEY,

  P_OR_L CHAR(1))

  INSERT INTO #t_TRDS

  SELECT '2007-03-28', 'OJ', -1462.5, 'L' UNION ALL

  SELECT '2007-04-02', 'CC', -1150, 'L' UNION ALL

  SELECT '2007-04-09', 'CT', -1030, 'L' UNION ALL

  SELECT '2007-04-12', 'FC', 540, 'P' UNION ALL

  SELECT '2007-04-18', 'BO', -756, 'L' UNION ALL

  SELECT '2007-04-19', 'CC', -330, 'L' UNION ALL

  SELECT '2007-04-30', 'CC', -950, 'L' UNION ALL

  SELECT '2007-04-30', 'NK', -1875, 'L' UNION ALL

  SELECT '2007-05-02', 'SI', -4025, 'L' UNION ALL

  SELECT '2007-05-03', 'AD', 1620, 'P' UNION ALL

  SELECT '2007-05-04', 'SI', -200, 'L' UNION ALL

  SELECT '2007-05-07', 'CL', -3890, 'L' UNION ALL

  SELECT '2007-05-08', 'SF', -1237.5, 'L' UNION ALL

  SELECT '2007-05-09', 'CL', -1010, 'L' UNION ALL

  SELECT '2007-05-10', 'EC', -100, 'L' UNION ALL

  SELECT '2007-05-10', 'BP', -1850, 'L' UNION ALL

  SELECT '2007-05-10', 'GC', -1770, 'L' UNION ALL

  SELECT '2007-05-11', 'KW', -787.5, 'L' UNION ALL

  SELECT '2007-05-14', 'HO', -1807.8, 'L' UNION ALL

  SELECT '2007-05-14', 'PA', -180, 'L' UNION ALL

  SELECT '2007-05-16', 'PA', -395, 'L' UNION ALL

  SELECT '2007-05-16', 'GC', -1380, 'L' UNION ALL

  SELECT '2007-05-16', 'HG', 575, 'P' UNION ALL

  SELECT '2007-05-16', 'CT', 1510, 'P' UNION ALL

  SELECT '2007-05-17', 'BP', -800, 'L' UNION ALL

  SELECT '2007-05-21', 'EC', -1087.5, 'L' UNION ALL

  SELECT '2007-05-21', 'HG', 2050, 'P' UNION ALL

  SELECT '2007-05-22', 'AD', -390, 'L' UNION ALL

  SELECT '2007-05-23', 'AD', 80, 'P' UNION ALL

  SELECT '2007-05-23', 'RB', 11376, 'P' UNION ALL

  SELECT '2007-05-24', 'PL', 2565, 'P' UNION ALL

  SELECT '2007-05-28', 'PL', -300, 'L' UNION ALL

  SELECT '2007-05-30', 'CT', -895, 'L' UNION ALL

  SELECT '2007-06-01', 'SI', -4350, 'L'

  For this data set, the expected outcome is:

  Maximum number of consecutive wins = 3 (May 23, 24)

  Maximum number of consecutive losses = 12 (May 4th to May 16th)

  Thanks in advance,

  Wayne

• LutzM

  SSC Guru

  Points: 107049

  More actions

  May 23, 2010 at 1:49 pm

  #1171321

  Hi Wayne,

  here's what I came up with (side note: I added an ID column to your original table to keep the order the data were inserted):

  ;WITH CTE AS

  (

  SELECT p_or_l,

  id - ROW_NUMBER() OVER(PARTITION BY P_or_L ORDER BY id) AS rnDiff

  FROM #t_TRDS

  )

  , cte_subtotal AS

  (

  SELECT

  p_or_l, COUNT(rndiff) AS cnt

  FROM CTE GROUP BY p_or_l,rnDiff

  )

  SELECT p_or_l,MAX(cnt) as max_p_or_l

  FROM cte_subtotal

  GROUP BY p_or_l

  /* result set

  p_or_lmax_p_or_l

  L12

  P3

  */

  I "borrowed" and modified the basic concept from Mark's post

  Edit: unused columns removed from cte.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Wayne-950947

  Ten Centuries

  Points: 1122

  More actions

  May 23, 2010 at 2:15 pm

  #1171326

  Thx Lutz - that nails it!

• LutzM

  SSC Guru

  Points: 107049

  More actions

  May 23, 2010 at 2:18 pm

  #1171328

  You're very welcome 😀

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]