February 25, 2013 at 11:10 pm
i have this table
CREATE TABLE [dbo].[HRCandidateProject](
[ProjectId] [bigint] IDENTITY(1,1) NOT NULL,
[orgid] [int] NULL,
[uid] [int] NULL,
[CandidateId] [bigint] NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[SkillSet] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Role] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Client] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectDiscription] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TotalExperience][int]
CONSTRAINT [PK_HRCandidateProject] PRIMARY KEY CLUSTERED
(
[ProjectId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
in a drop down list i have values for total experience 1-2 years 2-4 years 4-7 years more than 7 years
i want to write a query that will segregate the experience like this.
February 25, 2013 at 11:19 pm
I think the best way is to use the ROW_NUMBER operator in a CASE statement. Here's a test procedure you can run to see how it works.
CREATE PROCEDURE dbo.TestConditionalOrdering
@SortKey VARCHAR(50)
,@SortDir VARCHAR(4) = 'ASC'
AS
BEGIN
/*
Variations:
EXEC dbo.TestConditionalOrdering NULL,NULL
EXEC dbo.TestConditionalOrdering '',''
EXEC dbo.TestConditionalOrdering 'EmpDOB','ASC'
EXEC dbo.TestConditionalOrdering 'EmpDOB','DESC'
EXEC dbo.TestConditionalOrdering 'EmpName','ASC'
EXEC dbo.TestConditionalOrdering 'EmpName','DESC'
*/
SET NOCOUNT ON
/* Do some validation and set the defaults */
IF @SortKey NOT IN ('EmpDOB','EmpName') -- include valid sortkey values here
SET @SortKey = 'EmpDOB'
SET @SortDir = ISNULL(NULLIF(@SortDir,''),'ASC')
/* Create the temp table with primary key */
IF OBJECT_ID('tempdb..[#Employee]') IS NOT NULL
DROP TABLE [#Employee]
CREATE TABLE [#Employee]
(
[EmpID] [int] NOT NULL
,[EmpName] [varchar](50) NOT NULL
,[EmpDOB] [datetime] NULL
,PRIMARY KEY ([EmpID],[EmpName])
,UNIQUE ([EmpID],[EmpName])
)
/* Insert some test data */
INSERT INTO [#Employee]
VALUES (1, 'Chandler', '5/3/1953')
INSERT INTO [#Employee]
VALUES (2, 'Enos', '9/2/1992')
INSERT INTO [#Employee]
VALUES (3, 'Baker', '8/10/1946')
INSERT INTO [#Employee]
VALUES (4, 'Davis', '4/1/1980')
INSERT INTO [#Employee]
VALUES (5, 'Franklin', '7/4/1976')
/* Run the query */
SELECT
ROW_NUMBER() OVER (ORDER BY
CASE
WHEN @SortKey = 'EmpDOB' AND @SortDir = 'DESC' THEN SortByEmpDOB_DESC
WHEN @SortKey = 'EmpDOB' THEN SortByEmpDOB
WHEN @SortKey = 'EmpName' AND @SortDir = 'DESC' THEN SortByEmpName_DESC
WHEN @SortKey = 'EmpName' THEN SortByEmpName
END
) AS RowNum
,EmpID
,EmpName
,EmpDOB
FROM
(
SELECT
EmpID
,EmpName
,EmpDOB
,DATEDIFF(dd,GETDATE(),EmpDOB) AS SortByEmpDOB
,DATEDIFF(dd,EmpDOB,GETDATE()) AS SortByEmpDOB_DESC
,RANK() OVER (ORDER BY EmpName) AS SortByEmpName
,RANK() OVER (ORDER BY EmpName DESC) AS SortByEmpName_DESC
FROM
#Employee
WHERE
EmpID > 0
) AS Result
END
February 26, 2013 at 11:32 am
I may be reading the question wrong, but this may do what you want
SELECT Columns
FROM HRCandidateProject
ORDER BY CASE
WHEN TotalExperience BETWEEN 1 and 2 THEN 1
WHEN TotalExperience BETWEEN 3 and 4 THEN 2
WHEN TotalExperience BETWEEN 4 and 7 THEN 3
WHEN TotalExperience > 7 THEN 4
ELSE 0
END, Other Ordering Columns
February 26, 2013 at 1:25 pm
mickyT (2/26/2013)
I may be reading the question wrong, but this may do what you want
SELECT Columns
FROM HRCandidateProject
ORDER BY CASE
WHEN TotalExperience BETWEEN 1 and 2 THEN 1
WHEN TotalExperience BETWEEN 3 and 4 THEN 2
WHEN TotalExperience BETWEEN 4 and 7 THEN 3
WHEN TotalExperience > 7 THEN 4
ELSE 0
END, Other Ordering Columns
To build on this and give the categorization, but moving the candidates with the most experience to the top of the list:
SELECT Columns,
CASE
WHEN TotalExperience BETWEEN 1 and 2 THEN '1-2 Years'
WHEN TotalExperience BETWEEN 3 and 4 THEN '3-4 Years'
WHEN TotalExperience BETWEEN 4 and 7 THEN '4-7 Years'
WHEN TotalExperience > 7 THEN 'More than 7 Years'
ELSE 'Other'
END ExperienceCategory
FROM HRCandidateProject
ORDER BY CASE
WHEN TotalExperience BETWEEN 1 and 2 THEN 3
WHEN TotalExperience BETWEEN 3 and 4 THEN 2
WHEN TotalExperience BETWEEN 4 and 7 THEN 1
WHEN TotalExperience > 7 THEN 0
ELSE 4
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply