CODEWITHSUNDEEP
sqlreporting-services
Chan SH
Chan SH

Reputation: 11

How to select the same columns with different input

The case is that I want to make a report using SSRS to compare the staff's Salary between 2 year. The years, staff number and salary is in 1 table.

The table structure is is : his_pay_period_num, his_employee_num, Salary for example:

[Salary]:

2007,A1234,$30000
2009,A1234,$50000
2009,B1234,$15000

I want to output:

A1234 , $30000, $50000   <-----how can I select 2 Salary with different input(2007,2009)?
B1234,  $0, $15000  <----------- And if no Record,return 0

Currently, I am doing:

SELECT  t1.his_pay_period_num, t1.his_employee_num ,t1.Salary AS rate1,
  t2.Salary AS rate2
FROM          [Salary] as t1 Left outer join
            [Salary] as t2 on t1.id = t2.id
WHERE        (t1.his_pay_period_num = '2007') or  (t2.his_pay_period_num= '2009')

It outputs:

A1234, $30000,$30000
A1234, $50000,$50000   <-----just display 2 record 
B1234, $15000,$15000

Problem 1 : How can I select 2 salaries with different input?

Problem 2 : And if no record exists, return 0

Upvotes: 1

Views: 83

Answers (1)

shree.pat18
shree.pat18

Reputation: 21757

Assuming that your DBMS supports the PIVOT keyword, you can do something like this:

select 
his_employee_num, 
coalesce([2007],0) as [Pay in 2007], --0 if first argument is NULL
coalesce([2009],0) as [Pay in 2009]
from
(select * from salary) s
pivot
(max(salary) for his_pay_period in ([2007],[2009])) p

This will work on SQL Server and Oracle (possibly with a little tweaking needed).

Upvotes: 1

Related Questions