July 17, 2020 at 3:46 am
I would like to bring the rows to column, create a pivot base on 12 months period. For example I would like to start the month from October instead of January. So it will have 12 months column from October till September with a sum of quarter 1-4. I create a sample below I hit error on the quarter 'Q'. I have try to convert to varchar but still not working. Anything wrong with my code?
Error: Conversion failed when converting the varchar value 'Q3' to data type int.
select a, left(a,2) test
from(
select '10-1999' a
union
select '01-1999' a
union
select '02-1999' a
union
select 'Q3-1999'
a) a
order by choose(cast(left(a,2) as varchar),'4','5','6','Q2','7','8','9','Q3','10','11','12','Q4','1','2','3','Q1')
July 17, 2020 at 4:32 am
One way is to use a Calendar table, and to include Fiscal Month and Fiscal Quarter, and join to that. And then do the pivot
July 17, 2020 at 9:20 am
Regarding your error: Check the choose function in Books online ! It expects an INT for index value !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply