Query 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)

Query problem

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-05 : 00:06:12

I have a report which has two subreports.
the main report gives the count of Requirements for a company, no. of openings, no. of resumes posted for that opening etc....

the detail report is a drill-through report.
In the drill thru report, i will see the requirement details.

like master will have :

User Requirements openings Resumes sent
ABC 2 3 13

Detail contains:

RequirementTitle No.of Positions Resumes Sent
JAVA Developer 2 6
DB Developer 1 7

This is the requirement...

Now i think i must write a co-related subquery to get the count of resumes against each req title.

This is my query:

DECLARE @locid INT

SET @locid = 102

DECLARE @rid BIGINT

SET @rid = 18

DECLARE @ClientID BIGINT

SET @ClientID = 14

DECLARE @fromdate DATETIME

SET @fromdate = '01/01/2010'

DECLARE @todate DATETIME

SET @todate = '05/01/2010'

Select DISTINCT HC_CLIENTS.ClientName,HC_ REQUISITIONS.ReqTitle,HC_REQ_ TEAM.Position

From HC_REQ_TEAM

JOIN HC_USERS ON HC_REQ_TEAM.TeamUserID = HC_USERS.RID

JOIN HC_REQUISITIONS ON HC_REQ_TEAM.ReqID = HC_REQUISITIONS.RID

JOIN HC_REQ_RESUME ON HC_REQ_RESUME.ReqID = HC_REQUISITIONS.RID

JOIN HC_REQ_RESUME_STATUS ON HC_REQ_RESUME_STATUS.reqresume ID = HC_REQ_RESUME.RID

JOIN HC_CLIENTS ON HC_REQUISITIONS.ClientID = HC_CLIENTS.RID

WHERE HC_REQ_TEAM.TeamUserID IN (@rid)

AND HC_REQUISITIONS.ClientID in (@ClientID)

AND HC_REQ_RESUME_STATUS.StatusDat e between (@fromdate) and (@todate)

AND HC_USERS.locationid in (@locid)

This gives me the correct result.

When i include one more query to display count of resumes, i am getting 13 against each title.
i must get 6 and 7 respectively as shown above.

The code i must add to the above query is like this:

(Select Count(HC_REQ_RESUME.RID)
from HC_REQ_RESUME WITH(NOLOCK)

WHERE HC_REQ_RESUME.CreatedUser=HC_ USERS.RID

and HC_REQ_RESUME.CreatedDate between (@fromdate ) and (@todate)

AND HC_REQ_RESUME.ReqID in(Select RID *******

From HC_REQUISITIONS WITH(NOLOCK)

WHERE HC_REQUISITIONS.ClientID in (select value from dbo.split(',',@ClientID))) ) as 'Resumes'

Here, the RID with * mark must now be selected for combination of that client and reqtitle .

Hope i have made req clear....
Is it a co-related subquery tht is required?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 02:48:27

you can use correlated query or a join i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-05 : 02:52:05

This is only a part of the query i sent. there are 5 such count columns.
all of them must be grouped by ReqTitle.

my prob is now i included the resumes count. but the next column, count(resumes where status = 1 ) as CV_Sent cannot be added to the same query as it is from a different table.
if i join that table, then my resume count will be affected.

how to go abt this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 02:54:44

plzz provide some sample data to understand your scenario and then explain

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-05 : 03:15:48

User Name||-||Requirements||-||Openings||Resumes||-||CV Sent||-||Total Interview||-||Offer Made
Manju||----------||2||----------||3||---||13||-----||7||----------9----------------||2

The above is my master report.

When you click on Requirements, a subreport opens and it is like this:

Requirement Title||-||Positions||-||Resumes||-||CV_Sent||-||Interview Scheduled||-||Offer Made
JAVA Developer||---------|| 2 ||--------||6 ||-------||3||--------||3||------------||2||

like this there is another record Team leader with similar data.

Now my prob is , The cols from CV_Sent to Offer Made are got from hc_req_Resume_status table .
they are the count of resumes under diff status.
i want those rows to return count based on the requirement title.

i have queries for them ,but i am not able to put them into same query.
pls help.

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-05 : 03:40:52

This is the whole query:
***********************************************************************************************
DECLARE @locid INT
SET @locid = 102
DECLARE @rid BIGINT
SET @rid = 18
DECLARE @ClientID BIGINT
SET @ClientID = 14
DECLARE @fromdate DATETIME
SET @fromdate = '01/01/2010'
DECLARE @todate DATETIME
SET @todate = '05/01/2010'
Select HC_CLIENTS.ClientName,HC_REQUISITIONS.ReqTitle,HC_REQ_TEAM.position
,(select Count(HC_REQ_RESUME.RID)
from HC_REQ_RESUME WITH(NOLOCK)
WHERE HC_REQ_RESUME.CreatedUser=HC_USERS.RID
and HC_REQ_RESUME.CreatedDate between (@fromdate ) and (@todate)
AND HC_REQ_RESUME.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (select value from dbo.split(',',@ClientID))) ) as 'Resumes'
,(select Count(distinct HC_REQ_RESUME_STATUS.ReqResumeID)
From HC_REQ_RESUME_STATUS WITH(NOLOCK),HC_REQ_RESUME WITH(NOLOCK)
WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)
and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID
AND HC_REQ_RESUME_STATUS.UserID=HC_USERS.RID
AND HC_REQ_RESUME.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (select value from dbo.split(',',@ClientID)))
and HC_REQ_RESUME_STATUS.StatusType=2
and HC_REQ_RESUME_STATUS.StageType=2
And HC_REQ_RESUME_STATUS.Stage=1) as 'CV-Sent'
,(select Count(distinct HC_REQ_RESUME_STATUS.ReqResumeID)
From HC_REQ_RESUME_STATUS WITH(NOLOCK),HC_REQ_RESUME WITH(NOLOCK)
WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)
and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID
AND HC_REQ_RESUME_STATUS.UserID=HC_USERS.RID
AND HC_REQ_RESUME.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (select value from dbo.split(',',@ClientID)))
and HC_REQ_RESUME_STATUS.StageType=3) as 'TotalInterview'
,(select Count(distinct HC_REQ_RESUME_STATUS.ReqResumeID)
From HC_REQ_RESUME_STATUS WITH(NOLOCK),HC_REQ_RESUME WITH(NOLOCK)
WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)
and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID
AND HC_REQ_RESUME_STATUS.UserID=HC_USERS.RID
AND HC_REQ_RESUME.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (select value from dbo.split(',',@ClientID)))
and HC_REQ_RESUME_STATUS.StatusType = 2
and HC_REQ_RESUME_STATUS.StageType=5
And HC_REQ_RESUME_STATUS.Stage=1 ) as 'Offer Made'
,(select Count(distinct HC_REQ_RESUME_STATUS.ReqResumeID)
From HC_REQ_RESUME_STATUS WITH(NOLOCK),HC_REQ_RESUME WITH(NOLOCK)
WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate) and (@todate)
and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID
AND HC_REQ_RESUME_STATUS.UserID=HC_USERS.RID
AND HC_REQ_RESUME.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (select value from dbo.split(',',@ClientID)))
and HC_REQ_RESUME_STATUS.StatusType=4
and HC_REQ_RESUME_STATUS.StageType=6
And HC_REQ_RESUME_STATUS.Stage=1
and HC_REQ_RESUME_STATUS.ReqResumeID not in (select ReqResumeID
from HC_REQ_RESUME_STATUS
where StageType = 5 and StatusType > 10)) as 'Joined'
From HC_REQUISITIONS
JOIN HC_REQ_TEAM ON HC_REQ_TEAM.ReqID = HC_REQUISITIONS.RID
JOIN HC_CLIENTS ON HC_CLIENTS.RID = HC_REQUISITIONS.ClientID
JOIN HC_REQ_RESUME ON HC_REQ_RESUME.ReqID = HC_REQUISITIONS.RID
JOIN HC_USERS ON hc_req_resume.createduser = hc_users.rid
WHERE HC_USERS.RID in(@rid)
AND HC_USERS.Locationid in (@locid) AND HC_CLIENTS.RID in (@ClientID)
--(4,1,18,17 )
Group By HC_USERS.RID,HC_USERS.UserName,HC_CLIENTS.ClientName,HC_REQUISITIONS.ReqTitle,HC_REQ_TEAM.position
****************************************************************************************************

The result set i get is like this:
Client Name ReqTitle Openings Resumes CV_SEnt Intw OfferMade Joined
MM Technology Java Developer 2 13 7 9 2 1
MM Technology Team Leader 1 13 7 9 2 1

----------------------------------------------------------------------------------------------------
But the numbers i am getting are the total counts. I must get the count against title.
Client Name ReqTitle Openings Resumes CV_SEnt Intw OfferMade Joined
MM Technology Java Developer 2 6 3 5 2 1
MM Technology Team Leader 1 7 4 4 2 1
****************************************************************************************************

Could you please tell me how i can get this?

like this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources