Need help with select case statement

Transact-SQL
1

Here's my query and it's working, but I need to get the value in NextYear column up to the same row as CurrentYear Column with the Depend LastName and Dependent FirstName. I have tries and don't know how to get it right. Please see the out below (one from query and last output that I want in the result.). Thank you very much in advance.

SELECT EMPLOYEE.EMPLOYEE,
EMPLOYEE.EMP_STATUS,
EMPLOYEE.LAST_NAME,
EMPLOYEE.FIRST_NAME,
EMDEPEND.LAST_NAME AS DEP_LNAME,
EMDEPEND.FIRST_NAME AS DEP_FNAME,
(CASE WHEN YEAR(START_DATE)=YEAR(GETDATE()) THEN HRDEPBEN.PLAN_CODE ELSE '' END) AS CurrentYEAR,
(CASE WHEN YEAR(START_DATE)=YEAR(GETDATE())+1 THEN HRDEPBEN.PLAN_CODE ELSE '' END) AS NextYEAR
FROM EMDEPEND INNER JOIN
HRDEPBEN ON EMDEPEND.EMPLOYEE = HRDEPBEN.EMPLOYEE AND EMDEPEND.SEQ_NBR = HRDEPBEN.DEPENDENT INNER JOIN
EMPLOYEE ON EMDEPEND.EMPLOYEE = EMPLOYEE.EMPLOYEE
WHERE (EMDEPEND.REL_CODE = 'WARD')
AND (EMDEPEND.ACTIVE_FLAG = 'A')
AND YEAR(HRDEPBEN.START_DATE) >= YEAR(GETDATE())
ORDER BY EMPLOYEE.EMPLOYEE,
HRDEPBEN.START_DATE,
EMDEPEND.SEQ_NBR,
HRDEPBEN.PLAN_CODE
Here's is the out put I got from this query
Emp Emp_Status LastName FirstName DepLname DepFname CurrentYEAR NextYEAR
1219 A1 Mullins James Mullins Caitlin MED1
1219 A1 Mullins James Mullins Caitlin VS01
1550 A1 Granado Sharon Stephenson Cameron DN01
1550 A1 Granado Sharon Stephenson Cameron MED4
1550 A1 Granado Sharon Stephenson Cameron VS01
1550 A1 Granado Sharon Stephenson Jason DN01
1550 A1 Granado Sharon Stephenson Jason MED4
1550 A1 Granado Sharon Stephenson Jason VS01
1550 A1 Granado Sharon Stephenson Cameron DN01
1550 A1 Granado Sharon Stephenson Cameron MED4
1550 A1 Granado Sharon Stephenson Cameron VS02
1550 A1 Granado Sharon Stephenson Jason DN01
1550 A1 Granado Sharon Stephenson Jason MED4
1550 A1 Granado Sharon Stephenson Jason VS02
1560 A1 Rogers Jessie Rogers Jessica DN03
1560 A1 Rogers Jessie Rogers Jessica MED4
1560 A1 Rogers Jessie Rogers Jessica VS01
1560 A1 Rogers Jessie Rogers Jessica DN03
1560 A1 Rogers Jessie Rogers Jessica MED4
1560 A1 Rogers Jessie Rogers Jessica VS01

Here's the Output I want
Emp Emp_Status LastName FirstName DepLname DepFname CurrentYEAR NextYEAR
1219 A1 Mullins James Mullins Caitlin MED1
1219 A1 Mullins James Mullins Caitlin VS01
1550 A1 Granado Sharon Stephenson Cameron DN01 DN01
1550 A1 Granado Sharon Stephenson Cameron MED4 MED4
1550 A1 Granado Sharon Stephenson Cameron VS01 VS02
1550 A1 Granado Sharon Stephenson Jason DN01 DN01
1550 A1 Granado Sharon Stephenson Jason MED4 MED4
1550 A1 Granado Sharon Stephenson Jason VS01 VS02
1550 A1 Granado Sharon Stephenson Cameron
1550 A1 Granado Sharon Stephenson Cameron
1550 A1 Granado Sharon Stephenson Cameron
1550 A1 Granado Sharon Stephenson Jason
1550 A1 Granado Sharon Stephenson Jason
1550 A1 Granado Sharon Stephenson Jason
1560 A1 Rogers Jessie Rogers Jessica DN03 DN03
1560 A1 Rogers Jessie Rogers Jessica MED4 MED4
1560 A1 Rogers Jessie Rogers Jessica VS01 VS01
1560 A1 Rogers Jessie Rogers Jessica
1560 A1 Rogers Jessie Rogers Jessica
1560 A1 Rogers Jessie Rogers Jessica

2

You need to provide DDL as well as sample data and expected results. We don't have your table structure or data, so it's hard to piece together what you have/need

3

I have 3 tables Employee with fields (EmployeeID, Empoyee_Status, LastName, FirstName)
Emplolyee Dependent (EMDEPEND) table with fields (EmpoyeeID, SQL_NBR, Relate_Code, LastName, FirstName)
HRDEPBEN table with fields (emplolyeeID, Dependent, Plan_Code, Start_Date

for SQL_NBR is number dependent like 1, 2, 3
Dependent is the same thing.

4

you're asking for help but not providing any relevant information. i.e.

Create table Employee (EmployeeID int, ).....

insert into Employee (EmpoyeeID,...) values(1......)

expected results.......

5

Hey Mike,

is there anyway that I can do in query not create temp table? What I need is the just bring the record for NextYear column up to the same as CurrentYear column with the same person? I don't know why to get it work? I don't know how to do pivot either. Please help.. thank very in advance.

6

yes you don't need a temp table. But for us to help, we use temp tables so we can get an idea of table layouts, data and expected results. You would then take what we provide and apply it to your environment, tables, etc... We don't need alot of data, maybe 10 rows, but we need representation of what you are talking about.