select sum(OrderQty) as Qty,
month(StartDate) as TheMonth,
Year(StartDate) as TheYear,
(select sum(OrderQty) from WorkOrder where StartDate between dateadd(yy,-1,StartDate-day(StartDate)) and StartDate+1-day(StartDate)) as TotalQty
from WorkOrder
where StartDate between('2001-7-4') and ('2004-8-31')
group by year(StartDate),month(StartDate)
order by year(StartDate),month(StartDate) asc
month(StartDate) as TheMonth,
Year(StartDate) as TheYear,
(select sum(OrderQty) from WorkOrder where StartDate between dateadd(yy,-1,StartDate-day(StartDate)) and StartDate+1-day(StartDate)) as TotalQty
from WorkOrder
where StartDate between('2001-7-4') and ('2004-8-31')
group by year(StartDate),month(StartDate)
order by year(StartDate),month(StartDate) asc
WID PID OderQty StockQty StartDate
1 722 20000 8 2007-7-4 0:00:00
2 723 7836 15 2007-7-10 0:00:00
3 724 60000 15 2007-8-4 0:00:00
4 725 1750 15 2007-8-5 0:00:00
.
.
.
30 755 100000 15 2008-8-5 0:00:00
31 756 194270 15 2008-8-12 0:00:00
month(StartDate) as TheMonth,
Year(StartDate) as TheYear,
(select sum(OrderQty) from WorkOrder where StartDate between dateadd(yy,-1,t.StartDate-day(t.StartDate)) and t.StartDate+1-day(t.StartDate)) as TotalQty
from WorkOrder t
where StartDate between('2001-7-4') and ('2004-8-31')
group by year(StartDate),month(StartDate)
order by year(StartDate),month(StartDate) asc少了别名,加上应该对了
的一个column. 如果不用sum(),就会报错(“Subquery returned more than 1 value"). 还有其他什么方法吗?
WID PID OrderQty StockQty StartDate
1 722 20000 8 2007-7-4 0:00:00
2 723 7836 15 2007-7-10 0:00:00
3 724 60000 15 2007-8-4 0:00:00
4 725 1750 15 2007-8-5 0:00:00
.
.
.
30 755 100000 15 2008-8-5 0:00:00
31 756 194270 15 2008-8-12 0:00:00
insert into WorkOrder select 20000,8,'2007-7-4'
insert into WorkOrder select 7836,15,'2007-7-10'
insert into WorkOrder select 60000,15,'2007-8-4'
insert into WorkOrder select 1750,15,'2007-8-5'
insert into WorkOrder select 100000,15,'2008-8-5'
insert into WorkOrder select 194270,15,'2008-8-12'select theyear,themonth,isnull(sum(totalqty),0) as TotalQty
from(
select Year(StartDate) as TheYear,month(StartDate) as TheMonth,
(select sum(OrderQty) from WorkOrder where StartDate between dateadd(yy,-1,t.StartDate-day(t.StartDate)) and t.StartDate+1-day(t.StartDate))as TotalQty
from WorkOrder t
where StartDate between('2007-01-01') and ('2008-08-31'))t
group by theyear,themonth
order by theyear,themonththeyear themonth TotalQty
2007 7 0
2007 8 55672
2008 8 123500
insert @t select 1, 722, 20000 , 8 ,'2007-7-4 0:00:00'
insert @t select 2, 723, 7836 , 15,'2007-7-10 0:00:00'
insert @t select 3, 724, 60000 , 15,'2007-8-4 0:00:00'
insert @t select 4, 725, 1750 , 15,'2007-8-5 0:00:00'
insert @t select 30,755, 100000, 15,'2008-8-5 0:00:00'
insert @t select 31,756, 194270, 15,'2008-8-12 0:00:00'
select sum(OrderQty) as Qty,
month(StartDate) as TheMonth,
Year(StartDate) as TheYear,
convert(varchar(7),startdate,120)+'--'+convert(varchar(7),dateadd(mm,-1,dateadd(yy,1,startdate)),120) 时间段
from @t where convert(varchar(7),startdate,120) between convert(varchar(7),startdate,120) and convert(varchar(7),dateadd(mm,-1,dateadd(yy,1,startdate)),120)
group by month(StartDate),
Year(StartDate),
convert(varchar(7),startdate,120)+'--'+convert(varchar(7),dateadd(mm,-1,dateadd(yy,1,startdate)),120) Qty TheMonth TheYear 时间段
----------- ----------- ----------- ----------------
27836 7 2007 2007-07--2008-06
61750 8 2007 2007-08--2008-07
294270 8 2008 2008-08--2009-07
不知道什么不是这个意思?
month(StartDate) as TheMonth,
Year(StartDate) as TheYear,
???????? as TotalQty,
from Production.WorkOrder
where StartDate between('2001-7-4') and ('2004-8-31')
group by year(StartDate),month(StartDate)
order by year(StartDate),month(StartDate) asc Qty TheMonth TheYear TotalQty
27836 7 2007 0
61570 8 2007 27836
46536 9 2007 27836+61570
41624 10 2007 27836+61570+46536
91942 11 2007 27836+61570+46536+41624
71972 12 2007 27836+61570+46536+41624+91942
37614 1 2008 27836+61570+46536+41624+91942+71972
76802 2 2008 27836+61570+46536+41624+91942+71972+37614
60634 3 2008 27836+61570+46536+41624+91942+71972+37614+76802
45134 4 2008 27836+61570+46536+41624+91942+71972+37614+76802+60634
92968 5 2008 27836+61570+46536+41624+91942+71972+37614+76802+60634+45134
69230 6 2008 27836+61570+46536+41624+91942+71972+37614+76802+60634+45134+92968
137975 7 2008 27836+...69230
194270 8 2008 61570+...137975
insert into WorkOrder select 10,'2007-01-05'
insert into WorkOrder select 20,'2007-05-05'
insert into WorkOrder select 30,'2007-08-05'
insert into WorkOrder select 40,'2007-09-05'
insert into WorkOrder select 50,'2007-12-05'
insert into WorkOrder select 60,'2008-06-05'
insert into WorkOrder select 70,'2008-07-05'
insert into WorkOrder select 80,'2008-08-05'
select theyear,themonth,isnull(sum(totalqty),0) as TotalQty
from(
select Year(StartDate) as TheYear,month(StartDate) as TheMonth,
(select sum(OrderQty) from WorkOrder where StartDate between dateadd(yy,-1,t.StartDate-day(t.StartDate)) and t.StartDate+1-day(t.StartDate))
as TotalQty
from WorkOrder t
where StartDate between('2007-01-01') and ('2008-08-31'))t
group by theyear,themonth
order by theyear,themonth
theyear themonth TotalQty
2007 1 0
2007 5 10
2007 8 30
2007 9 60
2007 12 100
2008 6 120
2008 7 180
2008 8 250你看这是不是按你的时间要求累加的,如果是,修改一下就行了