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;