I have data where it has specific date (yyyy-mm-dd) with its city and value
-------------------------------------
city | date | value
-------------------------------------
city A | 2018-05-01 | 10
city A | 2018-04-03 | 15
city A | 2019-01-03 | 9
city A | 2019-05-01 | 13
city B | 2019-05-01 | 12
city B | 2019-02-12 | 11
and I would like to get the first value for each year partitioned by city in the next column :
--------------------------------------------------
city | date | value | first_value_year_by_city
--------------------------------------------------
city A | 2018-05-01 | 10 | 15
city A | 2018-04-03 | 15 | 15
city A | 2019-01-03 | 9 | 9
city A | 2019-05-01 | 13 | 9
city B | 2019-05-01 | 12 | 11
city B | 2019-02-12 | 11 | 11
For that I used SQL :
select *,
,first_value(value) over(partition by city order by date rows between unbounded preceding and unbounded following) as first_value_year_by_city
from table
But I couldn't put datepart(year,date) = 2018 and datepart(year,date) = 2019 in the first_value(). How we can do this?
You can put the year in the partition by
:
select t.*,
first_value(value) over (partition by city, year(date)
order by date
) as first_value_year_by_city
from table t;