Hello, I am making a query that allows the user input a Begin Date and an End Date while choosing their company which will give them the result of their report which is suppose to include all the contracts that are funded but only that from SERVICE. So I have a table called tlkOrigDept. So the ID for "SERVICE" is 2. But when I run my query It shows an empty result set. But when I put in the Value "3" which is for 'In production' that is in tlkOrigDept Table It gives me the result. I feel like there is an error in my "Where Clause" I need all c.funded_date from SERVICE which orig_dept_id = 2.In my where clause I keep getting an empty result set. BUT when I enter orig_dept_id = 3 I get results for all c.funded_date in F&I Table tlkOrigDept orig_dept_id Orig_Dept_Name 1 Sales 2 Service 3 F&I 4 Other 5 Direct Marketing
Alter Proc spGetAdminServiceYTD(@Begin_Date DATETIME, @End_Date DATETIME, @program int=null) As Declare @year int Set @year = 2014 Declare @orig_dept_ID Int Set @orig_dept_ID = 2 Begin SELECT d.name, a.dealer_code, b.last_name, b.city, b.state, b.phone,e.orig_dept_name , COUNT(CASE WHEN MONTH(c.orig_dept_id) = 1 THEN 1 ELSE NULL END) January , COUNT(CASE WHEN MONTH(c.orig_dept_id) = 2 THEN 1 ELSE NULL END) Feburary , COUNT(CASE WHEN MONTH(c.Funded_date) = 3 THEN 1 ELSE NULL END) March , COUNT(CASE WHEN MONTH(c.Funded_date) = 4 THEN 1 ELSE NULL END) April , COUNT(CASE WHEN MONTH(c.Funded_date) = 5 THEN 1 ELSE NULL END) May , COUNT(CASE WHEN MONTH(c.Funded_date) = 6 THEN 1 ELSE NULL END) June , COUNT(CASE WHEN MONTH(c.Funded_date) = 7 THEN 1 ELSE NULL END) July , COUNT(CASE WHEN MONTH(c.Funded_date) = 8 THEN 1 ELSE NULL END) August , COUNT(CASE WHEN MONTH(c.Funded_date) = 9 THEN 1 ELSE NULL END) September , COUNT(CASE WHEN MONTH(c.Funded_date) = 10 THEN 1 ELSE NULL END) October , COUNT(CASE WHEN MONTH(c.Funded_date) = 11 THEN 1 ELSE NULL END) November , COUNT(CASE WHEN MONTH(c.Funded_date) = 12 THEN 1 ELSE NULL END) December , count(1) As YTD FROM tdealer a JOIN tContact b ON a.contact_id = b.contact_id JOIN tContract c ON a.dealer_id = c.dealer_id JOIN tCompany d ON c.company_id = d.company_id Join tlkOrigDept E ON c.orig_dept_id = e.orig_dept_id WHERE e.orig_dept_id = 3 And d.company_id = @program And c.Funded_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-5, 0) And YEAR(c.Funded_date) = @Year And c.Funded_date < DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, 0) And (c.funded_date) between @Begin_Date And @End_Date GROUP BY d.name, a.dealer_code, b.last_name, b.city, b.state, b.phone, MONTH(c.funded_date), Month(e.orig_dept_name), orig_dept_name endexec spGetAdminServiceYTD '01/01/2014', '05/30/2014', '47'
Adding another Table
Posted - 2014-06-30 : 11:32:51
