ROW_NUMBER() Based on Dates

user_5560898 0
Viewed 419

I have the following data:

test_date
2018-07-01
2018-07-02
...
2019-06-30
2019-07-01
2019-07-02
...
2020-06-30
2020-07-01

I want to increment a row_number value every time right(test_date,5) = '07-01' so that my final result looks like this:

test_date    row_num
2018-07-01         1
2018-07-02         1
...                1
2019-06-30         1
2019-07-01         2
2019-07-02         2
...                2
2020-06-30         2
2020-07-01         3

I tried doing something like this:

, ROW_NUMBER() OVER (
    PARTITION BY CASE WHEN RIGHT(a.[test_date],5) = '07-01' THEN 1 ELSE 0 END
    ORDER BY a.[test_date]
) AS [test2]

But that did not work out for me.

Any suggestions?

tsql sql-server sql
3 Answers

Use datepart to identify the correct date, and then add 1 to a sum every time it changes (assuming there will never be more than 1 row per date).

declare @Test table (test_date date);

insert into @Test (test_date)
values
('2018-07-01'),
('2018-07-02'),
('2019-06-30'),
('2019-07-01'),
('2019-07-02'),
('2020-06-30'),
('2020-07-01');

select *
  , sum(case when datepart(month,test_date) = 7 and datepart(day,test_date) = 1 then 1 else 0 end) over (order by test_date asc) row_num
from @Test
order by test_date asc;

Returns:

test_date row_num
2018-07-01 1
2018-07-02 1
2019-06-30 1
2019-07-01 2
2019-07-02 2
2020-06-30 2
2020-07-01 3
user_11274280

You can do it with DENSE_RANK() window function if you subtract 6 months from your dates:

SELECT test_date,
       DENSE_RANK() OVER (ORDER BY YEAR(DATEADD(month, -6, test_date))) row_num
FROM tablename

See the demo.
Results:

test_date  | row_num
---------- | -------
2018-07-01 |       1
2018-07-02 |       1
2019-06-30 |       1
2019-07-01 |       2
2019-07-02 |       2
2020-06-30 |       2
2020-07-01 |       3
user_104988280

build a running total based on month=7 and day=2

 declare @Test table (mykey int,test_date date);

 insert into @Test (mykey,test_date)
 values
  (1,'2018-07-01'),
 (2,'2018-07-02'),
 (3,'2019-06-30'),
 (4,'2019-07-01'),
 (5,'2019-07-02'),
 (6,'2020-06-30'),
 (7,'2020-07-01');

 select mykey,test_date, 
 sum(case when DatePart(Month,test_date)=7 and DatePart(Day,test_date)=2 then 1 else 0 end) over (order by mykey) RunningTotal from @Test
 order by mykey
user_40011770
Related