October 18, 2019 at 3:33 am
I have a table like this:
Year month col_abc1 col_abc2 col_abc3 col_abc4 col_processing
2019 1 a1 a2 a3 a4 abc2
2019 2 a8 a6 a2 a9 abc2
2019 3 a5 a6 a3 a8 abc3
...and so on
I have a requirement that for a year and month, i need to check value of col_processing like for year 2019 and month 2, its abc2. Now whatever value is there, its numeric should be added by 1, like in our example, abc2 becomes abc3 and check the value in column col_abc3. That should be the output. I need to do it in single query and no dynamic sql. Can anyone please help me.
October 18, 2019 at 4:40 am
You can use this one
substring(col_processing, PatIndex('%[0-9]%', col_processing), len(col_processing))+1
October 18, 2019 at 5:04 am
I need query to get value of col_abc3 for year 2019 and month 2 based on value of col_processed which is abc2.
October 18, 2019 at 5:29 am
@ sqlenthu 89358 - Solution suggested by @sumathi shall work. You can use it along with CTE and Ranking function to accomplish your desired goal.
October 18, 2019 at 7:16 am
Mr or Mrs 500, i was able to get that information using substring. Its the other part where i have to make up the value and join that value with column name. If u can help me with that, i will be grateful. It doesn't seem to work with pivot, i guess...
October 18, 2019 at 4:46 pm
This is just BAD table design. You'd be better off totally redesigning this process.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply