set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Expensedetails]
        @PID int,
        @PNE nvarchar(64)
as
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 and b.ProductName = @PNE
) 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 *,uday * surplus as counts from (
select  *,
(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 ) is null) then dateadd(d,1,getdate())
else (select  min(OrderDate) from t a where a.rank>b.rank  ) end)) as uday
,
isnull(
isnull((select sum(isnull(SubTotal,0)) from t a where a.rank <= b.rank and OrderDirection = 0),0)
-
isnull((select sum(isnull(SubTotal,0)) from t a where a.rank <= b.rank and OrderDirection = 1),0)
,0)
as surplus
from t as b
) a
)
select convert(char(10),orderdate,120) as orderdate,productName,(case orderdirection when 0 then '出库' when 1 then '入库' end) as orderdirection,subtotal,surplus,uday,counts,(select SUM(counts) as zsum from ts a where a.rank < = b.rank) as zsum from ts b