日期:2014-05-17 浏览次数:20498 次
select YEAR(ShipDate) AS NN,MONTH(ShipDate) AS YY,
SUM(coShipDetail.Amount*(case coOrder.CyNo when 100 then 1 when 101 then 0.8145 when 102 then 6.335 end))/10000 as AMT
from coShip
INNER JOIN coShipDetail ON coShip.ShipNo=coShipDetail.ShipNo
INNER JOIN coOrderDetail ON coOrderDetail.DetailNo=coShipDetail.COrderDetailNo
INNER JOIN coOrder ON coOrder.OrderNo=coOrderDetail.OrderNo
where YEAR(ShipDate) in (2011,2012,2013)
Group by YEAR(ShipDate) ,MONTH(ShipDate)
select a.*,b.AMT as [上个月]
from
(
select
YEAR(ShipDate) AS NN,MONTH(ShipDate) AS YY,
SUM(coShipDetail.Amount*(case coOrder.CyNo
when 100 then 1 when 101 then 0.8145 when 102 then 6.335 end))/10000 as AMT
from coShip
INNER JOIN coShipDetail ON coShip.ShipNo=coShipDetail.ShipNo
INNER JOIN coOrderDetail ON coOrderDetail.DetailNo=coShipDetail.COrderDetailNo
INNER JOIN coOrder ON coOrder.OrderNo=coOrderDetail.OrderNo
where YEAR(ShipDate) in (2011,2012,2013)
Group by YEAR(ShipDate) ,MONTH(ShipDate)
) a left join
(
select
YEAR(DATEADD(mm,1,ShipDate)) AS NN,MONTH(DATEADD(mm,1,ShipDate)) AS YY,
SUM(coShipDetail.Amount*(case coOrder.CyNo
when 100 then 1 when 101 then 0.8145 when 102 then 6.335 end))/10000 as AMT
from coShip
INNER JOIN coShipDetail ON coShip.ShipNo=coShipDetail.ShipNo
INNER JOIN coOrderDetail ON coOrderDetail.DetailNo=coShipDetail.COrderDetailNo
INNER JOIN coOrder ON coOrder.OrderNo=coOrderDetail.OrderNo
where YEAR(ShipDate) in (2011,2012,2013)
Group by YEAR(DATEADD(mm,1,ShipDate)) ,MONTH(DATEADD(mm,1,ShipDate))
) b
on a.NN=b.NN and a.YY=b.YY
select a.NN,a.YY,a.AMT,b.AMT
from
(
select row_number() over(order by YEAR(ShipDate),MONTH(ShipDate)) as rownum,