直接SQL报表,测试没问题 ,下一步 不通过
626次浏览
编辑于2017年05月08日 17:26:43
测试结果
下一步
在SQL 里面没问题
SELECT T.FSHORTNAME AS 客户简称 ,t2.FNUMBER as 产品编码,t1.FNAME as 产品名称,t.FMTONO as 计划跟踪号 ,t.F_AAA_Ms as 面色 ,t4.FWl as 纹路,t4.FDise as 底色, t4.FHD as 后段 ,
T2.FMNEMONICCODE as 旧编码,t.fdate as 订单下单日期,T.JHWHRQ AS 计划跟踪号维护日期 ,T.FPlanDate 计划回复日期,T.FYWNeedDate 业务要货日期 ,T.FQty as 订单数量,t.RKRQ as 生产入库日期,t.RKSL as 生产入库数量,
t.JDFDATE as 简单生产入库日期,t.JDRK as 简单生产入库数量,
case when (t.JDFDATE <> ''and t.JDFDATE < = t.FPlanDate ) or (t.RKRQ <>'' and t.RKRQ < = t.FPlanDate)
then '达成' else '未达成' end 交期是否达成
FROM (Select T5.FSHORTNAME as FSHORTNAME,MAX(t1.F_AAA_MS) as F_AAA_Ms, min(t.FDATE) as fdate ,T1.FMTONO,Max(T1.F_AAA_MTODATE) as FMtoDate, T2.FMASTERID,T1.FAUXPROPID,
Sum(T1.FQTY) FQty,MAX(F_AAA_MtoDate) AS JHWHRQ,
MAX(T3.FDELIVERYDATE) as FPlanDate,MAX(T1.F_AAA_NEEDDATE) as FYWNeedDate,MAX(T8.RKRQ )AS RKRQ, SUM(T8.RKSL) AS RKSL ,
SUM(J.FREALQTY) AS JDRK ,max(j.fdate) as JDFDATE
From T_SAL_ORDER T
Join T_SAL_ORDERENTRY T1 On T.FID=T1.FID and T.FCANCELSTATUS='A'
join T_SAL_ORDERENTRY_D T3 On T1.FENTRYID=T3.FENTRYID
Join T_BD_MATERIAL T2 On T1.FMATERIALID=t2.FMATERIALID
join T_SAL_ORDER T4 On T1.FID=T4.FID
Join T_BD_CUSTOMER_L T5 On T4.FCUSTID=T5.FCUSTID
---销售订单
Left join
(select max(t2.FDATE) as fdate, sum(FREALQTY) as FREALQTY,t.FSBillId,t.FSId from T_SP_INSTOCKENTRY_LK as t
inner join T_SP_INSTOCKENTRY as t1 on t1.FENTRYID =t.FEntryID
inner join T_SP_INSTOCK as t2 on t2.fid = t1.fid
where t.FSTableName ='T_SAL_ORDERENTRY'
group by t.FSBillId,t.FSId) as J ON J.FSBillId =T.FID AND J.FSId =T1.FENTRYID
LEFT JOIN
( SELECT A1.FNUMBER,FMtoNo,FAuxPropId,Sum(FREALQTY) AS RKSL ,MAX(FDATE) AS RKRQ FROM T_PRD_INSTOCKENTRY AS A
Join T_Bd_Material A1 On a.FMaterialId=A1.FMaterialId
INNER JOIN T_PRD_INSTOCK AS A2 ON A2.FID =A.FID
where A.FSTOCKSTATUSID <> '10006' and A.FSTOCKSTATUSID <> '192842'
Group by A1.FNUMBER,FMtoNo,FAuxPropId) AS T8
On T2.FNUMBER =T8.FNUMBER and T8.FMTONO=T1.FMTONO
--- 简单生产入库数量
Where T1.FMRPTERMINATESTATUS='A' and t.FBILLTYPEID ='eacb50844fc84a10b03d7b841f3a6278'
Group by T1.FMTONO,T2.FMASTERID,T1.FAUXPROPID,T5.FSHORTNAME,F_AAA_Ms ) AS T
join T_BD_MATERIAL_L T1 On T1.FMATERIALID=T.FMASTERID And T1.FLOCALEID=2052
Join T_BD_MATERIAL T2 On T1.FMATERIALID=T2.FMATERIALID
Left join AAA_V_FLEXSITEMDETAILV T4 on T.FAUXPROPID=T4.FID
推荐阅读