• 金蝶云星空SQL 84.89 kB 2024-06-03 22:06
  • Position: 金蝶云星空SQL / 物料综合查询(年出货量、采购量统计)[星空].sql

    declare @begdate date,@enddate date
    set @begdate='2023-02-01'
    set @enddate='2025-01-01'
    
    
    
    select 
    m.FMATERIALID as FID
    ,org.FNUMBER ʹ����֯����
    ,orgl.FNAME  ʹ����֯
    ,m.FNUMBER ��ϱ���
    ,ml.[FNAME] ������
    ,ml.[FSPECIFICATION] ����ͺ�
    ,mgl.FNAME ��Ϸ���
    
    ,(
    	select sum(FBaseQty)
    	from  T_STK_INVENTORY ti --��ʱ���
    	 left join T_BD_MATERIAL tm on tm.FMATERIALID=ti.FMATERIALID --��ϱ�ȡ��ϱ���
    	 left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ti.FSTOCKORGID --�����֯
    	 where tm.FNUMBER=m.FNUMBER --��ϱ���һ��
    	 and torg.FNUMBER=org.FNUMBER--�����֯һ
    ) as ��ʱ��� --��ϱ���,��֯����һ��
    
    ,(	
    	select top 1 ouef.FCOSTPRICE
    	from T_SAL_OUTSTOCK ou
    	left join T_SAL_OUTSTOCKENTRY oue on ou.FID=oue.FID
    	left join T_SAL_OUTSTOCKENTRY_F ouef on oue.FENTRYID=ouef.FENTRYID--����
    	left join T_BD_MATERIAL tm on tm.FMATERIALID=oue.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ou.FSTOCKORGID --������֯
    	where ou.FDOCUMENTSTATUS ='C'
    	and ou.FCANCELSTATUS='A'
    	and tm.FNUMBER=m.FNUMBER
    	and torg.FNUMBER=org.FNUMBER
    	and ouef.FCOSTPRICE<>0
    	and ou.FDATE>=@begdate
    	and ou.FDATE<=@enddate	
    	order by ou.FDATE desc
    
    ) as �ɱ����� 
    
    ,(
    	select top 1 
    	poif.FTAXPRICE
    	--poie.F_GOLD_DECIMAL
    	from t_STK_InStock poi  --�ɹ���ⵥ
    	left join T_STK_INSTOCKENTRY poie on poi.fid=poie.FID ---�ɹ������ϸ
    	left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
    	left join T_BD_MATERIAL tm on tm.FMATERIALID=poie.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS torg on torg.FORGID=poi.FPURCHASEORGID --��֯���ɹ���֯
    	where
    	tm.FNUMBER=m.FNUMBER
    	and torg.FNUMBER=org.FNUMBER
    	and poi.FDOCUMENTSTATUS = 'C'--�����
    	and poi.FCANCELSTATUS<> 'B' --δȡ�
    	--and tm.FNUMBER <>'ZPY00000004'
    	--and po.FDATE between @begdate and @enddat
    	order by poi.FDATE desc
    )  ���ɹ���
    
    
    ,CONVERT(varchar(230),(
    	select top 1 
    	poi.FDATE
    	from t_STK_InStock poi  --�ɹ���ⵥ
    	left join T_STK_INSTOCKENTRY poie on poi.fid=poie.FID ---�ɹ������ϸ
    	--left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
    	left join T_BD_MATERIAL tm on tm.FMATERIALID=poie.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS torg on torg.FORGID=poi.FPURCHASEORGID --��֯���ɹ���֯
    	where
    	tm.FNUMBER=m.FNUMBER
    	and torg.FNUMBER=org.FNUMBER
    	and poi.FDOCUMENTSTATUS = 'C'--�����
    	and poi.FCANCELSTATUS<> 'B' --δȡ�
    	order by poi.FDATE desc
    ), 23)   ���ɹ��������
    
    
    
    ,DATEDIFF(dd,
    (
    	select top 1 
    	poi.FDATE
    	from t_STK_InStock poi  --�ɹ���ⵥ
    	left join T_STK_INSTOCKENTRY poie on poi.fid=poie.FID ---�ɹ������ϸ
    	--left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
    	left join T_BD_MATERIAL tm on tm.FMATERIALID=poie.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS torg on torg.FORGID=poi.FPURCHASEORGID --��֯���ɹ���֯
    	where
    	tm.FNUMBER=m.FNUMBER
    	and torg.FNUMBER=org.FNUMBER
    	and poi.FDOCUMENTSTATUS = 'C'--�����
    	and poi.FCANCELSTATUS<> 'B' --δȡ�
    	order by poi.FDATE desc
    )
    ,GETDATE())  δ�ɹ����
    
    
    ,CONVERT(varchar(230),(
    	select top 1 
    	sout.FDATE
    	from T_SAL_OUTSTOCK sout  --��۳���
    	left join T_SAL_OUTSTOCKENTRY soute on sout.fid=soute.FID ---�ϸ
    	--left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
    	left join T_BD_MATERIAL tm on tm.FMATERIALID=soute.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS torg on torg.FORGID=sout.FStockOrgId --��֯��������֯
    	where
    	tm.FNUMBER=m.FNUMBER
    	and torg.FNUMBER=org.FNUMBER
    	and sout.FDOCUMENTSTATUS = 'C'--�����
    	and sout.FCANCELSTATUS<> 'B' --δȡ�
    	order by sout.FDATE desc
    ), 23)   ����۳�������
    
    ,DATEDIFF(dd,
    (
    	select top 1 
    	sout.FDATE
    	from T_SAL_OUTSTOCK sout  --��۳���
    	left join T_SAL_OUTSTOCKENTRY soute on sout.fid=soute.FID ---�ϸ
    	--left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
    	left join T_BD_MATERIAL tm on tm.FMATERIALID=soute.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS torg on torg.FORGID=sout.FStockOrgId --��֯��������֯
    	where
    	tm.FNUMBER=m.FNUMBER
    	and torg.FNUMBER=org.FNUMBER
    	and sout.FDOCUMENTSTATUS = 'C'--�����
    	and sout.FCANCELSTATUS<> 'B' --δȡ�
    	order by sout.FDATE desc
    )
    ,GETDATE())  ������
    
    
    ,(
    	select sum(FQTY)
    	from t_PUR_POOrder po  --�ɹ�����
    	left join t_PUR_POOrderEntry poe on po.fid=poe.FID ---�ɹ������ϸ
    	left join T_BD_MATERIAL pm on pm.FMATERIALID=poe.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS porg on porg.FORGID=po.FPURCHASEORGID --��֯���ɹ���֯
    	where
    	pm.FNUMBER=m.FNUMBER --�ɹ��������=��ϱ���
    	and porg.FNUMBER=org.FNUMBER--�����ɹ���֯����=�����֯����
    	and po.FDOCUMENTSTATUS = 'C'--�����
    	--and po.FCLOSESTATUS='A' --δ�ر�
    	and po.FCANCELSTATUS<> 'B' --δȡ�
    	and po.FDATE between @begdate and @enddate
    )  �ۼƲɹ��������
    ,(
    	select sum(por.FSTOCKBASESTOCKINQTY)
    	from t_PUR_POOrder po  --�ɹ�����
    	left join t_PUR_POOrderEntry poe on po.fid=poe.FID ---�ɹ������ϸ
    	left join T_PUR_POORDERENTRY_R por on poe.FENTRYID=por.FENTRYID --���������
    	left join T_BD_MATERIAL pm on pm.FMATERIALID=poe.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS porg on porg.FORGID=po.FPURCHASEORGID --��֯���ɹ���֯
    	where
    	pm.FNUMBER=m.FNUMBER --�ɹ��������=��ϱ���
    	and porg.FNUMBER=org.FNUMBER--�����ɹ���֯����=�����֯����
    	and po.FDOCUMENTSTATUS = 'C'--�����
    	--and po.FCLOSESTATUS='A' --δ�ر�
    	and po.FCANCELSTATUS<> 'B' --δȡ�
    	and po.FDATE between @begdate and @enddate
    )  �ۼƲɹ�������
    ,(
    	select sum(por.FREMAINSTOCKINQTY)
    	from t_PUR_POOrder po  --�ɹ�����
    	left join t_PUR_POOrderEntry poe on po.fid=poe.FID ---�ɹ������ϸ
    	left join T_PUR_POORDERENTRY_R por on poe.FENTRYID=por.FENTRYID --���������
    	left join T_BD_MATERIAL pm on pm.FMATERIALID=poe.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS porg on porg.FORGID=po.FPURCHASEORGID --��֯���ɹ���֯
    	where
    	pm.FNUMBER=m.FNUMBER --�ɹ��������=��ϱ���
    	and porg.FNUMBER=org.FNUMBER--�����ɹ���֯����=�����֯����
    	and po.FDOCUMENTSTATUS = 'C'--�����
    	and po.FCLOSESTATUS='A' --δ�ر�
    	and po.FCANCELSTATUS<> 'B' --δȡ�
    	and po.FDATE between @begdate and @enddate
    )  ʣ��δ�����
    
    --Ԥ�Ƴ�����۶���������������FSTOCKBASEQTY+�ۼ��˻�����������������FSTOCKBASEREBACKQTY-�ۼƳ���������������FSTOCKBASESTOCKOUTQTY
    ,(
    	select sum(soe.FQTY)
    	from T_SAL_ORDER so
    	left join T_SAL_ORDERENTRY soe on soe.FID=so.fid
    	--left join T_SAL_ORDERENTRY_R soer on soe.FENTRYID=soer.FENTRYID 
    	left join T_BD_MATERIAL sm on sm.FMATERIALID=soe.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS sorg on sorg.FORGID=so.FSALEORGID --��֯�������֯
    	where
    	sm.FNUMBER=m.FNUMBER --��۶������=��ϱ���
    	and sorg.FNUMBER=org.FNUMBER--���������֯����=�����֯����
    	and so.FDOCUMENTSTATUS='C'--�Ѿ����
    	--and so.FCLOSESTATUS='A' --δ�ر�
    	and so.FCANCELSTATUS<> 'B' --δȡ�
    	and so.FDATE between @begdate and @enddate
    )  �ۼ���۶������
    ,(
    	select sum(soee.FSTOCKBASESTOCKOUTQTY)
    	from T_SAL_ORDER so
    	left join T_SAL_ORDERENTRY soe on soe.FID=so.fid
    	left join T_SAL_ORDERENTRY_E soee on soe.FENTRYID=soee.FENTRYID
    	left join T_BD_MATERIAL sm on sm.FMATERIALID=soe.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS sorg on sorg.FORGID=so.FSALEORGID --��֯�������֯
    	where
    	sm.FNUMBER=m.FNUMBER --��۶������=��ϱ���
    	and sorg.FNUMBER=org.FNUMBER--���������֯����=�����֯����
    	and so.FDOCUMENTSTATUS='C'--�Ѿ����
    	--and so.FCLOSESTATUS='A' --δ�ر�
    	and so.FCANCELSTATUS<> 'B' --δȡ�
    	and so.FDATE between @begdate and @enddate
    )  �ۼ���۳������
    ,(
    	select sum(soee.FSTOCKBASEREBACKQTY)
    	from T_SAL_ORDER so
    	left join T_SAL_ORDERENTRY soe on soe.FID=so.fid
    	left join T_SAL_ORDERENTRY_E soee on soe.FENTRYID=soee.FENTRYID
    	left join T_BD_MATERIAL sm on sm.FMATERIALID=soe.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS sorg on sorg.FORGID=so.FSALEORGID --��֯�������֯
    	where
    	sm.FNUMBER=m.FNUMBER --��۶������=��ϱ���
    	and sorg.FNUMBER=org.FNUMBER--���������֯����=�����֯����
    	and so.FDOCUMENTSTATUS='C'--�Ѿ����
    	--and so.FCLOSESTATUS='A' --δ�ر�
    	and so.FCANCELSTATUS<> 'B' --δȡ�
    	and so.FDATE between @begdate and @enddate
    )  �ۼ�����˻����
    
    ,(
    	select sum(soer.FREMAINOUTQTY)
    	from T_SAL_ORDER so
    	left join T_SAL_ORDERENTRY soe on soe.FID=so.fid
    	left join T_SAL_ORDERENTRY_R soer on soe.FENTRYID=soer.FENTRYID 
    	left join T_BD_MATERIAL sm on sm.FMATERIALID=soe.FMATERIALID --��ϱ�ȡ��ϱ���
    	left join T_ORG_ORGANIZATIONS sorg on sorg.FORGID=so.FSALEORGID --��֯�������֯
    	where
    	sm.FNUMBER=m.FNUMBER --��۶������=��ϱ���
    	and sorg.FNUMBER=org.FNUMBER--���������֯����=�����֯����
    	and so.FDOCUMENTSTATUS='C'--�Ѿ����
    	--and so.FCLOSESTATUS='A' --δ�ر�
    	and so.FCANCELSTATUS<> 'B' --δȡ�
    	and so.FDATE between @begdate and @enddate
    )  ʣ��δ�����
    
    ,cast(ms.FMINSTOCK as decimal(23,2)) �����
    ,cast(ms.FSAFESTOCK as decimal(23,2)) ��ȫ���
    
    
    ,cast(
    (select sum(FPRICEQTY)
    from T_AP_PAYABLEENTRY ape 
    left join T_AP_PAYABLE ap on ap.FID=ape.FID
    left join T_BD_MATERIAL tm on tm.FMATERIALID=ape.FMATERIALID --��ϱ�ȡ��ϱ���
    left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ap.FSETTLEORGID --��֯��������֯
    where ap.FDOCUMENTSTATUS='C'
    and tm.FNUMBER=m.FNUMBER --��ϱ���
    and torg.FNUMBER=org.FNUMBER--��֯����
    and ap.FDATE between @begdate and @enddate
    )
     as decimal(23,2))
    �½�����
    
    
    ,cast(
    (select sum(FPRICEQTY)
    from T_AP_PAYABLEENTRY ape 
    left join T_AP_PAYABLE ap on ap.FID=ape.FID
    left join T_BD_MATERIAL tm on tm.FMATERIALID=ape.FMATERIALID --��ϱ�ȡ��ϱ���
    left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ap.FSETTLEORGID --��֯��������֯
    where ap.FDOCUMENTSTATUS='C'
    and tm.FNUMBER=m.FNUMBER --��ϱ���
    and torg.FNUMBER=org.FNUMBER--��֯����
    and datediff(year,ap.FDATE,@enddate)=0
    )
     as decimal(23,2))
    ������
    
    ,cast(
    (select sum(FPRICEQTY)
    from T_AP_PAYABLEENTRY ape 
    left join T_AP_PAYABLE ap on ap.FID=ape.FID
    left join T_BD_MATERIAL tm on tm.FMATERIALID=ape.FMATERIALID --��ϱ�ȡ��ϱ���
    left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ap.FSETTLEORGID --��֯��������֯
    where ap.FDOCUMENTSTATUS='C'
    and tm.FNUMBER=m.FNUMBER --��ϱ���
    and torg.FNUMBER=org.FNUMBER--��֯����
    and datediff(year,ap.FDATE,@enddate)=1
    )
     as decimal(23,2))
    ��������
    
    
    ,cast(
    (select sum(FPRICEQTY)
    from t_AR_receivableEntry are 
    left join t_AR_receivable ar on ar.FID=are.FID
    left join T_BD_MATERIAL tm on tm.FMATERIALID=are.FMATERIALID --��ϱ�ȡ��ϱ���
    left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ar.FSETTLEORGID --��֯��������֯
    where ar.FDOCUMENTSTATUS='C'
    and tm.FNUMBER=m.FNUMBER --��ϱ���
    and torg.FNUMBER=org.FNUMBER--��֯����
    and ar.FDATE between @begdate and @enddate
    )
     as decimal(23,2))
    �³�����
    
    ,cast(
    (select sum(FPRICEQTY)
    from t_AR_receivableEntry are 
    left join t_AR_receivable ar on ar.FID=are.FID
    left join T_BD_MATERIAL tm on tm.FMATERIALID=are.FMATERIALID --��ϱ�ȡ��ϱ���
    left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ar.FSETTLEORGID --��֯��������֯
    where ar.FDOCUMENTSTATUS='C'
    and tm.FNUMBER=m.FNUMBER --��ϱ���
    and torg.FNUMBER=org.FNUMBER--��֯����
    and datediff(year,ar.FDATE,@enddate)=0
    )
     as decimal(23,2))
    ������
    
    ,cast(
    (select sum(FPRICEQTY)
    from t_AR_receivableEntry are 
    left join t_AR_receivable ar on ar.FID=are.FID
    left join T_BD_MATERIAL tm on tm.FMATERIALID=are.FMATERIALID --��ϱ�ȡ��ϱ���
    left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ar.FSETTLEORGID --��֯��������֯
    where ar.FDOCUMENTSTATUS='C'
    and tm.FNUMBER=m.FNUMBER --��ϱ���
    and torg.FNUMBER=org.FNUMBER--��֯����
    and datediff(year,ar.FDATE,@enddate)=1
    )
     as decimal(23,2))
    ��������
    
    into #t1
    from
    T_BD_MATERIAL m --������
    left join T_BD_MATERIAL_L ml on ml.FMATERIALID=m.FMATERIALID --������
    left join T_BD_MATERIALGROUP_L mgl on mgl.FID=m.FMATERIALGROUP --��Ϸ���
    left join t_BD_MaterialStock ms on ms.FMATERIALID=m.FMATERIALID --��ϲֿ�ҳǩ
    
    left join T_ORG_ORGANIZATIONS org on org.FORGID=m.FUSEORGID --��֯��ȡʹ����֯����
    left join T_ORG_ORGANIZATIONS_L orgl on orgl.FORGID=m.FUSEORGID and orgl.FLOCALEID=2052--��֯�������չ��ȡ��֯���
    
    where m.FDOCUMENTSTATUS='C'
    and m.FFORBIDSTATUS='A'
    
    select 
    CASE
     WHEN isnull(�ɱ�����,0)=0 and isnull(���ɹ���,0)<>0 then (��ʱ���*���ɹ���) 
     WHEN isnull(�ɱ�����,0)<>0 then (��ʱ���*�ɱ�����) 
    else  0 end 
    as �ɱ����
    ,*
    from #t1
    
     drop table #t1

    Powered by kodbox V1.64

    Copyright © kodcloud.com.

    Files