June 18, 2014 at 10:34 am
--I Am Using SQL Server 2008 R2 And Report Builder 3.0--
So what I am trying to do is, I have a report that generates of how many Funded contracts are funded within the year 2014 of each month and in the end of the year its suppose to total up the funded contracts of all the months a column called "Year to Date"
My problem is not that bad, So when the user enters the dates of their choosing it shows how many funded contracts within the month they chose. So if they input @Begin_date 01/02/2014 and @End_date 03/02/2014.
*THEY SHOULD ONLY SEE THOSE 3 MONTHS IN RANGE*
So my query does give the user the funded contracts within their months EXCEPT it shows all the months even though my query doesn't show the funded contracts in other months they haven't chosen.
In simpler terms I don't want this output when User enters their credentials:
@begin_Date 01/01/2014 @End_date 02/01/2014
Jan Feb Mar May Jun Jul Aug Sep Oct Nov Dec YTD
1 1 0 0 0 0 0 0 0 0 0 2
0 1 0 0 0 0 0 0 0 0 0 1
1 0 0 0 0 0 0 0 0 0 0 1
I want this for the user output when they enter their credentials
@begin_Date 01/01/2014 @End_date 02/01/2014
Jan Feb YTD
1 1 2
0 1 1
1 0 1
This is my Query
Alter Proc spGetAdminTotalYTD
(@Begin_Date DATETIME,
@End_Date DATETIME,
@program int=null) As
Declare @year int
Set @year = 2014
Begin
SELECT d.name, a.dealer_code, b.last_name, b.city, b.state, b.phone,
c.Funded_date, MONTH(c.Funded_Date), YEAR(c.Funded_Date)
, 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,
c.funded_date
end
exec spGetAdminTotalYTD '01/04/2014', '05/30/2014', '47'
June 18, 2014 at 10:45 am
duplicate post. Direct replies here. http://www.sqlservercentral.com/Forums/ShwMessage.aspx?TopicID=1583131&ForumID=150&PageIndex=1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2014 at 10:47 am
Removed as per previous post :doze:
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply