June 18, 2014 at 9:16 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:48 am
First I'd change the query to return the data differently and then use the tablix as a matrix to pivot the results in the report.
I'd change the query to something like this:
SELECT
d.name,
a.dealer_code,
b.last_name,
b.city,
b.state,
b.phone,
/* DateName returns January, February, etc... when used with the MONTH datepart */
DATENAME(MONTH, c.Funded_Date) AS funded_month,
YEAR(c.Funded_Date) AS funded_year,
COUNT(*) AS funded_contracts
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,
DATENAME(MONTH, c.Funded_Date),
YEAR(c.Funded_Date)
So your results will be like this:
name dealer_code last_name city state phone funded_month funded_year funded_contracts
a b c here NV 123-456-7890 January 2014 3
a b c here NV 123-456-7890 February 2014 1
Then your matrix can be used to roll up the total for YTD.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 18, 2014 at 10:59 am
Bah. This is yet another duplicate post. This one has several responses already.
http://www.sqlservercentral.com/Forums/Topic1583131-150-1.aspx
_______________________________________________________________
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 11:13 am
Sean Lange (6/18/2014)
Bah. This is yet another duplicate post. This one has several responses already.http://www.sqlservercentral.com/Forums/Topic1583131-150-1.aspx
Well that's a pisser. Especially since my answer is using a method the OP says he can't use.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 18, 2014 at 11:20 am
Jack Corbett (6/18/2014)
Sean Lange (6/18/2014)
Bah. This is yet another duplicate post. This one has several responses already.http://www.sqlservercentral.com/Forums/Topic1583131-150-1.aspx
Well that's a pisser. Especially since my answer is using a method the OP says he can't use.
Yeah, makes it tougher.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2014 at 12:18 pm
Thanks corbet, I showed the client that way as well. The thing that sucks is they don't want it like that because the format is not the way they want it. The way they want it is they want the month column not to be called month and below the month be displayed, but rather the actual month names be displayed for each column with the funded contracts underneath.
June 18, 2014 at 12:32 pm
waseemshaikh345 (6/18/2014)
Thanks corbet, I showed the client that way as well. The thing that sucks is they don't want it like that because the format is not the way they want it. The way they want it is they want the month column not to be called month and below the month be displayed, but rather the actual month names be displayed for each column with the funded contracts underneath.
But that's the way a matrix works. It takes the data from the pivot column and makes it the column header.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply