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.
| Author |
Topic |
|
mrm23
Posting Yak Master
198 Posts |
 Posted - 2010-05-13 : 03:42:31
|
| 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)ASBEGINIF @rid = 0 BEGINIF @ClientID = 0 BEGINSELECT 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.ridJOIN hc_users ON hc_users.rid = hc_req_team.teamuseridJOIN 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_teamJOIN hc_requisitiONs ON hc_req_team.reqid = hc_requisitiONs.ridJOIN hc_users ON hc_users.rid = hc_req_team.teamuseridJOIN 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.ridJOIN hc_requisitiONs ON hc_req_resume.reqid = hc_requisitiONs.ridJOIN hc_clients b ON hc_requisitiONs.clientid = b.rid WHERE a.rid = b.ridAND hc_req_resume.CreatedDate between (@fromdate) AND (@todate)) AS 'Resumes'FROM hc_usersJOIN 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_usersJOIN 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.... |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-05-13 : 03:52:35
|
| 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 resumes1 John 9401 SS 2 1 4 4 Admin 9399 ABC 2 1 254 Admin 9400 TEST 3 2 254 Admin 3112 XYZ 2 1 0 but i am getting only the first 3 rows with my first query.... |
 |
|
|
|
|
|
|
|
Query Error