Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 school work(regarding CASE)

Author  Topic 

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 02:45:37
i had two table salary & salgrade
Salary table is like

Empid   FirstName     Lastname  Title           Salary
1	Vamsi	      Kashyap	DbDeveloper	15000	
2	Gattu	      Kumar	Programmer	20000	
3	Ratna	      Kumar	Programmer	20000	
4	Hema	      Kumar	MD	        115000	
5	CH	      Satish	Developer	25000	
6	Ravi	      Kanth	DBA	        35000	
7	Mohammed      Naeem	Admin	        45000	
8	Sriram	      Rahi	HR	        80000		
9	Pardha	      Saradhi	Accountent	45000	
10	Bhushan	      Ivutry	ProjectManger	75000

Second Table Salgrade is 

Grade   LowSal  HighSal
A	50001	150000
B	20001	50000
C	10001	20000
D	5001	10000
E	1001	5000

i want to write a case which will give a o/p like
[Code]
EmpId FullName Title Salary Grade
5 CH Satish Developer 25000 B
[/code]
Grade must be given regarding the Sal plz consider it is so simple but i am little bit confused plz help me
With Regards
Kashyap M

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 03:12:57
Hi

This is your query:


 SELECT Empid, FirstName + ' ' + Lastname AS [FullName], Title, Salary,
        ( SELECT Grade FROM SalGrade WHERE S.Salary > LowSal AND  S.Salary <= HighSal) AS EmpGrade
 FROM Salary S
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 03:17:38
thanks a ton this is very nice query if you don't mind can you modify the query by using case condition plz plz

With Regards
Kashyap M
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 03:24:47
quote:
Originally posted by kashyap_sql

thanks a ton this is very nice query if you don't mind can you modify the query by using case condition plz plz

With Regards
Kashyap M



SELECT Empid,FirstName + ' '+Lastname AS [FullName],Title,Salary,
CASE WHEN Salary > 1001 AND Salary <=5000
THEN 'E'
WHEN Salary > 5001 AND Salary <=10000
THEN 'D'
WHEN Salary > 10001 AND Salary <=20000
THEN 'C'
WHEN Salary > 20001 AND Salary <=50000
THEN 'B'
WHEN Salary > 50001 AND Salary <=150000
THEN 'A'
ELSE 'Ungraded'
END AS EmpGrade
FROM Salary S
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 03:25:00
quote:
Originally posted by kashyap_sql

thanks a ton this is very nice query if you don't mind can you modify the query by using case condition plz plz

With Regards
Kashyap M


May I know the reason behind the rigidity of using CASE in the query? As you can see above and compare, you need to hardcode the vaues in the CASE statements whereas, in the first query the salgrade table is used.
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 03:25:12
quote:
Originally posted by kashyap_sql

thanks a ton this is very nice query if you don't mind can you modify the query by using case condition plz plz

With Regards
Kashyap M



SELECT Empid,FirstName + ' '+Lastname AS [FullName],Title,Salary,
CASE WHEN Salary > 1001 AND Salary <=5000
THEN 'E'
WHEN Salary > 5001 AND Salary <=10000
THEN 'D'
WHEN Salary > 10001 AND Salary <=20000
THEN 'C'
WHEN Salary > 20001 AND Salary <=50000
THEN 'B'
WHEN Salary > 50001 AND Salary <=150000
THEN 'A'
ELSE 'Ungraded'
END AS EmpGrade
FROM Salary S
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 03:27:20
that is very nice query thanks again for your consideration Cheers to ROHIT a lot of Thanks

With Regards
Kashyap M
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 03:29:09
May I know the reason behind the rigidity of using CASE in the query? As you can see above and compare, you need to hardcode the vaues in the CASE statements whereas, in the first query the salgrade table is used.
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 03:30:47
i am just learning about case i understood a little bit of case in one table but unable to do it in two tables that's the reason for asking this query

With Regards
Kashyap M
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 03:41:56
quote:
Originally posted by kashyap_sql

i am just learning about case i understood a little bit of case in one table but unable to do it in two tables that's the reason for asking this query

With Regards
Kashyap M


Until and unless the two tables are related in any way, you need to hardcode the values.
See, what i mean to say is, in future if new records are being added to the Salgrade table, you need to put those new values in the CASE, which is not a very good idea
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 03:46:06
you are right the first query given by you is very nice and i can gather more information according to that but this is just a example to write a query on case for two tables...
case is a condition used for a relation right

