Untitled

USE [Parikshak8]
GO

/****** Object:  StoredProcedure [schema_HRM3932DS].[SP_prob5_Q1]    Script Date: 12-02-2024 15:06:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [schema_HRM3932DS].[SP_prob5_Q1]
 AS
 
 /* Refer to the Candidates table.
 The Candidates table has details on each candidate who has experience in different programming languages. 
 One candidate can have experience in more than one programming language. 
 The experience in years, of each programming language is given in same order of programming languages mentioned in Skills column. 
 For example, Python, C -> 3,5 indicates that a candidate has experience of 3 years in Python and of 5 years in C.
 
 The objective is to find the candidates having maximum experience in each programming language.
 Create a view called vw_candidate_skills to display the candidates having maximum experience in each programming language.
 Each programming language should be in a separate row. 
 Multiple programming language may have same candidates.
 
 Refer to the sample output below for the required output schema details and the column names.
 
 Sample Output:
 
  Skills   Candidate_Name	Experience_Years	Country
    C      Michael Johnson		  6				Canada
   Java	  Michael Johnson         5				Canada
   Html	  Yuki Tanaka             5             Japan
 
 Please keep the object name as follows:
 View -> vw_candidate_skills
 
 (Tags: String Functions)
 */
GO
create view vw_candidate_skills as

WITH SplitSkills AS (
	Select 
		Candidate_Name,
		trim(value) as skill,
		ROW_NUMBER() OVER (PARTITION BY Candidate_Name ORDER BY (SELECT NULL)) AS row_num,
		CHARINDEX(',',skills) AS comma_index
	from 
		Candidates
		cross apply string_split(skills,',')
	),
	SplitExpYears AS(
	select 
		Candidate_name,
		TRIM (value) AS exp_years,
		ROW_NUMBER() OVER(PARTITION BY Candidate_Name ORDER BY (SELECT NULL)) AS row_num
	FROM
			Candidates
			Cross Apply string_split (Experience_years, ',')
		),
	SplitCountry AS(
	Select
		Candidate_Name,
		Country
	FROM
		Candidates
	Group by 
		Candidate_Name,
		Country
	),
	RankedCandidates AS (
		Select s.Candidate_Name,
		s.skill,
		cast(e.exp_years as int) as exp_years,
		c.country,
		ROW_NUMBER() over (Partition by s.skill order by cast (e.exp_years as int)DESC ) AS rank
	From SplitSkills s
	Join
		SplitExpYears e on s.Candidate_Name = e.Candidate_Name and s.row_num = e.row_num
	Join
		SplitCountry c on s.Candidate_Name=c.Candidate_Name
	)
	select skill as Skills, Candidate_Name,exp_years as Experience_years, Country from RankedCandidates where rank = 1;