June 16, 2014 at 12:23 pm
So my query is suppose to get all the funded contracts of how many there are of each month in the year 2014 of which the user inputs. The problem I am having is the 'Year to date" I cant seem to get the function working with the "Count" function. Here is my Stored proc.
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)'Funded Contracts'
, month(c.Funded_date) 'Month_ID'
, datename(month, dateadd(month, month(c.funded_date), - 1)) [Month]
, 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
Order By Month_ID
end
exec spGetAdminTotalYTD '01/04/2014', '05/30/2014', '47'
June 16, 2014 at 12:52 pm
Which version of SQL Server are you on?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 16, 2014 at 1:04 pm
I am using SQL server 2008R2
June 16, 2014 at 1:21 pm
waseemshaikh345 (6/16/2014)
I am using SQL server 2008R2
OK, so TSQL windowing functions are out of the picture unfortunately.
Although still perfectly possible in TSQL, you can calculate a YTD in SSRS itself:
Cumulative Aggregates in SQL Server Reporting Services[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply