May 13, 2010 at 1:35 am
Hi All,
I have written a query to get count of various fields. This is based on the users table.
I now created 31 new requirements but it is not showing data for tht.
this is for a report and hence i am passing parameters. if i pass the corresponding user id and client id then it works but if i dont give them it is not working. it must return all the 31 rows in that case (when user id anf client id are not given).
here is my SP. i am showing only 3 columns here for convenience....
****************************************************************************************************************
ALTER PROC [dbo].[sp_UserPerformance] (@locid bigint,@rid varchar(100),@ClientID varchar(250),@fromdate datetime,@todate datetime)
AS
BEGIN
IF @rid = 0
BEGIN
IF @ClientID = 0
BEGIN
SELECT distinct hc_users.rid,hc_users.username AS 'User Name'
,a.rid as clientid,a.clientname
,(SELECT sum(distinct hc_Req_team.positiON)
FROM hc_req_team
JOIN hc_requisitiONs ON hc_req_team.reqid = hc_requisitiONs.rid
JOIN hc_users ON hc_users.rid = hc_req_team.teamuserid
JOIN hc_clients b ON hc_requisitiONs.clientid = b.rid WHERE a.rid = b.rid) AS 'Openings'
,(SELECT count(distinct hc_req_team.reqID)
FROM hc_req_team
JOIN hc_requisitiONs ON hc_req_team.reqid = hc_requisitiONs.rid
JOIN hc_users ON hc_users.rid = hc_req_team.teamuserid
JOIN hc_clients b ON hc_requisitiONs.clientid = b.rid WHERE a.rid = b.rid) AS 'Requirements'
,(SELECT Count(distinct HC_REQ_RESUME.RID)
FROM hc_req_resume
JOIN hc_users ON hc_req_resume.createduser = hc_users.rid
JOIN hc_requisitiONs ON hc_req_resume.reqid = hc_requisitiONs.rid
JOIN hc_clients b ON hc_requisitiONs.clientid = b.rid WHERE a.rid = b.rid
AND hc_req_resume.CreatedDate between (@fromdate) AND (@todate)) AS 'Resumes'
FROM hc_users
JOIN hc_req_resume ON hc_users.rid = hc_req_resume.createduser and hc_req_resume.createddate between (@fromdate) and (@todate)
JOIN hc_requisitions ON hc_req_Resume.reqid = hc_requisitiONs.rid
JOIN hc_clients a ON a.rid = hc_requisitions.clientid
WHERE hc_users.rid in (select rid from hc_users)--(select value from dbo.split(',',@rid))
AND hc_users.locationid in (@locid)
group by hc_users.rid,hc_users.username,a.rid,a.clientname
****************************************************************************************************************
the above query is for fetching all the records....
and one more thing, i dont have values for that "Resumes" right now. so it can display 0.
****************************************************************************************************************
the same query is run when @rid and @clientid are passed. in that case a slight change will be there in FROM clause:
FROM hc_users
JOIN hc_req_resume ON hc_users.rid = hc_req_resume.createduser and hc_req_resume.createddate between (@fromdate) and (@todate)
JOIN hc_requisitions ON hc_req_Resume.reqid = hc_requisitiONs.rid
JOIN hc_clients a ON a.rid IN (select value from dbo.Split(',',@ClientID))
WHERE hc_users.rid in (select value from dbo.split(',',@rid))
AND hc_users.locationid in (@locid)
group by hc_users.rid,hc_users.username,a.rid,a.clientname
****************************************************************************************************************
This second query is working correctly.
Can anyone suggest me where i have gone wrong? 1st query is displaying only 3 rows 🙁 i must get 31 atleast....
May 13, 2010 at 1:51 am
i think the prob is with the 1st line.....
select distinct a.rid, a.username......
the DISTINCT keyword here is filtering records...
i have two user ids 1 and 4 ......
1 has only one record.... 4 has 30 records.
now if i remove distinct i am getting 31 rows. but that will not match my requirement.
the output i must get is :
rid UserName clientid ClientName openings requirements resumes
1 John 9401 SS 2 1 4
4 Admin 9399 ABC 2 1 25
4 Admin 9400 TEST 3 2 25
4 Admin 3112 XYZ 2 1 0
but i am getting only the first 3 rows with my first query....
May 13, 2010 at 2:09 am
malavika.ramanathan (5/13/2010)
now if i remove distinct i am getting 31 rows. but that will not match my requirement.
it seems that query is syntatically right but your logic is NOT as per your requirement. additionally we can help you with sample data. So better post some data.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 13, 2010 at 2:42 am
Hi Bhuvnesh,
Thanks for the reply. I will look into the code in detail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply