SQL: last month total

Alex A
Alex A used Ask the Experts™
on
Please help with writing a query to get aggregated OrderAmount for each SalesPerson for the last month (last 28-31 days). For example, if today is 09/13/2018, we need sum from 08/13/2018 to 09/12/2018.

TableOrders
----------------
SalesPerson
OrderAmount
OrderDate


Thank you in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
something like this:

select salesperson, sum(salesamount)
from tablesales
where date > dateadd(month, -1, getdate()
group by salesperson

Open in new window

Most Valuable Expert 2015

Commented:
(no points..)

I would just add a cutoff to Éric Moreau's query to exclude the current date. Here's Eric's query with the cutoff

select salesperson, sum(salesamount)
from tablesales
where date > dateadd(month, -1, getdate()
and      date < CONVERT(date, getDate())
group by salesperson
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
good catch
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
And dont forget the closing brackets.....
select salesperson, sum(salesamount)
from tablesales
where [date] >= CONVERT(date,dateadd(month, -1, getdate()))
and   [date] < CONVERT(date, getDate())
group by salesperson

Open in new window

Using getdate() as a substitution for your actual date parameter by way of example. Using convert(date,<parameter>) to avoid the time component of a datetime / datetime2

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial