Stored Procedure Problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure Problem

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-30 : 08:44:16

Hi ,
I have three tables - employee skill, skill , rating as shown below

Employee_skill

Skill_NBR Employee_nbr supervisor rating Date
1 1 3 01/01/2007
2 1 4 08/01/2007
3 1 2 02/10/2007
12 2 4 02/10/2007
12 2 3 02/10/2007
5 8 4 01/08/2008
3 1 4 25/10/2008
2 1 5 25/10/2008

Rating
Number Name
1 No Experience
2 Beginner
3 Intermediate
4 Proficient
5 Expert

Skill
Number Name
1 .Net
2 SQL
3 Java
4 Manual Testing
12 Siebel

I would get Employee number, From date and To date as input to the Stored Procedure. I need to return the result set in the following manner. How do I get the column values as the column headings and display the desired result as shown below

Skill No Experience Beginner Intermediate Proficient Expert
.net 01/01/2007
SQL 08/01/2007 25/10/2008
Java 02/10/2007 25/10/2008

Please help me in writing this stored procedure.
Thanks before hand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 08:52:28

use this query

SELECT s.Name,
MAX(CASE WHEN r.Name='No Experience' THEN es.Date ELSE NULL END) AS [No Experience],
MAX(CASE WHEN r.Name='Beginner' THEN es.Date ELSE NULL END) AS [Beginner],
MAX(CASE WHEN r.Name='Intermediate' THEN es.Date ELSE NULL END) AS [Intermediate],
MAX(CASE WHEN r.Name='Proficient' THEN es.Date ELSE NULL END) AS [Proficient],
MAX(CASE WHEN r.Name='Expert' THEN es.Date ELSE NULL END) AS [Expert]
FROM Employee_skill es
INNER JOIN Skill s
ON es.Skill_NBR=s.Number
INNER JOIN Rating r
ON r.Number=es.rating 
GROUP BY s.Name

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-30 : 09:16:55

Thanks vishakh. But the requirement is again changed now.It seems the data should be displayed along with the end date.. comething like this

Skill No Experience Beginner Intermediate Proficient Expert
.net 01/01/2007 to 10/30/2008
SQL 08/01/2007 to 25/10/2008 25/10/2008 to 10/30/2008
Java 02/10/2007 to 25/10/2008 25/10/2008 to 10/30/2008

If the skill is not rated again then to date will be today's date nor else to date of the previous one will be rated date of next record.

Hope the data above is all clear. as i dont have option to format much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 09:18:45

sorry didnt get format you want. you mean you want each levels data to come as from-to

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-30 : 09:26:24

say for eg- a person is rated as begineer on 10th july 2008 and as proficient on 10th oct 2008, then the data should be sisplayed sumthing like this

Skill NO-Ex begineer Inter proficient Expert
.net 10-jul-2008 to 10-Oct-2008 10th-oct-2008 to 31 Oct2008

31st Oct2008 should be todays date coz it is not rated again after this

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-30 : 09:28:24

Begineer 10th Jul 2008 - 10th Oct 2008

Proficient 10th Oct 2008 - 30 Oct 2008

Under each Rating name dates should be displayed like this
Nor else just null

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-10-30 : 09:39:17

can you show what you have tried so far

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-30 : 09:48:37

I've tried the same which vishakh gave the above query.. which worked very much fine

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-31 : 01:00:51

Hi. Please I need that change..
can anyone help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 02:55:00

may be this

declare @Employee_skill table
(
Skill_NBR int,
Employee_nbr int,
--supervisor int,
rating int,
Date datetime
)
insert into @Employee_skill
select 1, 1, 3 ,'01/01/2007' union all
select 2, 1, 4, '08/01/2007' union all
select 3, 1, 2, '02/10/2007' union all
select 12, 2, 4, '02/10/2007' union all
select 12, 2, 3, '02/10/2007' union all
select 5, 8, 4, '01/08/2008' union all
select 3, 1, 4, '25/10/2008' union all
select 2, 1, 5, '25/10/2008' union all
select 2,1,2,'30/06/2006'

declare @Rating table
(
Number int,
Name varchar(20)
)
insert into @Rating
select 1, 'No Experience' union all
select 2, 'Beginner' union all
select 3, 'Intermediate' union all
select 4, 'Proficient' union all
select 5, 'Expert'

declare @Skill table
(
Number int,
Name varchar(20)
)
insert into @Skill
select 1, '.Net' union all
select 2, 'SQL' union all
select 3, 'Java' union all
select 4, 'Manual Testing' union all
select 12, 'Siebel'

SELECT SkillName,Employee_NBR,
CONVERT(varchar(11),MAX(CASE WHEN Number=1 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),COALESCE(MIN(CASE WHEN Number>1 THEN t.Date END),GETDATE()),121) AS [No Experience],
CONVERT(varchar(11),MAX(CASE WHEN Number=2 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),COALESCE(MIN(CASE WHEN Number>2 THEN t.Date END),GETDATE()),121) AS Beginner,
CONVERT(varchar(11),MAX(CASE WHEN Number=3 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),COALESCE(MIN(CASE WHEN Number>3 THEN t.Date END),GETDATE()),121) AS Intermediate,
CONVERT(varchar(11),MAX(CASE WHEN Number=4 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),COALESCE(MIN(CASE WHEN Number>4 THEN t.Date END ),GETDATE()),121) AS Proficient,
CONVERT(varchar(11),MAX(CASE WHEN Number=5 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),GETDATE(),121) AS Expert

FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY s.Name ORDER BY r.Number) AS Seq,es.Employee_NBR,r.Number,s.Name AS SkillName,r.Name AS RateName,es.rating,es.Date,es.Skill_NBR
FROM @Employee_skill es
INNER JOIN @Rating r
on r.Number=es.rating
INNER JOIN @Skill s
ON s.Number=es.Skill_NBR
) t
GROUP BY SkillName,Employee_NBR
ORDER BY Employee_NBR,SkillName

output
--------------------------------------------------

SkillName            Employee_NBR No Experience              Beginner                   Intermediate               Proficient                 Expert
-------------------- ------------ -------------------------- -------------------------- -------------------------- -------------------------- --------------------------
.Net                 1            NULL                       NULL                       2007-01-01  to 2008-10-31  NULL                       NULL
Java                 1            NULL                       2007-10-02  to 2008-10-25  NULL                       2008-10-25  to 2008-10-31  NULL
SQL                  1            NULL                       2006-06-30  to 2007-01-08  NULL                       2007-01-08  to 2008-10-25  2008-10-25  to 2008-10-31 
Siebel               2            NULL                       NULL                       2007-10-02  to 2007-10-02  2007-10-02  to 2008-10-31  NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-31 : 03:19:13

But When i Execute your its giving me an error message like this
Msg 242, Level 16, State 3, Line 11
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

(5 row(s) affected)

(5 row(s) affected)

(0 row(s) affected)

But the output which u have shown is what i exactly wanted

quote:

Originally posted by visakh16

may be this

declare @Employee_skill table
(
Skill_NBR int,
Employee_nbr int,
--supervisor int,
rating int,
Date datetime
)
insert into @Employee_skill
select 1, 1, 3 ,'01/01/2007' union all
select 2, 1, 4, '08/01/2007' union all
select 3, 1, 2, '02/10/2007' union all
select 12, 2, 4, '02/10/2007' union all
select 12, 2, 3, '02/10/2007' union all
select 5, 8, 4, '01/08/2008' union all
select 3, 1, 4, '25/10/2008' union all
select 2, 1, 5, '25/10/2008' union all
select 2,1,2,'30/06/2006'

declare @Rating table
(
Number int,
Name varchar(20)
)
insert into @Rating
select 1, 'No Experience' union all
select 2, 'Beginner' union all
select 3, 'Intermediate' union all
select 4, 'Proficient' union all
select 5, 'Expert'

