一个统计查询写的哥要哭了

整了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