遗忘海岸

江湖程序员 -Feiph(LM战士)

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

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

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

 

 

posted on   遗忘海岸  阅读(435)  评论(2编辑  收藏  举报

点击右上角即可分享
微信分享提示