SQL Frequency Count resetting for each year-CodePudding

I want to count the frequency of occurrences of a variable based on the order of the date - which I can do. But I want the frequency count to reset for each year- I can't get it to reset.

The table looks like the below but I've manually added the Frequency:

Date / Variable / Year / Frequency
2019-01-01 / blue / 2019 / 1
2019-01-15 / blue / 2019 / 2
2019-01-01 / red / 2019 / 1
2019-04-01 / blue / 2019 / 3
2019-06-01 / red / 2019 / 2
2020-01-01 / blue / 2020 / 1
2020-04-01 / red / 2020 / 1
2020-01-21 / blue / 2020 / 2
2020-06-01 / blue / 2020 / 3
2020-08-01 / red / 2020 / 2
2021-01-21 / blue / 2021 / 1
2021-02-21 / red / 2021 / 1

This how I did the original count but this ignores the reset for each year:

UPDATE A
SET A.Frequency = 
(SELECT COUNT(*) FROM #TableB B 
WHERE B.Date <= A.Date 
AND B.Variable = A.Variable)
FROM #TableA A

I've tried adding in WHERE clauses i.e.

WHERE A.Date BETWEEN '2019-01-01' AND '2019-12-31'

And adding in a year column and limiting that way i.e.

UPDATE A
SET A.Frequency = 
(SELECT COUNT(*) FROM #TableB B 
WHERE B.Date <= A.Date 
AND B.Variable = A.Variable
AND B.Year = '2020')
FROM #TableA A

Any tips would be greatly appreciated! Thanks

CodePudding user response：

You can do this with row_number()

DBFiddle

select a.[date], 
       a.variable, 
       a.year,
       row_number() over (partition by a.variable, a.year order by [date]) AS frequency
from   tableA a
order by a.id

result

date        variable    year    frequency
2019-01-01  blue        2019    1
2019-01-15  blue        2019    2
2019-01-01  red         2019    1
2019-04-01  blue        2019    3
2019-06-01  red         2019    2
2020-01-01  blue        2020    1
2020-04-01  red         2020    1
2020-01-21  blue        2020    2
2020-06-01  blue        2020    3
2020-08-01  red         2020    2

EDIT
How to use this in an update:

update aa
set    aa.frequency = aaa.frequency
from   tableA aa
  inner join ( select a.[date], a.variable, a.year,
                      row_number() over (partition by a.variable, a.year order by [date]) AS frequency
               from   tableA a
             ) aaa on aa.[date] = aaa.[date]
                  and aa.variable = aaa.variable
                  and aa.year = aaa.year

See this DBFiddle