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

Question

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

dhananjay.nagarkar

Default port

Points: 1496
More actions
April 13, 2013 at 3:55 pm

#273027

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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

dhananjay.nagarkar Default port Points: 1496 More actions · Answer 2

No worries, I was able to resolve this 🙂

Kind Regards

Dhananjah

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

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

dhananjay.nagarkar Default port Points: 1496 More actions · Answer 4

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

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 5

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

sqldriver SSChampion Points: 10440 More actions · Answer 6

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 +'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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.

sqldriver SSChampion Points: 10440 More actions · Answer 8

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!

dhananjay.nagarkar Default port Points: 1496 More actions · Answer 9

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

Pothiraj R SSC-Addicted Points: 486 More actions · Answer 10

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 +'

dhananjay.nagarkar Default port Points: 1496 More actions · Answer 11

dhananjay.nagarkar

Default port

Points: 1496

April 16, 2013 at 4:54 am

#1606765

The code is working good for me 🙂

thanks

Dhananjay