September 18, 2017 at 3:30 pm
I would like to get year to date of total qty and total value fields for the current and for the prior year
Example:
Current year qty value($) average
Sept-2017 15 45000 3000
YTD Thruough Aug-2017 150 650000 4333
Prior Year qty value($) average
Sept-2016 25 90000 3600
YTD through Sept-2016 200 960000 4800
I tried to use datediff and getdate but i got zero value:
select sum(qty) as qtyTotal
from Order
where OrderDate between dateadd(yy,datediff (yy,0,getdate()), 0) and getdate()
I need help pls!!
September 18, 2017 at 4:01 pm
Please post table DDL and inserts with sample data.
September 19, 2017 at 9:40 am
Run your query without the sum, just get a field or two. Do you get data? I suspect that you haven't structured your query correctly, but if you don't provide some DDL, data, and your query, we can't help.
Make sure you are doing the datediff in the correct order as well.
September 19, 2017 at 10:31 am
I don't fully understand the req but here's some sample data and some t-sql code which might point you in the right direction.
-- sample data
declare @order table (orderDate date, [qty] int);
insert @order values ('20160102',50), ('20160515',33), ('20160822',5),
('20170206',1000), ('20170306',2000), ('20170516',5000);
-- variables
declare @year smallint = 2016, @month tinyint = 8;
-- solution (for 2016 through August 2016)
select shortMo+'-'+cast(yr as varchar(10)), sumq, avgq
from
(
select
year(orderdate),
sum(qty),
avg(qty)
from @order
where year(orderdate) = @year and month(orderdate) <= @month
group by year(orderdate)
) x(yr, sumq, avgq)
cross join
(
select shortMo = left(datename(month, '2000'+n+'01'),3)
from (values ('01'),('02'),('03'),('04'),('05'),('06'),
('07'),('08'),('09'),('10'),('11'),('12')) n(n)
where @month = n
) sm;
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply