mardi 23 décembre 2014

Pivoting with NULL Dates


My apologies ahead of time for the poor readability of my code.


I'm attempting the pivot below:



select



EmpID as ID, [1] as January, [2] as February, [3] as March, [4] as April, [5] as May, [6] as June, [7] as July, [8] as August, [9] as September, [10] as October, [11] as November, [12] as December, isnull((processeddate),' ') as 'Null'



from (select EmpID, EmpDeductAmt, month(ProcessedDate) as TMonth from [dbo].[Invoices] where Inv_Number not in ('1035','1039') )



source pivot ( sum(EmpDeductAmt) for TMonth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) )



as pvtmonth



order by EmpID;








However, I receive an error message of "Invalid column name 'processeddate' upon hitting F5.


I'm fairly green, but the code worked beautifully when the isnull((processed date),' ') as 'Null' code was left off.


The desired output would consist of columns pertaining to each month in addition to a column for NULL months, as some of the records in my dataset have NULL as their date. Failure to account for the NULL dates shortchanges the dollar amount arrived out. The rows are arranged by employee identification number.


Is there a suggested remedy for this issue?


Many, many thanks!





Aucun commentaire:

Enregistrer un commentaire