1

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')
)
AS 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!

  • Are you using a case sensitive collation? If so, then "processeddate" is not equal to "ProcessedDate". – RLF Dec 23 '14 at 19:13
  • Please let us know which "sql server" you are using. – RLF Dec 23 '14 at 19:21
  • Even with proper capitalization, the error message persists. I'm using SQL Server 2012. Thank you RLF! – Corey Dec 23 '14 at 19:37
  • See Mark Sinkinson's answer. – RLF Dec 23 '14 at 22:02
3

So at this point in the code:

isnull((processeddate),' ') as 'Null'

The column processeddate is not visible to SQL Server, because you are selecting available fields from the result of the following query:

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

...along with the pivoted columns [1] through [12].

You either need to include ProcessedDate in the above query, or exclude it from the outer SELECT.

  • Hi Mark. Thanks for the input. I attempted your method and it definitely fixed the error and produced a NULL column. However, the column has dates instead of dollar amounts (EmpDeductAmt). – Corey Dec 24 '14 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.