March 1, 2010 at 12:55 am
Get Similar recordes base on usage and matching criteria
Hi All,
I have requiremnt to find similar recordes base on its usage and maching criteria.
I did this by firing 3 quuries seperately. but i not got my desire output.
For further information please look at my attached .xls file
My query is
Declare @CanID bigint
set @CanID=45
Declare @Exp decimal
Set @Exp=(Select Exp from Candidate where ID=@CanID)
Declare @Location bigint
set @Location=(Select Location from Candidate where ID=@CanID)
Select Distinct ABX.RID from (
Select CandidateID as ID,'0' as Exp,Status from CandidateUsedForProject where ProjectId in (Select a.ProjectId from CandidateUsedForProject as a where a.CandidateID=@CanID) and Status>=2
UNION ALL
Select ID,Exp,0 as Status From Candidate where Exp between @Exp-1 and @Exp+1 and Location=@Location
and ID in ( Select CandidateID from CandidateFunctionIds where FunCtionID in (select a.FunctionID from CandidateFunctionIds as a where a.CandidateID=@CanID))
) as ABX where ABX.RID <> @CanID
order by ABX.Status
March 1, 2010 at 7:12 am
Hi Pritesh
It's not obvious what you are trying to do, but let's have a go anyway. The first thing to do is to break your problem down into separate parts, get them right, then reassemble into a solution.
Let's start with the query you've posted, which is a UNION of two queries which are quite different.
Call them Query 1 and Query 2
Here's a "corrected" version of Query 1:
-- get all rows from cp1 where the projectID matches...
SELECT
cp1.CandidateID AS [ID],
'0' AS [Exp],
cp1.[Status]
FROM CandidateUsedForProject cp1
-- this ProjectID, for the selected candidate @CanID / status range
INNER JOIN CandidateUsedForProject cp2
ON cp2.ProjectId = cp1.ProjectId
AND cp2.CandidateID = @CanID
AND cp2.[Status] >= 2
Start small. Check that the filter (the INNER JOINed part) is returning the correct results by turning it into a simple SELECT:
SELECT TOP 100 *
FROM CandidateUsedForProject cp2
WHERE cp2.CandidateID = @CanID
AND cp2.[Status] >= 2
If and when you're confident that Query 1 is returning the correct data, then begin work on Query 2. Don't attempt to join anything together until you have tested the individual components and confirmed that they do what they are supposed to do.
If nobody has told you yet, the most important tool for writing any program is your eyes - look at the result of your code, at every step.
So, work on Query 1 until it's working correctly, then post your finished Query 1 here. Once it's been checked over, move onto Query 2, and so on. Come back here for help when you need it.
Read about JOIN syntax in Books On-line, the help system which comes with SQL Server.
Can you set up some sample data for people to experiment with? Instructions to do this are in the link in my sig below.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 1, 2010 at 8:36 pm
Hi Chris,
My above posted query is wroking fine. I have problem with order by Status.
I wand distinct CandidateIDs from Candidate and CandidateProject table where i want to give priority to candidateproject table using order by status.
that is what not happening.
My input and output values are there in attached excel.
regards,
Pritesh
March 1, 2010 at 10:59 pm
pritesh-807803 (3/1/2010)
Hi Chris,My above posted query is wroking fine. I have problem with order by Status.
I wand distinct CandidateIDs from Candidate and CandidateProject table where i want to give priority to candidateproject table using order by status.
that is what not happening.
My input and output values are there in attached excel.
regards,
Pritesh
There is no column RID in derived table ABX.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 1, 2010 at 11:28 pm
pritesh-807803 (3/1/2010)
My input and output values are there in attached excel.
Pritesh, I know you've provided sample data in a spreadsheet, but I simply don't have the time to turn the content into table creation scripts and populate the tables with data. If you do this yourself you may be surprised at the response you get - it's so much easier for folks to work on your project. If you're unsure then check out this thread - see what happens when the poster provides some data for, by anybody's standards, quite a complex problem.
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 2, 2010 at 1:58 am
DECLARE @Candidate table(
id INT IDENTITY(1,1),[Name] nvarchar(200), [Location] nvarchar(200),
Exp decimal)
insert into @Candidate
Select 'Pritesh Patel','Bangalor',2 union all
Select 'Balaji','Bangalor',4 union all
Select 'IRIN','Mumbai',2 union all
Select 'Manju','Bangalor',3 union all
Select 'Rajesh','Mumbai',2 union all
Select 'Jeevan','Ahemdabad',5 ;
--Select * from @Candidate
DECLARE @CandidateFunctionIds table(
id INT IDENTITY(1,1),CandidateID INT,FunctionID INT )
insert into @CandidateFunctionIds
Select 1,2 union all
Select 1,3 union all
Select 2,3 union all
Select 2,8 union all
Select 2,7 union all
Select 3,2 union all
Select 4,2 union all
Select 5,2;
--select * from @CandidateFunctionIds
DECLARE @CandidateUsedForProject table(
id INT IDENTITY(1,1),ProjectId INT,CandidateID INT,Status INT )
insert into @CandidateUsedForProject
Select 1,1,2 union all
Select 1,6,2 union all
Select 1,2,1 union all
Select 2,2,2 union all
Select 2,5,2 union all
Select 3,1,2 union all
Select 3,5,2 ;
-- Select * from @CandidateUsedForProject
--Query
Declare @CanID int
set @CanID=1
Declare @Exp decimal
Set @Exp=(Select Exp from @Candidate where ID=@CanID)
Declare @Location nvarchar(200)
set @Location=(Select Location from @Candidate where ID=@CanID)
DECLARE @SimilarCandidate table(
id INT IDENTITY(1,1),CandidateID INT,SimilarID INT,SlNumber INT )
insert into @SimilarCandidate
Select Distinct @CanID as CandidateID, ABX.ID as SimilarID,row_number() over (ORDER BY ABX.ID ) from (
Select CandidateID as ID,'0' as Exp,Status from @CandidateUsedForProject where ProjectId in (Select a.ProjectId from @CandidateUsedForProject as a where a.CandidateID=@CanID) and Status>=2
UNION ALL
Select ID,Exp,0 as Status From @Candidate where Exp between @Exp-1 and @Exp+1 and Location=@Location
and ID in ( Select CandidateID from @CandidateFunctionIds where FunCtionID in (select a.FunctionID from @CandidateFunctionIds as a where a.CandidateID=@CanID))
) as ABX where ABX.ID <> @CanID
select * from @SimilarCandidate
Hi Now my problem is i want data base on Status but becouse of Distinct ABX.ID i am not able to do.
March 2, 2010 at 2:20 am
Thank you Pritesh, this is very helpful.
The first part of your UNION query looks like this:
Select CandidateID as ID, '0' as Exp, Status
from @CandidateUsedForProject
where ProjectId in (Select a.ProjectId from @CandidateUsedForProject as a where a.CandidateID=@CanID)
and Status >= 2
Can you tell us why you are using IN rather than an INNER JOIN?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 2, 2010 at 2:36 am
Hi,
i can use "inner join" or "subquery"
I use Subquery. there is no purpose to use specialy subquery.
thanks
Pritesh
March 2, 2010 at 2:58 am
Hi All,
Got Solution
DECLARE @Candidate table(
id INT IDENTITY(1,1),[Name] nvarchar(200), [Location] nvarchar(200),
Exp decimal)
insert into @Candidate
Select 'Pritesh Patel','Bangalor',2 union all
Select 'Balaji','Bangalor',4 union all
Select 'IRIN','Mumbai',2 union all
Select 'Manju','Bangalor',3 union all
Select 'Rajesh','Mumbai',2 union all
Select 'Jeevan','Ahemdabad',5 ;
--Select * from @Candidate
DECLARE @CandidateFunctionIds table(
id INT IDENTITY(1,1),CandidateID INT,FunctionID INT )
insert into @CandidateFunctionIds
Select 1,2 union all
Select 1,3 union all
Select 2,3 union all
Select 2,8 union all
Select 2,7 union all
Select 3,2 union all
Select 4,2 union all
Select 5,2;
--select * from @CandidateFunctionIds
DECLARE @CandidateUsedForProject table(
id INT IDENTITY(1,1),ProjectId INT,CandidateID INT,Status INT )
insert into @CandidateUsedForProject
Select 1,1,2 union all
Select 1,6,4 union all
Select 1,2,1 union all
Select 2,2,2 union all
Select 2,5,2 union all
Select 3,1,2 union all
Select 3,5,2 ;
-- Select * from @CandidateUsedForProject
--Query
Declare @CanID int
set @CanID=1
Declare @Exp decimal
Set @Exp=(Select Exp from @Candidate where ID=@CanID)
Declare @Location nvarchar(200)
set @Location=(Select Location from @Candidate where ID=@CanID)
DECLARE @SimilarCandidate table(
id INT IDENTITY(1,1),CandidateID INT,SimilarID INT,SlNumber INT )
insert into @SimilarCandidate
Select Distinct @CanID as CandidateID, ABX.ID as SimilarID,row_number() over (ORDER BY @@identity ) from (
Select top 500 CandidateID as ID,'0' as Exp,Status from @CandidateUsedForProject where ProjectId in (Select a.ProjectId from @CandidateUsedForProject as a where a.CandidateID=@CanID) and Status>=2 order by Status desc
UNION ALL
Select ID,Exp,0 as Status From @Candidate where Exp between @Exp-1 and @Exp+1 and Location=@Location
and ID in ( Select CandidateID from @CandidateFunctionIds where FunCtionID in (select a.FunctionID from @CandidateFunctionIds as a where a.CandidateID=@CanID))
) as ABX where ABX.ID <> @CanID
select * from @SimilarCandidate
Used top 500 and order by in my first query
thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply