整了2个小时：
--列出存在的线束
with WiringHarness(PartNo,CarType,PartName,Setting)
as
(
select tp.PartNo,tp.CarType,pp.p_setting Setting,pp.p_partname PartName from
(
select p_partno PartNo,p_yw carType from p_partno group by p_partno,p_yw
Union
select partno PartNo, p_yw carType from inticket where p_bs = '1' group by partno,p_yw
Union
select partno PartNo,p_yw carType from outticket where p_bs = '1' group by partno,p_yw
)as tp
left join p_Partno pp ON
tp.PartNo=pp.p_partno
And
tp.CarType=pp.p_yw
),--计算期初库存
WHBeforeInventory(PartNo,CarType,PartName,Setting,BeforeQty)
as
(
select *,
(
isnull((
select sum(Inqty) from inticket
where ticketstatus = '关闭' and p_bs = '1'
And WH.PartNo=inticket.PartNo And WH.CarType=inticket.p_yw
And InDate < '2011-10-10'
),0)
- --这里有个减号!!
isnull((
Select sum(isnull(A.OutQty,0)) from OutticketList A
left join outticket B
ON A.partno = B.partno and A.subid = B.subid and A.outticketid = B.outticketid
where A.partno =WH.PartNo
and A.Status = '完成'
and ltrim(rtrim(B.p_yw)) =WH.CarType and B.p_bs = '1' and B.OutDate <'2011-10-10'
),0)
)as BeforeQty
from WiringHarness WH
), --指定时间段内的入库统计
WHIn (PartNo,CarType,SumProductionIn,SumRepairIn,SumReturnIn,SumOtherIn)
as
(
Select
PartNo,p_yw CarType,
Sum(ProductionIn) SumProductionIn,
Sum(RepairIn) SumRepairIn,
Sum(ReturnIn) SumReturnIn,
Sum(OtherIn) SumOtherIn
from
(
Select
Case When Tickettype='生产缴库' Then isnull(InQty,0)
Else 0 End as ProductionIn,
Case When Tickettype='返修入库' Then isnull(InQty,0)
Else 0 End as RepairIn,
Case When Tickettype='退货入库' Then isnull(InQty,0)
Else 0 End as ReturnIn ,
Case When Tickettype='其它入库' Then isnull(InQty,0)
Else 0 End as OtherIn,
partNo,p_yw
From InTicket
where TicketStatus = '关闭' and p_bs = '1'
and InDate >='2011-10-10' and InDate <='2011-11-3 23:59:59'
) as t01
group by partno, p_yw
), --指定时间段内的出库统计
WHOut(PartNo,CarType,SumRepairOut,SumTransferOut,SumOutPlanOut,SumOtherOut,SumRetailOut)
as
(
Select
PartNo,CarType,
Sum(RepairOut) SumRepairOut,
Sum(TransferOut) SumTransferOut,
Sum(OutPlanOut) SumOutPlanOut,
Sum(OtherOut) SumOtherOut,
Sum(RetailOut) SumRetailOut
From
(
Select
B.partNo,B.p_yw as CarType,
Case When Ticktype='返修出库' Then isnull(A.OutQty,0)
Else 0 End as RepairOut,
Case When Ticktype='转移出库' Then isnull(A.OutQty,0)
Else 0 End as TransferOut,
Case When Ticktype='计划外出库' Then isnull(A.OutQty,0)
Else 0 End as OutPlanOut ,
Case When Ticktype='其它出库' Then isnull(A.OutQty,0)
Else 0 End as OtherOut,
Case When Ticktype='个体零售' Then isnull(A.OutQty,0)
Else 0 End as RetailOut
From OutticketList A
left join outticket B
ON A.partno = B.partno and A.subid = B.subid and A.outticketid = B.outticketid
where A.Status = '完成' and B.p_bs = '1'
and OutDate >='2011-10-10' and OutDate <='2011-11-3 23:59:59'
) as t02
group by PartNo,CarType
)
select
*,
BeforeQty+TotalIn-TotalOut as Inventory
from(
select
WHB.*,
SumProductionIn,SumRepairIn,SumReturnIn,SumOtherIn,
SumRepairOut,SumTransferOut,SumOutPlanOut,SumOtherOut,SumRetailOut,
(isnull(SumProductionIn,0)+isnull(SumRepairIn,0)+isnull(SumReturnIn,0)+isnull(SumOtherIn,0)) as TotalIn ,
(isnull(SumRepairOut,0)+isnull(SumTransferOut,0)+isnull(SumOutPlanOut,0)+isnull(SumOtherOut,0)+isnull(SumRetailOut,0)) as TotalOut
from
WHBeforeInventory WHB
Left Join WHIn ON WHB.PartNo=WHIn.PartNO And WHB.CarType=WHIn.CarType
Left Join WHOut On WHB.PartNo=WHOut.PartNo And WHB.CarType=WHOut.CarType
) as t001