日期:2014-05-17 浏览次数:20557 次
0.60 1.20
0.90 -0.60
3.60 6.60
9.60 25.80
9.60 45.00
1.20
-0.60
25.80
45.00
45.00
declare @PID int,@PNE nvarchar(64);
set @pid=1;set @PNE='钢管'
declare @totollength decimal(18,2),@category varchar(64);
select @category=(case @PNE when '钢管' then 'Steel' when '扣件' then 'Fastening' when '套管' then 'CasingPipe' end);
with t as (
select Row_Number() OVER ( ORDER by orderdate ASC) rank, a.ID,a.ProjectID,a.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate,sum(a.SubTotal) as SubTotal from (
select a.ID,a.ProjectID,b.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate,a.SubTotal from (
select a.ID,b.ProductName,a.ProjectID,a.OrderDirection,a.OrderDate,a.SubTotal from (
select a.ID,a.ProjectID,b.StandardID,a.OrderDirection,a.OrderDate,b.SubTotal from SteelBusinessOrder a
join (select StandardID,sum(SubTotal) as SubTotal,OrderID from SteelBusinessList group by StandardID,OrderID) b
on a.ID = b.OrderID
) a
join SteelStandard b
on a.StandardID = b.ID
) a
join SteelProject b
on a.ProjectID = b.ID and b.ID = @PID
) a group by a.ID,a.ProjectID,a.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate
)
,ts as (
select rank,id,projectid,projectName,productname,orderdirection,orderdate,subtotal,uday,surplus,uday * surplus as counts from (
select rank,id,projectid,projectName,productname,orderdirection,orderdate,subtotal,
(select DATEDIFF(d,(select OrderDate from t a where a.rank = b.rank )
,case when((select min(OrderDate) from t a where a.rank > b.rank and a.OrderDate<>b.OrderDate and a.productname=b.productname) is null) then getdate()
else (select min(OrderDate) from t a where a.rank>b.rank and a.OrderDate<>b.OrderDate and a.productname=b.productname) end)) as uday
,
isnull((select sum(isnull(SubTotal,0)) from t a where a.rank <= b.rank and OrderDirection = 0 and a.productname=b.productname),0)
-
isnull((select sum(isnull(SubTotal,0)) from t a where a.rank <= b.rank and OrderDirection = 1 and a.productname=b.productname),0)
as surplus
from t as b
) a
)
select counts from ts where productname='钢管'