Last post Jan 07, 2019 08:38 PM by simflex
Jan 07, 2019 12:40 AM|simflex|LINK
I am trying to perform pivot on registration_date which has a data type of datetime.
Here is my query:
SELECT t.TransactionID, t.Amount, t.AmountPaid, t.AmountOwed, t.EventYear, t.Reg_Fee, p.Description,
m.FirstName + ' ' + m.LastName AS Members, m.EmailAddress, m.Phone, m.Address, m.gender,
m.registration_date As [Year], m.enelope_number
FROM dbo.Transactions AS t INNER JOIN dbo.Members AS m ON t.MemberID = m.MemberID
INNER JOIN dbo.PaymentTypes AS p ON t.TypeID = p.PaymentTypeID
FOR [Year] IN (Year)
) AS PVT
Registration_date is in a range of 2016 to 2019 but the user has the option of adding 2020 or more.
So far, I am getting the following error message:
Msg 8114, Level 16, State 1, Line 18
Error converting data type nvarchar to datetime.
Msg 473, Level 16, State 1, Line 18
The incorrect value "Year" is supplied in the PIVOT operator.
Any ides how to resolve this?
Thanks in advance
Jan 07, 2019 08:33 PM|limno|LINK
FOR [Year] IN (2016,2017,2018,2019,2020,2021)
) AS PVT
Jan 07, 2019 08:38 PM|simflex|LINK
I came here to delete the question but unfortunately, there is no delete feature on this forum.
I recognized there is no way of doing what I am trying to accomplish which is rather than manually add Years, it would have been nice to add the years on the back end and have it display the year number.
However, I ended up doing it just the way you did it.