Urgent Query help needed- calculate one field based on other field in SELECT statment

  • Hi Friends,

    I have reporting TSQL query- in this query within the select statement I have 2 fields 1) Age (working Days) and 2) Age Group(Working days).

    the requirement demands that based on the first Select field - Age(working Days) I, display one of these selections:

    0 - 29

    30 - 59

    60 - 89

    90 - 119

    120 +

    for the Second SELECT list field Age Group(Working Days)

    Note- the data Type for both is Varchar.

    thanks

    Dhananjay

  • dhananjay.nagarkar (4/13/2013)


    Hi Friends,

    I have reporting TSQL query- in this query within the select statement I have 2 fields 1) Age (working Days) and 2) Age Group(Working days).

    the requirement demands that based on the first Select field - Age(working Days) I, display one of these selections:

    0 - 29

    30 - 59

    60 - 89

    90 - 119

    120 +

    for the Second SELECT list field Age Group(Working Days)

    Note- the data Type for both is Varchar.

    thanks

    Dhananjay

    Sorry, but there really isn't enough enough information here to help you. Please take the time to read the first article I reference below in my signature block reading asking for help. It will walk you through everything you need to post and how to post it to get the best possible tested answers.

    Also, these sounds like homework as I seem to remember seeing a very similar post just a few days ago. If true, we would be more the happy to help you, but besides the other info we need we will need you to show us what you have tried so far.

  • No worries, I was able to resolve this 🙂

    Kind Regards

    Dhananjah

  • for the benefit of others who may in the future read your post...would you care to share your solution.

    many thanks

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Sure I would love to share-

    Here's what I did-

    created 3 temp tables with the filelds I need for each with same order, data type.

    then UNION the 3 selects.

    then in the UNION i calculated the field as below-

    SELECT A.*,

    CASE

    WHEN [Age (Working Days)] IS NULL

    THEN NULL -- DO NOT DISLAY THIS FIELD IF AGE WORKING DAYS IS NULL

    ELSE -- DISPLAY AGE GROUP WHEN AGE IS PRESENT

    CASE

    WHEN CAST([Age (Working Days)] AS INT) < 30

    THEN '1 - 29'

    WHEN CAST([Age (Working Days)] AS INT) < 60

    THEN '30 - 59'

    WHEN CAST([Age (Working Days)] AS INT) < 90

    THEN '60 - 89'

    WHEN CAST([Age (Working Days)] AS INT) < 120

    THEN '90 - 119'

    ELSE'120 +'

    END

    END AS [Age Group (Working Days)]

    FROM (

    SELECT * FROM #tmp_JP

    UNION

    SELECT * FROM #tmp_WO

    UNION

    SELECT * FROM #tmp_WOR

    )A

    Thanks

    Dhananjay

  • The statement is ok, but using identifiers (column names) with spaces and braces usually brings a lot of troubles.

  • dhananjay.nagarkar (4/14/2013)


    Sure I would love to share-

    Here's what I did-

    created 3 temp tables with the filelds I need for each with same order, data type.

    then UNION the 3 selects.

    then in the UNION i calculated the field as below-

    SELECT A.*,

    CASE

    WHEN [Age (Working Days)] IS NULL

    THEN NULL -- DO NOT DISLAY THIS FIELD IF AGE WORKING DAYS IS NULL

    ELSE -- DISPLAY AGE GROUP WHEN AGE IS PRESENT

    CASE

    WHEN CAST([Age (Working Days)] AS INT) < 30

    THEN '1 - 29'

    WHEN CAST([Age (Working Days)] AS INT) < 60

    THEN '30 - 59'

    WHEN CAST([Age (Working Days)] AS INT) < 90

    THEN '60 - 89'

    WHEN CAST([Age (Working Days)] AS INT) < 120

    THEN '90 - 119'

    ELSE'120 +'

    END

    END AS [Age Group (Working Days)]

    FROM (

    SELECT * FROM #tmp_JP

    UNION

    SELECT * FROM #tmp_WO

    UNION

    SELECT * FROM #tmp_WOR

    )A

    Thanks

    Dhananjay

    Doesn't everything get cast as either <120 or Over 120, or NULL? You didn't set any lower limits on your higher numbers?

    WHEN CAST([Age (Working Days)] AS INT) < 30

    THEN '1 - 29'

    WHEN CAST([Age (Working Days)] AS INT) between 30 and 59

    THEN '30 - 59'

    WHEN CAST([Age (Working Days)] AS INT) between 60 and 89

    THEN '60 - 89'

    WHEN CAST([Age (Working Days)] AS INT) between 90 and 119

    THEN '90 - 119'

    ELSE '120 +'

  • erikd (4/15/2013)


    dhananjay.nagarkar (4/14/2013)


    Sure I would love to share-

    Here's what I did-

    created 3 temp tables with the filelds I need for each with same order, data type.

    then UNION the 3 selects.

    then in the UNION i calculated the field as below-

    SELECT A.*,

    CASE

    WHEN [Age (Working Days)] IS NULL

    THEN NULL -- DO NOT DISLAY THIS FIELD IF AGE WORKING DAYS IS NULL

    ELSE -- DISPLAY AGE GROUP WHEN AGE IS PRESENT

    CASE

    WHEN CAST([Age (Working Days)] AS INT) < 30

    THEN '1 - 29'

    WHEN CAST([Age (Working Days)] AS INT) < 60

    THEN '30 - 59'

    WHEN CAST([Age (Working Days)] AS INT) < 90

    THEN '60 - 89'

    WHEN CAST([Age (Working Days)] AS INT) < 120

    THEN '90 - 119'

    ELSE'120 +'

    END

    END AS [Age Group (Working Days)]

    FROM (

    SELECT * FROM #tmp_JP

    UNION

    SELECT * FROM #tmp_WO

    UNION

    SELECT * FROM #tmp_WOR

    )A

    Thanks

    Dhananjay

    Doesn't everything get cast as either <120 or Over 120, or NULL? You didn't set any lower limits on your higher numbers?

    WHEN CAST([Age (Working Days)] AS INT) < 30

    THEN '1 - 29'

    WHEN CAST([Age (Working Days)] AS INT) between 30 and 59

    THEN '30 - 59'

    WHEN CAST([Age (Working Days)] AS INT) between 60 and 89

    THEN '60 - 89'

    WHEN CAST([Age (Working Days)] AS INT) between 90 and 119

    THEN '90 - 119'

    ELSE '120 +'

    Actually, no. If working days is less than 30 then the first WHEN is satisfied. If working days is less than 60, then the second WHEN is satisfied. If you flipped the CASE and started with the largest value first, then yes you would need the lower bounds defined as well. The CASE stops evaluating when it hits the first successful condition.

  • That's really cool. Never would have guessed that would work. I had a fairly similar query based on employee size and SIC codes, that I just changed up a bit and it works correctly. I had been using between previously.

    Thanks!

  • I'm new to TSQL , but per my requirement I used this logic- feel free to suggest me if anything better .

    I'm open to suggestions 🙂

    thanks

    Dhananjay

  • Is type casting required?. I think it is not required. Even though it is a varchar type, SQL Engine will convert it internally in this scenario. Please correct me, if I wrong.

    The below code will work:

    CASE

    WHEN [Age (Working Days)] < 30

    THEN '1 - 29'

    WHEN [Age (Working Days)] < 60

    THEN '30 - 59'

    WHEN [Age (Working Days)] < 90

    THEN '60 - 89'

    WHEN [Age (Working Days)] < 120

    THEN '90 - 119'

    ELSE'120 +'

  • The code is working good for me 🙂

    thanks

    Dhananjay

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply