I want Pdate column as an output. I have these three attributes: id,Bonus & Date. I have to get an output of date column such that the column shows the previous dates when employee received 20 or more than 20 euros corresponding to the normal dates when employee got bonus. Take a look at the below table for more understanding of the question:
id Bonus Date my_output Pdate(Required Output)
1 15 "2017-06-20" "2017-04-17"
1 10 "2017-05-22" "2017-04-17"
1 20 "2017-04-17" "2017-04-17" "2017-03-20"
1 20 "2017-03-20" "2017-03-20" NULL
2 15 "2017-02-20" "2017-01-28"
2 25 "2017-01-28" "2017-01-28" NULL
So, as you can see for first row, Bonus is 15, since we want bonus more or equal to 20, so at "2017-04-17" ,for id 1, bonus was 20. Hence, Pdate has that date. And in the fourth row, since there is no previous date on which bonus was according to the condition for user 1, the pdate is null.
select id,Bonus_value as Bonus,
(case when Bonus_value>=0 then Bonus_date end) as date,
(case when Bonus_value>=20 then Bonus_date end) as my_output
from Bonus
group by id,Bonus_value,Bonus_date
order by id,Bonus_date desc
In this code, there is no pdate as i dont know how to get that column. Thats what i want. I thought of using lead() window function , still i dont know how to get it corresponding to the date column.
In standard SQL, you can use the ignore nulls option:
select t.*,
lag(case when bonus >= 20 then date end ignore nulls) over (partition by id order by date) as pdate
from t;
Not all databases support this option, so you can also use max() with a window clause:
max(case when bonus >= 20 then date end ignore nulls) over (partition by id order by date rows between unbounded preceding and 1 preceding) as pdate
from t;