With Regards
Kashyap M
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 03:52:09
quote:
Originally posted by kashyap_sql

you are right the first query given by you is very nice and i can gather more information according to that but this is just a example to write a query on case for two tables...
case is a condition used for a relation right

With Regards
Kashyap M


I am sure you want to learn how to use CASE using two tables. But, in this case the second table is not being used at all. we are just harcoding the values present in the second table and surely not using the second table in any way.

Let me think of some better example for you where you have to use two tables & CASE. keep watching this thread.

Thanks,

Rohit
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 03:55:24
thanks again for your consideration i will follow you

With Regards
Kashyap M
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 04:06:34
I want to the output to be something like this(based on your two tables above)

Empid FullName Title Salary EmpGrade AppraisalPercent
1 Vamsi Kashyap DbDeveloper 15000 C 25
2 Gattu Kumar Programmer 20000 C 20
3 Ratna Kumar Programmer 20000 C 20
4 Hema Kumar MD 115000 A 0
5 CH Satish Developer 25000 B 20
6 Ravi Kanth DBA 35000 B 5
7 Mohammed Naeem Admin 45000 B 10
8 Sriram Rahi HR 80000 A 10
9 Pardha Saradhi Accountent 45000 B 10



Conditions are:

1) For Programmers the AppraisalPercent should be 20 If they are in Grade C & 10 if they are in Grade B.
2) For Developers Opposite of above(10 If they are in Grade C & 20 If they are in Grade B).
3) For DbDeveloper It should be 30 if they are in Grade E & F and 25 if they are in C,D and 15 if they are in A & B.
3) For MD it should be 0 irrespective of the Grade.
4) For Admin,HR and Accountant it should be 10 irrespective of their Grade.
5) If there are any other Job Titles not included in above conditions the percent should be 5.

Add some more data in your table and write the query above using case statements

Let me know if you need more clarification


Thanks
Rohit
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 04:10:41
ok i will get you back by adding some columns to the table

With Regards
Kashyap M
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 04:14:41
quote:
Originally posted by kashyap_sql

ok i will get you back by adding some columns to the table

With Regards
Kashyap M


you don't need to add columns to the table you need to add rows(to get some more Job Titles in order to validate the query for its correct o/p) and get that AppraisalPercent column as o/p using CASE.
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 05:33:24
sorry for the delay in reply i gone for lunch now i will work on it

With Regards
Kashyap M
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 05:48:13
quote:
Originally posted by kashyap_sql

sorry for the delay in reply i gone for lunch now i will work on it

With Regards
Kashyap M


No Worries
Take your time, don't rush into things. Important thing is to get understanding and not speed. Anyways, you will be quick once you have the knowledge.
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 05:52:47
you are great man it seems to be so easy but on working it is giving me a problem hard but definitely i will work on it to get the o/p

With Regards
Kashyap M
Go to Top of Page

kashyap_sql
Posting Yak Master

174 Posts
Posted - 2010-09-24 : 06:26:10
i think i had achieved the o/p 

select Empid, LastName+ ' ' +FirstName as FullName, Title, Salary,
case when Salary > 1001 and Salary <=5000 then 'E'
when salary > 5001 and Salary <=10000 then 'D'
when Salary > 10001 and Salary <=20000 then 'C'
when Salary > 20001 and Salary <=50000 then 'B'
when Salary > 50001 and Salary <=150000 then 'A'
else 'UnGrade'
end as EmpGrade,
case when Salary='20000' and title='programmer' then '20%' 
when Salary='115000' and title='MD' then 'O' 
when Salary='25000' and title='developer' then '20%' 
when Salary='15000' and title='Dbdeveloper' then '25%'
when Salary='45000' and title='Accountent' then '10' 
when Salary='80000' and title='HR' then '10'
when Salary='45000' and title='Admin' then '10'
when Salary='35000' and title='DBA' then '10'
else 'NoHike'
end as 'AppraisalPercent'
from salary



With Regards
Kashyap M
Go to Top of Page

rohitvishwakarma
Posting Yak Master

232 Posts
Posted - 2010-09-24 : 06:32:18
INSERT the following records into your table and check the O/P:

INSERT INTO salary
SELECT 10,'Rohit','Vishwakarma','Programmer',23500
UNION
SELECT 11,'Rahul','Singh','Sales Manager',36850
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site