September 13, 2010 at 3:54 am
Hi experts,
Can anyone solve this query, I have tried but based on monthwise report not executing what is the issue in this query.
SELECT cnt.ContractID,[1] as jan,[2] as feb,[3] as mar,[4] as apr,[5] as may,
[6] as jun,[7]as jul,[8] as aug,[9]as sep,[10] as oct,[11]as nov,[12]as decs
from (Select ContractID, srvctype,Startdate from OCTR ) as source
PIVOT(count(ContractID)FOR month(convert(varchar,Startdate,103)) IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))AS cnt
order by cnt.ContractID
September 13, 2010 at 12:42 pm
What error message do you get?
Why do you convert a date format to varchar followed by an implicit conversion back to datetime due to the month() function? Wouldn't month(Startdate)) return the value needed?
Other than that I don't see an error in your query.
September 13, 2010 at 1:47 pm
I think there are several things wrong here. Firstly you won't be able to SELECT or ORDER BY ContractID in your final SELECT statement because ContractID is consumed in the PIVOT statement. Secondly you will need to do your MONTH conversion inside your source query and not in the PIVOT clause
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply