Skip to content
Advertisement

Running count SQL server

Can someone please help me in counting the rows in SQL Server

Id Date   Trend 
A 15-1-20 Uptrend
A 14-1-20 Uptrend
A 13-1-20 Uptrend
A 12-1-20 NULL
A 11-1-20 Uptrend
A 10-1-20 Uptrend
A 09-1-20 NULL

Expected result

Id Date   Trend    Counttrend
A 15-1-20 Uptrend      3
A 14-1-20 Uptrend      2
A 13-1-20 Uptrend      1
A 12-1-20 NULL        NULL
A 11-1-20 Uptrend      2
A 10-1-20 Uptrend      1
A 09-1-20 NULL        NULL


CREATE TABLE #TREND (ID Varchar(2),[DATE] Date ,TREND Varchar(10))

INSERT INTO #trend
  ( ID, [DATE], TREND )
VALUES
  ('A', '01-15-2020', 'Uptrend'), 
  ('A', '01-14-2020', 'Uptrend'), 
  ('A', '01-13-20', 'Uptrend'),
  ('A', '01-12-20', NULL),
  ('A', '01-11-20', NULL),
  ('A', '01-10-20', 'Uptrend'),
  ('A', '01-09-20', 'Uptrend');

Advertisement

Answer

Try this:

SELECT src.Id, src.[Date], src.Trend, 
  CASE
    WHEN Trend IS NULL THEN NULL
    ELSE ROW_NUMBER() OVER (PARTITION BY Id, Trend, MasterSeq-SubSeq ORDER BY [Date])
  END AS TrendCnt
FROM (
  SELECT *,  
    ROW_NUMBER() OVER(PARTITION BY Id ORDER BY [Date]) As MasterSeq,
    ROW_NUMBER() OVER(PARTITION BY Id, Trend ORDER BY [Date]) +1 As SubSeq
    FROM aaa
) src
ORDER BY [Date] DESC;
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement