I'm sure this won't be a difficult question for the hardcore SQL geeks, but I need some help. This is for SQL Server 2000 (inherited projects!).
I have a table of salary listings that look like this:
EmployeeID | EffectiveDate | Salary
1 | 2/1/2011 | 500
1 | 6/1/2011 | 600
1 | 12/1/2011 | 650
I need to create a query that will output these salaries by month for a given year. So the output would be like
EmployeeID | Jan | Feb | Mar | Apr | Apr | May | Jun | Jul | Aug | Sept | Oct | Nov | Dec
1 | 500 | 500 | 500 | 500 | 500 | 500 | 600 | 600 | 600 | 600 | 600 | 600 | 650
I know there must be a way to do this effectively with SQL, I just can't seem to get it right. Obviously, I would be naming the month columns above with SQL such as SELECT EmployeeID, 'Jan' AS Jan, 'Feb' AS Feb, etc but the rest of the statement is harder since I'm looking for ranges.
Since you are using SQL Server 2000 it does not have a PIVOT function, so you will have to replicate this using an aggregate function and a CASE statement. Similar to this:
select employeeid,
sum(case when DatePart(Month, EffectiveDate) = 1 then Salary end) as Jan,
sum(case when DatePart(Month, EffectiveDate) = 2 then Salary end) as Feb,
sum(case when DatePart(Month, EffectiveDate) = 3 then Salary end) as Mar,
sum(case when DatePart(Month, EffectiveDate) = 4 then Salary end) as Apr,
sum(case when DatePart(Month, EffectiveDate) = 5 then Salary end) as May,
sum(case when DatePart(Month, EffectiveDate) = 6 then Salary end) as Jun,
sum(case when DatePart(Month, EffectiveDate) = 7 then Salary end) as Jul,
sum(case when DatePart(Month, EffectiveDate) = 8 then Salary end) as Aug,
sum(case when DatePart(Month, EffectiveDate) = 9 then Salary end) as Sep,
sum(case when DatePart(Month, EffectiveDate) = 10 then Salary end) as Oct,
sum(case when DatePart(Month, EffectiveDate) = 11 then Salary end) as Nov,
sum(case when DatePart(Month, EffectiveDate) = 12 then Salary end) as Dec
from yourtable
group by employeeid
Edit, based on your comments above carrying over the value from one month to the next, here is a solution that might work for you.
declare @query as nvarchar(max) = '',
@rowcount as int = 1,
@pivotrow as int,
@currentMonthSalary as int = 0,
@priorMonthSalary as int = 0,
@employeeid int
select distinct effectivedate
into #colspivot
from yourtable
while @rowcount <= 12 -- loop thru each month
begin
set @pivotrow = (select top 1 datepart(month, effectivedate)
from #colspivot
order by datepart(month, effectivedate))
select @currentMonthSalary = salary, @employeeid = EmployeeID
from yourtable
where datepart(month, effectivedate) = @pivotrow
if @pivotrow = @rowcount
begin
insert into FinalData (employeeid, effectivemonth, salary)
select @employeeid, cast(DateName(month, DateAdd(month, @pivotrow, 0) -1) as varchar(3)), @currentMonthSalary
set @query = @query + ', sum(case when effectivemonth = ''' + cast(DateName(month, DateAdd(month, @pivotrow, 0) -1) as varchar(3)) + '''
then ' + cast(@currentMonthSalary as varchar(10)) + ' end) as '+ cast(DateName(month, DateAdd(month, @pivotrow, 0) -1) as varchar(3))
delete from #colsPivot where datepart(month, effectivedate) = @pivotRow
set @priorMonthSalary = @currentMonthSalary
end
else
begin
insert into FinalData (employeeid, effectivemonth, salary)
select @employeeid, cast(DateName(month, DateAdd(month, @rowcount, 0) -1) as varchar(3)), @priorMonthSalary
set @query = @query + ', sum(case when effectivemonth = ''' + cast(DateName(month, DateAdd(month, @rowcount, 0) -1) as varchar(3)) + '''
then ' + cast(@priorMonthSalary as varchar(10)) + ' end) as '+cast(DateName(month, DateAdd(month, @rowcount, 0) -1) as varchar(3))
end
if @rowcount <= 12
set @rowcount = @rowcount + 1
end
set @query = 'select employeeid '+ @query
+ ' from FinalData group by employeeid;'
exec(@query)
See SQL Fiddle with Demo. I created a new table FinalData to store the data for each month while I loop through creating the sql statement.