December 11, 2017 at 10:32 am
I created a report in ssrs with start and end date parameters that pull summary pipeline for certain types of deals.
I just want to find out how to retrieve total sales for the previous month.
For example, if i pull the report for December. The opening balance must pull total sales for November. This will be based on its own query.
I can get all months but i don't know how to get previous month in the where condition and to include my parameters when the report runs.
Here is my query that gets all months:
SELECT month(create_date) AS month_name,
SUM(Forecast_Revenue) AS sum_of_month
FROM AMGR_Opportunity
WHERE YEAR(create_date) = YEAR(GetDate())
GROUP BY month(create_date)
My report parameters are @StartDate and @EndDate. If selecting 01.12.2017 as startdate to 31.12.2017 as enddate the report pulls the corresponding data needed.
2 questions:
How do i incorporate last months sales in a separate query based on my coding above?
How to incorporate the parameters? If needed.
I have been on all forums today and cannot get what i need. Time to call in the sql experts so hence i am here.
Please help!!
December 11, 2017 at 11:32 am
To get the sum of the forecast_revenue for the previous month based on the start date, you could do something like:SELECT SUM(Forecast_Revenue)
FROM AMGR_Opportunity
WHERE create_date < DATEADD(mm, DATEDIFF(mm, 0, @StartDate), 0)
AND create_date >= DATEADD(mm, DATEDIFF(mm, 0, @StartDate) - 1, 0)
Sue
December 11, 2017 at 12:11 pm
Thanks Sue. it works perfectly.
If i now want to calculate the closing balance, that will be the opening balance + all in progress deals done in current month.. Can that be done?
How would the query change for that?
December 12, 2017 at 12:39 am
I figured it out thanks - with a union all clause
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply