June 26, 2014 at 8:46 am
Hello everyone, So what I am asking might be fairly obvious for some of you so bear with me since I only have 5 months exp. in SQL.
So I have this query that generates all funded contracts of every month only for the year 2014. So that is done, the problem now is I have to add another table called tlkOrigDept. That is because I have to generate all the funded contracts that are in SERVICE which is in the tlkorigDept table.
So I have to generate all Funded contracts in that are in SERVICE which is in the tlkorigDept table. Here is my From and Where Clause. If you need more snippet of my codec I will be glad to provide more detail.
ALTER Proc [dbo].[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.Funded_date) = 1 THEN 1 ELSE NULL
END) January
, COUNT(CASE WHEN MONTH(c.Funded_date) = 2 THEN 1 ELSE NULL
END) Feburary
, 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 e.orig_dept_id = c.orig_dept_id
WHERE 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),
e.orig_dept_name
end
Exec spGetAdminServiceYTD '01/01/2014', '05/30/2014', '64'
June 26, 2014 at 8:56 am
I'm not sure what the question is. It seems like you have added the table are you not getting the results that you expect? If not, what are you getting vs. what do you want. In order to help with this problem it would be nice to have the DDL and some sample data. Please read the article in my signature o help post the necessary information.
Edit: You still have some funky date logic too.
June 26, 2014 at 9:02 am
To better explain the case statement calculates funded contracts from the tcontract table. So I want all the funded contracts that are in Service which is in the tlkorigdept table. the dept_Id in that table is 1. So the problem when I run the query its just giving me only the total funded contracts and not the funded contracts that are in Service. If you need more details I will explain better and try to give a visual.
June 26, 2014 at 9:08 am
You need to put relevant where clause for "all the funded contracts that are in Service which is in the tlkorigdept table".
June 26, 2014 at 9:14 am
Oh okay, so for the Where Clause do you know how to integrate the orig_dept_id with the c.funded_Date? This is a little confusing for me because I already added the Get Date along with the parameters.
WHERE 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
June 26, 2014 at 9:22 am
Why you want to integrate the orig_dept_id with the c.funded_Date?
Cant you do something like below?
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.Funded_date) = 1 THEN 1 ELSE NULL
END) January
, COUNT(CASE WHEN MONTH(c.Funded_date) = 2 THEN 1 ELSE NULL
END) Feburary
, 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 e.orig_dept_id = c.orig_dept_id
WHERE d.company_id = @program and e.orig_dept_id = @orig_dept_id
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
June 26, 2014 at 9:46 am
The reason is because I want all Funded contracts that are IN SERVICE. Not all funded contracts in total. Only the ones in service I want. inside the tlkOrigDept shows orig_dept _ID the ID number for service is 2. So I need to integrate the Funded contracts with the Orig_dept_ID which is equal to 2 And should give the total for all funded contracts for SERVICE in total.
If I don't put the origDeptID it just calculates all of the funded contracts no matter if they are in service or not. That's why I need the table to be integrated with. This is my entire query.
Alter Proc spGetAdminTotalYTD
(@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,orig_dept_id
, COUNT(CASE WHEN MONTH(c.Funded_date) = 1 THEN 1 ELSE NULL END) January
, COUNT(CASE WHEN MONTH(c.Funded_date) = 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
WHERE 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)
end
exec spGetAdminTotalYTD '01/01/2014', '05/30/2014', '47'
June 26, 2014 at 9:48 am
Please, Please, Please add the code to create the tables and some sample data along with the desired output. We are wasting time trying to guess what you want and not being able to verify it. If you provide the tables and data this will go a lot faster. 🙂
June 26, 2014 at 10:08 am
CREATE TABLE [dbo].[tlkOrigDept]
(
[orig_dept_id] [int] IDENTITY(1,1) NOT NULL,
[orig_dept_name] [varchar](50) NOT NULL,
[created_user_id] [int] NOT NULL,
[created_date] [datetime] NOT NULL,
[abbreviation] [varchar](2) NULL,
CONSTRAINT [PK_tlkOrigDept] PRIMARY KEY CLUSTERED
Table tlkOrigDept
orig_dept_id Orig_Dept_Name
1 Sales
2 Service
3 F&I
4 Other
5 Direct Marketing
Okay now lets say the user Selects '@Program =37'
So the parameter chosen 37 shows the funded contract and lets say the Orig_Dept_ID is 2. So I want that to show in the output that name of the program that it is counted as 1 funded contract for SERVICE.
Lets say the user selects '@program=21'
So this parameter chosen 21 shows the funded contract BUT this time the orig_Dept_ID is 4 which is "Other" which I don't want. So The user should see a 0 or Null if the user choses this Program 21.
So the overall view is I want all Funded contracts in SERVICE not any other dept_ID and totaling how many contracts are in Service which as you can see is already done in my Where clause. I just have to figure out where to plug in the table tlkOrigDept and the Orig_dept_ID of where it should be placed in my where clause of if there is an easier way.
June 26, 2014 at 10:57 am
Have you tried this? Is it not working?
e.orig_dept_id = @orig_dept_id where @ @orig_dept_id = 2 this is supposed to what you are trying to do..
June 26, 2014 at 11:14 am
I cant incorporate that extra 'where' in my where clause
June 26, 2014 at 11:20 am
what about AND?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply