Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieving Specific Table Help. Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 1:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 8:34 AM
Points: 14, Visits: 32
Okay so I have made a report calculating how many contracts are funded in each month within the year 2014.

So Now I have to calculate the total for all the contracts that are in SERVICE ONLY.

What I mean by this is I have a table called tlkOrigDept. Within that table I have this

 

Table tlkOrigDept

orig_dept_id Orig_Dept_Name
1 Sales
2 Service
3 F&I
4 Other
5 Direct Marketing

So I have to get all the funded contracts ONLY that from
 SERVICE 

which is
 'orig_dept_id' = 2

So this is my Query but the problem I see is in my where clause. Because when I change the orig_dept_Id to 3 it works but not for 2. It just shows blank and not an error message.

The user inputs a @Begin_date and @End_Date the User than picks a company which is @Program. The user should see ALL the FUNDED Contracts for each month that are from SERVICE ONLY.


I either see a problem in the SELECT Statement or my WHERE Clause

Here is my 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 c.orig_dept_id = 2 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),
e.orig_dept_name
end



exec spGetAdminServiceYTD '01/01/2014', '05/30/2014', '47'





Post #1586634
Posted Thursday, June 26, 2014 1:31 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 583, Visits: 875
Please don't post the same question in multiple forums. Here is a link to the original http://www.sqlservercentral.com/Forums/Topic1586485-391-2.aspx#bm1586578



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1586638
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse