June 30, 2014 at 8:00 am
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"
This is my Where Clause
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
And this is the Full Query:
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
end
exec spGetAdminServiceYTD '01/01/2014', '05/30/2014', '47'
June 30, 2014 at 8:11 am
You have posted 4 or 5 different times about this query. If you want it to work correctly please give us the all the create table statements (for all tables involved) and sample data to work with which will show the issue you are having plus we need the desired output. I can't see your data, so I have no idea. If it returns values for one set of parameters, but not another it sounds like an issue with the data and not the where clause. Also I have responded about your date logic before, but I see you haven't changed it. If you only want to search on the two parameters passed in why are you also doing additional checks? Do the values passed in need to be confined to a certain range?
Please provide the necessary information and ask the question one time rather then 4 or 5 different post about each little area of the query.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply