var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Calculating sales for the previous 12 months
I'm trying to figure out a query that will give me the sum of qty_sold for the previous (last) 12 months. My table consist of part_code, qty_sold, and sale_date. Can someone please help me with the query?
where saledate >= dateadd('Month', -12, getdate())
thx blind man. I noticed that stament subtracts the month but keeps the day of year. is there a way to exclude the current month.
I came up with this statement but it is returning the message: incorrect sytax near ')'. Do you know what I am doing wrong?
SELECT sale_date FROM sumsales where
(sale_date BETWEEN CONVERT(varchar,
DATEADD(Month, - 12, DATEADD(Day, - (1 * (DAY(GETDATE()) - 1)), GETDATE())), 101) AND CONVERT(varchar, DATEADD(Day, - 1,
DATEADD(Day, - (1 * (DAY(GETDATE()) - 1)), GETDATE())), 101))) AS prev_12_sls)
Looks overly complicate to me, so you are probably just missing a parenthesis somewhere.
What, for instance, is this supposed to accomplish:
1 * (DAY(GETDATE()) - 1)
It's been a while since 6th grade math class, but as I recall anything multiplied by one is itself.
Give me a few sample dates and the results you would like for each, and I'll have a better idea of what you are trying to accomplish.
aperales10, try this for calculating the start month (day 1) and end month (day last day)
First day of month 12 months from last day of previous month:
DATEADD(M, -12, DATEADD(D, -(DAY(date-col) -1), date-col) )
and last day of previous month:
DATEADD(D, -DAY(date-col), date-col)
Today's date: 2009-05-18
Last day prev month: 2009-05-18 - 18 days = 2009-04-30
First day 12 months from previous end of month: (2009-05-18 - (18 - 1) ) - 12 months = 2008-05-01