Creating a running identifier variable in SQL Impala

Lisa 0
Viewed 20

I have a variable trade_date that occurs several times in the dataset. I would like to create a variable trade_day that uniquely identifies the trading days by skipping trading days where no trade has occured (e.g. 2016-07-03). The trade_date variable is in date format. How would you do it in SQL Impala?

trade_date trade_day
2016-07-01 20636
2016-07-01 20636
2016-07-01 20636
2016-07-02 20637
2016-07-02 20637
2016-07-04 20639
2016-07-04 20639
2016-07-04 20639
sql
1 Answers

In case anyone is interested. The solution is

SELECT trade_date,  datediff(CAST(trade_date AS DATE), CAST('2016-07-01' AS DATE)) + 20636 AS trade_day
FROM datashop
GROUP BY trade_date
ORDER BY trade_day
Lisa0
Related