SQL Connections: Select the status of employee-submitted feedback and a list of employees who did not submit a review for each year
During each year, for each employee, I want to indicate the status of the review that was submitted by the employee, or "not initiated" in case the employee has not submitted a review for that year.
It's hard to put this question into words, so I would try to explain it by giving an 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')
Bad request
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 |
+-------+------+-------+----------------+
+3
source to share
4 answers
You can use cross join in your sub request
select a.empid
,a.name
,c.ryear
,isnull(b.status,'Not Initiated') as status
from #employees as a
cross join(select distinct
ryear
from #review
) as c
left join #review as b
on b.ryear = c.ryear
and a.empid = b.empid
order by a.empid, ryear
+3
source to share
This is a generic table expression; He also maintains multiple reviews in the same year for an employee.
WITH X AS
(SELECT distinct ryear FROM #review)
SELECT a.empid, a.name, X.ryear, isnull(b.status,'Not initiated')
FROM X as x
LEFT
JOIN #employees as a
ON 1=1
LEFT
JOIN #review as b
ON a.empid = b.empid
AND b.ryear = x.ryear
ORDER
BY a.empid,x.ryear
+1
source to share
This allegedly confirms more than one review by the same employee in the same year:
SELECT Employees.empid, Employees.[name], ReviewYears.[Value]
, [Status] = ISNULL(LatestReviews.[status], 'Not Initiated')
FROM #employees AS Employees
CROSS JOIN (SELECT DISTINCT ryear AS [Value] FROM #review) AS ReviewYears -- We need some source of years, hopefully there are no missing years here.
LEFT JOIN (
SELECT *
FROM (
SELECT empid, ryear, [status]
, RN = ROW_NUMBER() OVER(PARTITION BY empid, ryear ORDER BY STATUS DESC) -- Per employee and year, we'll take only one status, hopefully we can order by statuses.
FROM #review
) AS T
WHERE RN = 1 -- Refer to comments at creation of RN.
) AS LatestReviews ON LatestReviews.empid = Employees.empid AND LatestReviews.ryear = ReviewYears.[Value] -- Refer to comments at creation of RN.
ORDER BY Employees.empid ASC, ReviewYears.[Value] ASC
+1
source to share
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')
declare @t table(inputyear int)
insert into @t values(2016),(2017)
;with CTE as
(
select e.*,ca.inputyear from #employees e
cross apply(select * from @t)ca
)
select c.empid,c.name,c.inputyear
,case when r.status is null then 'Not Initiated'
else [status] end [status] from cte c
left join #review r on c.empid=r.empid
and c.inputyear=r.ryear
drop table #employees
drop table #review
-1
source to share