SQL query help

  • 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.

  • You can use this one

    substring(col_processing, PatIndex('%[0-9]%', col_processing), len(col_processing))+1

  • I need query to get value of col_abc3 for year 2019 and month 2 based on value of col_processed which is abc2.

  • @ sqlenthu 89358 - Solution suggested by @sumathi shall work. You can use it along with CTE and Ranking function to accomplish your desired goal.

  • 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...

  • sqlenthu 89358 wrote:

    I need to do it in single query and no dynamic sql.

    Arbitrary requirements such as those make me suspect that this is a homework question.  Please show us what you've tried, and we'll try to point out where you're going wrong.

    John

  • 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