For each year, for each employee, I want to list the status of the review that employee had submitted, or "not initiated" in case employee did not submit review for that year.
It's kind of difficult to express the question in words therefore I would try to explain it by giving example:
create table #employees
(
empid int,
name varchar(100)
)
Create table #review
(
empid int,
ryear int,
status varchar(20)
)
insert into #review values(1,2016,'S2')
insert into #review values(2,2016,'S2')
insert into #review values(2,2017,'S1')
insert into #review values(3,2017,'S2')
insert into #employees values(1,'jack')
insert into #employees values(2,'mack')
insert into #employees values(3,'rack')
insert into #employees values(4,'tack')
Wrong Query
select a.empid
,a.name
,b.ryear
,case isnull(b.status,'')
when ''
then 'Not Initiated'
else status
end as status
from #employees as a
left join #review as b
on a.empid = b.empid
and b.ryear in(select distinct
ryear
from #review
);--something like that
Expected Result:
+-------+------+-------+----------------+
| empid | name | ryear | status |
+-------+------+-------+----------------+
| 1 | jack | 2016 | S2 |
| 1 | jack | 2017 | not initiated |
| 2 | mack | 2016 | S2 |
| 2 | mack | 2017 | S1 |
| 3 | rack | 2016 | not initieated |
| 3 | rack | 2017 | S2 |
| 4 | tack | 2016 | Not Initiated |
| 4 | tack | 2017 | Not Initiated |
+-------+------+-------+----------------+