Averaging data streaks

• keymoo

  SSCrazy

  Points: 2975

  More actions

  November 22, 2010 at 5:34 am

  #388278

  Hi,

  I have the following sample data:

  -- schema

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[accountPerformance]') AND type in (N'U'))

  DROP TABLE [dbo].[accountPerformance]

  GO

  CREATE TABLE [dbo].[accountPerformance](

  [tradeId] [int] NOT NULL,

  [pctAccountRisked] [decimal](4, 2) NOT NULL,

  [profit] [decimal](38, 6) NULL,

  [tradeDateTime] [datetime] NULL,

  CONSTRAINT [PK_accountPerformance] PRIMARY KEY NONCLUSTERED

  (

  [tradeId] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  -- sample data

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(78,2.00,106.000000,'Jan 4 2010 7:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(63,2.00,-42.000000,'Jan 6 2010 3:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(17,2.00,-328.000000,'Jan 7 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(64,2.00,-153.000000,'Jan 14 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(79,2.00,211.000000,'Jan 18 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(18,2.00,32.000000,'Jan 18 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(65,2.00,143.000000,'Jan 19 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(1,2.00,110.000000,'Jan 19 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(39,2.00,-158.000000,'Jan 27 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(80,2.00,10.000000,'Jan 27 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(40,2.00,-112.000000,'Jan 28 2010 3:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(2,2.00,104.000000,'Jan 28 2010 3:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(19,2.00,218.000000,'Jan 29 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(3,2.00,463.000000,'Feb 3 2010 7:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(20,2.00,394.000000,'Feb 4 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(66,2.00,605.000000,'Feb 4 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(41,2.00,-108.000000,'Feb 10 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(67,2.00,-66.000000,'Feb 11 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(4,2.00,-50.000000,'Feb 15 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(42,2.00,101.000000,'Feb 17 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(21,2.00,432.000000,'Feb 18 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(23,2.00,174.000000,'Feb 23 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(5,2.00,97.000000,'Feb 23 2010 3:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(81,2.00,796.000000,'Feb 24 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(43,2.00,71.000000,'Feb 25 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(82,2.00,81.000000,'Mar 10 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(44,2.00,-24.000000,'Mar 12 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(83,2.00,-369.000000,'Mar 15 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(6,2.00,59.000000,'Mar 19 2010 3:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(24,2.00,282.000000,'Mar 23 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(84,2.00,-158.000000,'Mar 23 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(45,2.00,229.000000,'Mar 24 2010 11:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(46,2.00,232.000000,'Mar 30 2010 3:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(68,2.00,494.000000,'Mar 31 2010 3:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(7,2.00,168.000000,'Apr 6 2010 4:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(85,2.00,8.000000,'Apr 8 2010 7:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(25,2.00,14.000000,'Apr 13 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(69,2.00,98.000000,'Apr 13 2010 7:00:00:000PM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(26,2.00,-78.000000,'Apr 20 2010 7:00:00:000AM')

  INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(8,2.00,259.000000,'Apr 20 2010 3:00:00:000PM')

  go

  I have written this query to find the winning trades and the losing trades:

  SELECT

  [tradeId]

  , case when [profit] >=0 then 'W' else 'L' end as WinLoss

  FROM accountPerformance

  order by tradeDateTime

  A winning trade is defined by profit >=0 and a losing trade is profit < 0. I want to produce a results set that has the counts of the losing streaks in the form 3,1,1,3,1, etc. So for our dataset and query above we get the following results:

  tradeId WinLoss

  ----------- -------

  78 W

  63 L

  17 L

  64 L

  79 W

  18 W

  65 W

  1 W

  39 L

  80 W

  40 L

  2 W

  19 W

  3 W

  20 W

  66 W

  41 L

  67 L

  4 L

  42 W

  21 W

  23 W

  5 W

  81 W

  43 W

  82 W

  44 L

  83 L

  6 W

  24 W

  84 L

  45 W

  46 W

  68 W

  7 W

  85 W

  25 W

  69 W

  26 L

  8 W

  I want to see the Ls added up ordered by tradeDateTime. So the results should look like:

  3

  1

  1

  3

  2

  1

  1

  Is there a nice elegant way to do this?

  Thanks!

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  November 22, 2010 at 5:53 am

  #1253674

  ;with cte

  as

  (

  select *,ROW_NUMBER()over(order by (select 1))-row_number()over(partition by winloss order by winloss)rid from

  (

  select

  tradeDateTime,[tradeId]

  , case when [profit] >=0 then 'W' else 'L' end as WinLoss

  from accountPerformance

  )T

  )

  select count(WinLoss)as LosingStreak from cte where WinLoss='L'

  group by rid

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• keymoo

  SSCrazy

  Points: 2975

  More actions

  November 22, 2010 at 6:16 am

  #1253692

  Thanks it doesn't give me the correct results for dates that are the same. Is there a tweak needed to do that?

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  November 22, 2010 at 7:31 am

  #1253733

  keymoo (11/22/2010)

  ---

  Thanks it doesn't give me the correct results for dates that are the same. Is there a tweak needed to do that?

  Please post some sample data and expected result for the above condition.

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• Jeff Moden

  SSC Guru

  Points: 1004354

  More actions

  November 22, 2010 at 7:53 am

  #1253746

  Sachin Nandanwar (11/22/2010)

  ---

  keymoo (11/22/2010)

  ---

  Thanks it doesn't give me the correct results for dates that are the same. Is there a tweak needed to do that?

  Please post some sample data and expected result for the above condition.

  I'm thinking the sample data is already there...

  keymoo (11/22/2010)

  ---

  Hi,

  I have the following sample data:

  @keymoo,

  I agree with Sachin on the expected result... it's not quite clear what you actually need. Could you post the expected results that match your sample data? Thanks.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• keymoo

  SSCrazy

  Points: 2975

  More actions

  November 22, 2010 at 8:00 am

  #1253753

  Here's some more sample data

  DELETE [accountperformance]

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(1,3.00,110.000000,'Jan 19 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(2,1.80,104.000000,'Jan 28 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(3,3.00,463.000000,'Feb 3 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(4,0.94,-50.000000,'Feb 15 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(5,3.00,97.000000,'Feb 23 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(6,1.80,59.000000,'Mar 19 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(7,3.00,168.000000,'Apr 6 2010 4:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(8,1.80,259.000000,'Apr 20 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(9,1.80,141.000000,'Apr 27 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(10,3.00,853.000000,'May 3 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(11,3.00,473.000000,'May 11 2010 11:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(12,1.80,-26.000000,'Jul 5 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(13,3.00,-161.000000,'Jul 7 2010 11:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(14,1.80,377.000000,'Jul 14 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(15,3.00,-114.000000,'Aug 20 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(16,0.94,-111.000000,'Sep 8 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(17,1.80,-328.000000,'Jan 7 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(18,3.00,32.000000,'Jan 18 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(19,1.21,218.000000,'Jan 29 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(20,3.00,394.000000,'Feb 4 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(21,3.00,432.000000,'Feb 18 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(23,3.00,174.000000,'Feb 23 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(24,3.00,282.000000,'Mar 23 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(25,3.00,14.000000,'Apr 13 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(26,3.00,-78.000000,'Apr 20 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(27,3.00,-119.000000,'Apr 26 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(28,3.00,941.000000,'May 3 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(29,1.80,-155.000000,'Jun 8 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(30,3.00,-124.000000,'Jun 18 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(31,3.00,131.000000,'Jun 25 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(32,3.00,647.000000,'Jul 23 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(33,1.80,-150.000000,'Aug 31 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(35,3.00,-100.000000,'Sep 7 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(36,3.00,71.000000,'Sep 15 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(37,1.80,168.000000,'Sep 23 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(38,3.00,837.000000,'Sep 15 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(39,3.00,-158.000000,'Jan 27 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(40,3.00,-112.000000,'Jan 28 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(41,1.80,-108.000000,'Feb 10 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(42,3.00,101.000000,'Feb 17 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(43,3.00,71.000000,'Feb 25 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(44,3.00,-24.000000,'Mar 12 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(45,3.00,229.000000,'Mar 24 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(46,3.00,232.000000,'Mar 30 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(47,3.00,102.000000,'Apr 22 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(48,1.21,56.000000,'Apr 30 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(49,1.21,469.000000,'Jul 13 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(50,3.00,-94.000000,'May 4 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(51,3.00,-12.000000,'May 14 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(52,1.80,71.000000,'May 18 2010 11:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(53,3.00,176.000000,'May 20 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(54,1.21,7.000000,'Jun 17 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(55,3.00,90.000000,'Jun 23 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(56,3.00,214.000000,'Jul 15 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(57,3.00,-14.000000,'Jul 30 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(58,3.00,20.000000,'Aug 5 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(59,3.00,105.000000,'Aug 24 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(60,3.00,-46.000000,'Aug 31 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(61,1.21,-35.000000,'Sep 7 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(62,3.00,31.000000,'Sep 28 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(63,3.00,-42.000000,'Jan 6 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(64,0.78,-153.000000,'Jan 14 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(65,3.00,143.000000,'Jan 19 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(66,3.00,605.000000,'Feb 4 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(67,1.21,-66.000000,'Feb 11 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(68,3.00,494.000000,'Mar 31 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(69,3.00,98.000000,'Apr 13 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(70,1.80,1239.000000,'May 4 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(71,3.00,731.000000,'May 11 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(72,3.00,282.000000,'May 20 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(73,3.00,-70.000000,'Jun 8 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(74,3.00,276.000000,'Jun 28 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(75,3.00,-92.000000,'Jul 13 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(76,3.00,683.000000,'Aug 20 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(77,0.94,96.000000,'Sep 7 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(78,3.00,106.000000,'Jan 4 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(79,3.00,211.000000,'Jan 18 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(80,3.00,10.000000,'Jan 27 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(81,3.00,796.000000,'Feb 24 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(82,3.00,81.000000,'Mar 10 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(83,3.00,-369.000000,'Mar 15 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(84,3.00,-158.000000,'Mar 23 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(85,3.00,8.000000,'Apr 8 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(86,3.00,499.000000,'Apr 20 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(87,1.80,-147.000000,'Apr 29 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(88,3.00,180.000000,'May 5 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(89,3.00,-37.000000,'May 24 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(90,1.21,199.000000,'May 31 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(91,1.80,-170.000000,'Jun 18 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(92,3.00,738.000000,'Jun 25 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(93,1.21,33.000000,'Jul 7 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(94,1.21,226.000000,'Aug 3 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(95,3.00,74.000000,'Aug 19 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(96,3.00,-68.000000,'Aug 26 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(97,1.80,231.000000,'Aug 31 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(98,3.00,-364.000000,'Sep 22 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(99,3.00,77.000000,'Jan 6 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(100,3.00,-62.000000,'Jan 28 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(101,3.00,-174.000000,'Feb 8 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(102,0.78,130.000000,'Feb 17 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(103,3.00,100.000000,'Mar 5 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(105,1.80,1660.000000,'Mar 24 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(106,3.00,37.000000,'Apr 22 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(107,3.00,-103.000000,'Apr 29 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(108,3.00,820.000000,'May 5 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(109,3.00,115.000000,'May 14 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(110,3.00,238.000000,'May 20 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(111,3.00,660.000000,'Jun 28 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(112,3.00,187.000000,'Jul 16 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(113,1.80,-197.000000,'Jul 30 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(114,3.00,220.000000,'Aug 10 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(115,3.00,781.000000,'Aug 20 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(116,3.00,104.000000,'Jan 6 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(117,1.80,-54.000000,'Jan 28 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(118,3.00,457.000000,'Feb 4 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(119,3.00,2.000000,'Feb 18 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(120,3.00,161.000000,'Mar 29 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(121,3.00,1141.000000,'May 14 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(122,3.00,614.000000,'Jun 29 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(123,3.00,-128.000000,'Jul 27 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(124,3.00,185.000000,'Aug 24 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(125,0.78,314.000000,'Sep 9 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(126,0.67,349.000000,'Jan 14 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(127,1.80,30.000000,'Jan 27 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(128,3.00,559.000000,'Feb 25 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(129,3.00,72.000000,'Mar 9 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(130,3.00,139.000000,'Apr 1 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(131,3.00,105.000000,'Apr 21 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(132,3.00,186.000000,'May 4 2010 7:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(133,3.00,140.000000,'Jun 3 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(134,3.00,59.000000,'Jun 24 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(135,1.80,-104.000000,'Jul 9 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(136,3.00,121.000000,'Aug 11 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(137,1.21,-16.000000,'Sep 3 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(138,3.00,226.000000,'Sep 21 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(139,1.21,-148.000000,'Jan 8 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(140,3.00,80.000000,'Jan 26 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(141,3.00,672.000000,'Feb 4 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(142,3.00,2278.000000,'Feb 23 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(143,3.00,482.000000,'Mar 30 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(144,3.00,804.000000,'May 11 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(145,1.80,-221.000000,'May 25 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(146,3.00,-12.000000,'Jun 30 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(147,1.80,122.000000,'Jul 27 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(148,1.80,366.000000,'Aug 20 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(149,1.80,-81.000000,'Sep 7 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(150,0.94,-126.000000,'Jan 13 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(152,3.00,65.000000,'Jan 26 2010 3:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(153,3.00,236.000000,'Feb 4 2010 7:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(154,1.80,70.000000,'Mar 12 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(155,3.00,28.000000,'Apr 13 2010 11:00:00:000AM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(156,3.00,69.000000,'Apr 27 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(157,3.00,468.000000,'May 3 2010 3:00:00:000PM')

  INSERT INTO [accountperformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(158,1.21,880.000000,'Jun 22 2010 7:00:00:000AM')

  The first four values should be:

  5,1,1,2

  But with your query I get

  1,2,3,2

  I think it's because your query is not ordering the set by tradeDateTime, is that right? I don't think it does have anything to do with duplicate dates after all. Thanks again for looking.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  November 22, 2010 at 8:17 am

  #1253776

  WITH CTE AS (

  SELECT [tradeDateTime],

  CASE WHEN [profit] >=0 THEN 'W' ELSE 'L' END AS WinLoss,

  ROW_NUMBER() OVER(ORDER BY [tradeDateTime])-

  ROW_NUMBER() OVER(PARTITION BY CASE WHEN [profit] >=0 THEN 'W' ELSE 'L' END ORDER BY [tradeDateTime]) AS rnDiff

  FROM [accountperformance])

  SELECT COUNT(*)

  FROM CTE

  WHERE WinLoss='L'

  GROUP BY rnDiff

  ORDER BY MIN([tradeDateTime]);

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• keymoo

  SSCrazy

  Points: 2975

  More actions

  November 22, 2010 at 8:33 am

  #1253796

  Awesome works great thanks