December 11, 2017 at 2:37 am
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.
December 11, 2017 at 3:43 am
Stix83 - Monday, December 11, 2017 2:37 AMHiIm 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 forthCan 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.
December 11, 2017 at 4:16 am
anthony.green - Monday, December 11, 2017 3:43 AMStix83 - Monday, December 11, 2017 2:37 AMHiIm 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 forthCan 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);
December 11, 2017 at 6:19 am
Stix83 - Monday, December 11, 2017 4:16 AManthony.green - Monday, December 11, 2017 3:43 AMStix83 - Monday, December 11, 2017 2:37 AMHiIm 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 forthCan 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
FROMAMGR_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);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply