help with sql query.
-
Hi, I am writing a query that returns me the various figures all the salesperons: My query goes like this : Select Usr.user_name as SalesPerson, isnull(ReqNo,0) ReqNo,isnull(ResSubToSalesPerNo,0) ResSubToSalesPerNo, isnull(ResSubToClientNo,0) ResSubToClientNo,isnull(InterviewNo,0) InterviewNo, isnull(ConfirmNo,0) ConfirmNo,isnull(RejectionNo,0) RejectionNo from xr_user Usr left Join (select sales_person_id,count(requisition_date) ReqNo from xr_sales_reqs where requisition_date between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by sales_person_id) Reg on (Usr.User_Id = Reg.sales_person_id) left join (select A.sales_person_id,count(date_sub_sales_per) ResSubToSalesPerNo from xr_sales_reqs A left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_sub_sales_per between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl1 on (Usr.User_ID = cl1.sales_person_id) left join (select A.sales_person_id,count(date_sub_client) ResSubToClientNo from xr_sales_reqs A Left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_sub_client between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl2 on (Usr.User_ID = cl2.sales_person_id) left join (select A.sales_person_id,count(date_interview) InterviewNo from xr_sales_reqs A Left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_interview between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl3 on (Usr.User_ID = cl3.sales_person_id) left join (select A.sales_person_id,count(date_of_start_entry) ConfirmNo from xr_sales_reqs A Left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_of_start_entry between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl4 on (Usr.User_ID = cl4.sales_person_id) left join (select A.sales_person_id,count(date_of_rejection) RejectionNo from xr_sales_reqs A Left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_of_rejection between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl5 on (Usr.User_ID = cl5.sales_person_id) Where isnull(ReqNo,0) > 0 or isnull(ResSubToSalesPerNo,0) > 0 or isnull(ResSubToClientNo,0) > 0 or isnull(InterviewNo,0) > 0 or isnull(ConfirmNo,0) >