Hi
Im trying to retrive total revenue last day of previous month, i can get all the months totals, however i cant get my head around only retrieving the total revenue for last day of previous month.
This will be my opening balance
I have this so far:
SELECT month(create_date) AS month_name,
SUM(Forecast_Revenue) AS sum_of_monthFROM AMGR_Opportunity
WHERE YEAR(create_date) = YEAR(GetDate())
GROUP BY month(create_date);
Result is:
Month 1 - R500000
Month 2 - R809090
and so forth
Can someone assist me in only getting the total revenue on the last day of the previous month.
Working from Lynn's common date routines[/url], you can get the beginning of the current month and minus one 1 day
select dateadd(day, -1, dateadd(mm, datediff(mm, 0, @ThisDate), 0) )
That will give you the last day of the previous month, then you need to do the same as your already doing but use that as the where clause instead.
I am not following, can you please post how i must put the above in my where query
FROM
AMGR_Opportunity
WHERE dateadd(day, -1, dateadd(mm, datediff(mm, 0, @ThisDate), 0) )
GROUP BY month(create_date);
SELECT
CONVERT(DATE,create_date) AS LastDayOfPreviousMonth
,SUM(Forecast_Revenue) AS SumForcastRevenueForLastDayOfPreviousMonth
FROM AMGR_Opportunity
WHERE create_date >= dateadd(day, -1, dateadd(mm, datediff(mm, 0, GETDATE()), 0) ) AND create_date < dateadd(mm, datediff(mm, 0, GETDATE()), 0)
GROUP BY CONVERT(DATE,create_date);