How to Reduce Column Months

Question

How to Reduce Column Months

waseemshaikh345

SSChasing Mays

Points: 600
More actions
June 12, 2014 at 10:16 am

#289969

Hello So what I am doing may be pretty easy for a lot of you. So as far how my query works is fine. I am calculating of how many funded contracts are in of each month within the year 2014. What my problem is on report builder when the user sets up the dates for @Begin_Date and @End_Date after picking their company which is @program. It shows all the months columns. The funded accounts go in the appropriate box of each month but I only want the columns of the months the user inputs within that year which is 2014.
When a user inputs @Begin_date: 01/02/2014 @End_Date 02/04/2014
I WANT ONLY January and February columns to pop up and not any other month from that point unless the user inputs that month in.
This is my Codec:
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
, 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
END
exec spGetAdminTotalYTD '01/04/2014', '05/30/2014', '47'

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63596 More actions · Answer 1

Sounds like you're using a tablix instead of a matrix, which it sounds like what you should be using. If you did this with a matrix, then the month would be the first column group. (Or you could have "OrderYear" and then "OrderMonth"). This way, the matrix handles how many columns it needs to represent the data in the dataset.

waseemshaikh345 SSChasing Mays Points: 600 More actions · Answer 2

Hey thanks a lot, Im going to try that out now. I was using a Table.

pietlinden SSC Guru Points: 63596 More actions · Answer 3

I must be getting better at this! <g>

I don't think you need most of this in your query:

SELECT d.name, a.dealer_code, b.last_name, b.city, b.state, b.phone

, 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

...

NULL END) November

, COUNT(CASE WHEN MONTH(c.Funded_date) = 12 THEN 1 ELSE

NULL END) December,

COUNT(1) AS YTD

You can probably use something like:

SELECT d.name, a.dealer_code, b.last_name, b.city, b.state, b.phone, c.Funded_date, MONTH(c.Funded_Date), YEAR(c.FundedDate)

the matrix will do the count for you.

waseemshaikh345 SSChasing Mays Points: 600 More actions · Answer 4

Thanks a lot for your input really do appreciate it. But I'm wondering because the report I am developing is very picky about the dates. So what I mean is the user can only see previous months from the present month. So to make it better terms, we are in the month of June. So we can see the past months ONLY in the year 2014. So were are in June we should be able to see Jan, Feb,Mar,Apr,May from the first day 2014 started. So when we hit August we then can see June. I need it to do that automatically so the query you edited I don't know if it will do that unless the Matrix will know? I think that's what you were saying right?

pietlinden SSC Guru Points: 63596 More actions · Answer 5

Okay, you can filter your dataset so that you can only see the current year.

SELECT <column list>

FROM <table>

WHERE YEAR([SomeDate])=YEAR(GetDate())

(Not sure that's going to perform well, but one problem at a time.)

Sean Lange SSC Guru Points: 286609 More actions · Answer 6

pietlinden (6/12/2014)
Okay, you can filter your dataset so that you can only see the current year.
SELECT <column list>
FROM <table>
WHERE YEAR([SomeDate])=YEAR(GetDate())
(Not sure that's going to perform well, but one problem at a time.)

Same concept but this is SARGable.

SELECT <column list>

FROM <table>

WHERE [SomeDate] >= dateadd(yy, datediff(yy, 0, GETDATE()), 0) --Beginning of this year

AND [SomeDate] < dateadd(yy, datediff(yy, 0, GETDATE()) + 1, 0) -- Beginning of next year

You can find many more date routines (including these) from Lynn's blog. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/%5B/url%5D

_______________________________________________________________

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/

pietlinden SSC Guru Points: 63596 More actions · Answer 7

pietlinden

SSC Guru

Points: 63596

June 12, 2014 at 1:00 pm

#1721215

Thanks, Sean... that's what I was thinking!

waseemshaikh345 SSChasing Mays Points: 600 More actions · Answer 8

So I have gone and input the date, But on the AND > on the codec is an incorrect syntax error.

WHERE '01/29/2014' >= dateadd(yy, datediff(yy, 0, GETDATE()), 0) --Beginning of this year

AND < dateadd(yy, datediff(yy, 0, GETDATE()) + 1, 0) -- Beginning

Sean Lange SSC Guru Points: 286609 More actions · Answer 9

waseemshaikh345 (6/12/2014)
So I have gone and input the date, But on the AND > on the codec is an incorrect syntax error.
WHERE '01/29/2014' >= dateadd(yy, datediff(yy, 0, GETDATE()), 0) --Beginning of this year
AND < dateadd(yy, datediff(yy, 0, GETDATE()) + 1, 0) -- Beginning

You should be using a variable instead of a hardcoded date. But look closely at what I posted. You are missing [SomeDate] in the second predicate.