一个统计查询写的哥要哭了
整了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 |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步