declare @Skill table
(
Number int,
Name varchar(20)
)
insert into @Skill
select 1, '.Net' union all
select 2, 'SQL' union all
select 3, 'Java' union all
select 4, 'Manual Testing' union all
select 12, 'Siebel'
;With CTE AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY s.Name ORDER BY r.Number) AS Seq,r.Number,s.Name AS SkillName,r.Name AS RateName,es.rating,es.Date,es.Skill_NBR
FROM @Employee_skill es
INNER JOIN @Rating r
on r.Number=es.rating
INNER JOIN @Skill s
ON s.Number=es.Skill_NBR
)

SELECT SkillName,Employee_NBR,
CONVERT(varchar(11),MAX(CASE WHEN Number=1 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),COALESCE(MIN(CASE WHEN Number>1 THEN t.Date END),GETDATE()),121) AS [No Experience],
CONVERT(varchar(11),MAX(CASE WHEN Number=2 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),COALESCE(MIN(CASE WHEN Number>2 THEN t.Date END),GETDATE()),121) AS Beginner,
CONVERT(varchar(11),MAX(CASE WHEN Number=3 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),COALESCE(MIN(CASE WHEN Number>3 THEN t.Date END),GETDATE()),121) AS Intermediate,
CONVERT(varchar(11),MAX(CASE WHEN Number=4 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),COALESCE(MIN(CASE WHEN Number>4 THEN t.Date END ),GETDATE()),121) AS Proficient,
CONVERT(varchar(11),MAX(CASE WHEN Number=5 THEN t.Date ELSE NULL END),121) + ' to '+ CONVERT(varchar(11),GETDATE(),121) AS Expert

FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY s.Name ORDER BY r.Number) AS Seq,es.Employee_NBR,r.Number,s.Name AS SkillName,r.Name AS RateName,es.rating,es.Date,es.Skill_NBR
FROM @Employee_skill es
INNER JOIN @Rating r
on r.Number=es.rating
INNER JOIN @Skill s
ON s.Number=es.Skill_NBR
) t
GROUP BY SkillName,Employee_NBR
ORDER BY Employee_NBR,SkillName

output
--------------------------------------------------

SkillName            Employee_NBR No Experience              Beginner                   Intermediate               Proficient                 Expert
-------------------- ------------ -------------------------- -------------------------- -------------------------- -------------------------- --------------------------
.Net                 1            NULL                       NULL                       2007-01-01  to 2008-10-31  NULL                       NULL
Java                 1            NULL                       2007-10-02  to 2008-10-25  NULL                       2008-10-25  to 2008-10-31  NULL
SQL                  1            NULL                       2006-06-30  to 2007-01-08  NULL                       2007-01-08  to 2008-10-25  2008-10-25  to 2008-10-31 
Siebel               2            NULL                       NULL                       2007-10-02  to 2007-10-02  2007-10-02  to 2008-10-31  NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 03:40:40

try

set dateformat dmy


my last query (i've edited it use new one )
set dateformat mdy

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-31 : 04:37:52

Yeah it worked now. Thanks Vishakh. Just last thing.. is it possible to show the date as 31 Oct 2008 instead of 10/31/2008
in the same query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 04:41:15

yup. use CONVERT(varchar(9),MAX(CASE WHEN...END),106) instead of current expression

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-31 : 04:48:34

Yeah Worked Vishal.. But its working when i give the varchar length as (11) not 9..

I really thank u for the code....

Can u tell me what is the use of the CTE and COALESCE function in the above context

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 04:57:08

quote:
Originally posted by swathigardas

Yeah Worked Vishal.. But its working when i give the varchar length as (11) not 9..

I really thank u for the code....

Can u tell me what is the use of the CTE and COALESCE function in the above context

you dont require that CTE. i was trying out with it but later modified it. thats why i asked you to use modified query(i edited it later).
COALESCE() is used for replacing NULL values with current date (this happens when you have no other change of skills until today)

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-10-31 : 05:22:04

Yeah Got it a Bit.. but still i'm trying to understand the code u have written..
Thanks A lot vishakh.. U r Genious

Hey one thing.. are u the moderator of this site...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 05:26:11

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources