
select * from (
select 产品系列,[计价&标准金额],[计价&销售金额],[计价&销售成本],
convert(varchar(20),convert(decimal(18,2),([计价&标准金额]/NULLIF([计价&销售金额],0)*100)))+'%' as [计价&标准销售比值],
convert(varchar(20),convert(decimal(18,2),([计价&销售成本]/NULLIF([计价&销售金额],0))*100) )+'%'AS [计价&BOM成本销售比值],
[承包&标准金额],[承包&销售金额],[承包&销售成本],
convert(varchar(20),convert(decimal(18,2),([承包&标准金额]/NULLIF([承包&销售金额],0)*100)))+'%' AS [承包&标准销售比值],
convert(varchar(20),convert(decimal(18,2),([承包&销售成本]/NULLIF([承包&销售金额],0))*100))+'%' AS [承包&BOM成本销售比值],
convert(decimal(18,4),(isnull([计价&标准金额],0))+(isnull([承包&标准金额],0)),0) [合计&标准金额],
convert(decimal(18,4),(isnull([计价&销售金额],0))+(isnull([承包&销售金额],0)),0) [合计&销售金额],
convert(decimal(18,4),(isnull([计价&销售成本],0))+(isnull([承包&销售成本],0)),0) [合计&销售成本],
convert(varchar(20),convert(decimal(18,2),(ISNULL(([计价&标准金额]),0)+ISNULL(([承包&标准金额]),0))/nullif(ISNULL(([计价&销售金额]),0)+ISNULL(([承包&销售金额]),0),0)*100))+'%' [合计&标准销售比值],
convert(varchar(20),convert(decimal(18,2),(ISNULL(([计价&销售成本]),0)+ISNULL(([承包&销售成本]),0))/nullif(ISNULL(([计价&销售金额]),0)+ISNULL(([承包&销售金额]),0),0)*100))+'%' [合计&BOM成本销售比值]
from(
 SELECT A.FITEM as 产品系列,nullif(convert(decimal(18,4),ISNULL( t0.[计价&标准金额],0)),0) AS [计价&标准金额]
 ,nullif(convert(decimal(18,4),ISNULL( t0.[计价&销售金额],0)),0) AS [计价&销售金额] ,nullif(convert(decimal(18,4),ISNULL( t0.[计价&销售成本],0)),0) AS [计价&销售成本],
nullif(convert(decimal(18,4),ISNULL( t1.[承包&标准金额],0)),0) AS [承包&标准金额],nullif(convert(decimal(18,4),ISNULL( t1.[承包&销售金额],0)),0) AS [承包&销售金额],
nullif(convert(decimal(18,4),ISNULL( t1.[承包&销售成本],0)),0) AS [承包&销售成本],
convert(decimal(18,4),ISNULL(ISNULL( t0.[计价&销售成本],0)+ISNULL( t1.[承包&销售成本],0),0)) AS [合计&销售成本],
convert(varchar(20),convert(decimal(18,2),ISNULL(ISNULL( t0.[计价&标准/销售%],0)+ISNULL( t1.[承包&标准/销售%],0),0)*100))+'%' AS [合计&标准/销售%]
,convert(varchar(20),convert(decimal(18,2),ISNULL(ISNULL( t0.[计价&BOM成本/销售%],0)+ISNULL( t1.[承包&BOM成本/销售%],0),0)*100))+'%' AS [合计&BOM成本/销售%] 
FROM
(select c.FDATAVALUE as FITEM from T_SAL_OUTSTOCK a left join T_SAL_OUTSTOCKENTRY b on a.FID=b.FID
left join (select FENTRYID,case  when (CHARINDEX('-1', FDATAVALUE)- 1)>0  then SUBSTRING(FDATAVALUE, 1, CHARINDEX('-1', FDATAVALUE) - 1) else FDATAVALUE end as FDATAVALUE 
from T_BAS_ASSISTANTDATAENTRY_L WHERE FLOCALEID = 2052) c on c.FENTRYID=b.FITEM
where --(convert(varchar(6),FDATE,112) like '#FDATE#%' or '#FDATE#' = '') and 
a.FDOCUMENTSTATUS='C' and a.FCANCELSTATUS='A'
group by FDATAVALUE ) A LEFT JOIN
(select FITEM as '计价&产品系列',标准金额 as'计价&标准金额',销售金额 as '计价&销售金额',销售成本 as '计价&销售成本', 
((标准金额)/NULLIF(销售金额,0))*100  as '计价&标准/销售%',case when 销售金额 > 0 then  ((销售成本)/(销售金额)) END as '计价&BOM成本/销售%' from(
select CP.FDATAVALUE AS FITEM,sum((isnull(TB.F_ZTW_PRICE,0)*isnull(T.FREALQTY,0))) as 标准金额,sum((isnull(TF.FTAXPRICE,0)*isnull(T.FREALQTY,0))) as 销售金额,
sum((isnull(T.FREALQTY,0)*isnull(F_LQKJ_AMOUNT,0))) as 销售成本 
 from T_SAL_OUTSTOCKENTRY T 
 left join (select F_ZTW_SUBMITDATE,FDOCUMENTSTATUS,FSTABLENAME,FSETACCOUNTTYPE,FSID from t_AR_receivable a left join t_AR_receivableEntry b on a.FID=b.FID
left join t_AR_receivableEntry_LK c on b.FENTRYID=c.FENTRYID  where FSTABLENAME='T_SAL_OUTSTOCKENTRY' and FSETACCOUNTTYPE='2' and (FDOCUMENTSTATUS='C' or FDOCUMENTSTATUS='B'))LK on LK.FSID=T.FENTRYID
INNER JOIN T_SAL_OUTSTOCKENTRY_F TF ON T.FENTRYID = TF.FENTRYID 
left join (select FMATERIALID,FMASTERID,F_ZTW_PRICE,FNUMBER from  T_BD_MATERIAL where FNUMBER like 'CZ%' or FNUMBER like 'CF%' or FNUMBER like 'SC%') TB on TB.FMATERIALID=T.FMATERIALID
INNER JOIN T_SAL_OUTSTOCKFIN TN ON T.FID = TN.FID 
LEFT JOIN T_SAL_OUTSTOCK TSK ON T.FID = TSK.FID  
left join (select FMASTERID,F_LQKJ_DATE,F_LQKJ_AMOUNT from  T_LQKJ_MaterialPrice a left join T_BD_MATERIAL b on a.F_LQKJ_MATERIAL=b.FMATERIALID where FNUMBER like 'CZ%' or FNUMBER like 'CF%' or FNUMBER like 'SC%') T7 
				ON T7.F_LQKJ_DATE=convert(varchar,YEAR(F_ZTW_SUBMITDATE))+'-'+ convert(varchar,MONTH(F_ZTW_SUBMITDATE))
				 AND TB.FMASTERID=t7.FMASTERID--计划单价
left join (select FENTRYID,case  when (CHARINDEX('-1', FDATAVALUE)- 1)>0  then SUBSTRING(FDATAVALUE, 1, CHARINDEX('-1', FDATAVALUE) - 1) else FDATAVALUE end as FDATAVALUE from T_BAS_ASSISTANTDATAENTRY_L WHERE FLOCALEID = 2052)
CP on CP.FENTRYID=T.FITEM

WHERE T.F_ZTW_SETTLEMENT = '3' and F_ZTW_SUBMITDATE is not null AND (convert(varchar(6),F_ZTW_SUBMITDATE,112) like '#FDATE#%' or '#FDATE#' = '')  
and TSK.FDOCUMENTSTATUS='C' and TSK.FCANCELSTATUS='A'  and TF.FISFREE='0'
GROUP BY  CP.FDATAVALUE)T0)T0  on T0.[计价&产品系列]=A.FITEM
LEFT JOIN
(select FITEM as '承包&产品系列',标准金额 as'承包&标准金额',销售金额 as'承包&销售金额',销售成本 as'承包&销售成本', 
((标准金额)/NULLIF(销售金额,0))*100 as '承包&标准/销售%',case when 销售金额 > 0 then  ((销售成本)/(销售金额)) END as '承包&BOM成本/销售%' from(
select CP.FDATAVALUE AS FITEM,sum((isnull(TB.F_ZTW_PRICE,0)*isnull(T.FREALQTY,0))) as 标准金额,sum((FALLAMOUNT)) as 销售金额,
sum((isnull(T.FREALQTY,0)*isnull(F_LQKJ_AMOUNT,0))) as 销售成本 
from T_SAL_OUTSTOCKENTRY T 
INNER JOIN T_SAL_OUTSTOCKENTRY_F TF ON T.FENTRYID = TF.FENTRYID 
INNER JOIN T_BD_MATERIAL  TB ON T.FMATERIALID = TB.FMATERIALID  
INNER JOIN T_SAL_OUTSTOCKFIN TN ON T.FID = TN.FID 
LEFT JOIN T_SAL_OUTSTOCK TSK ON T.FID = TSK.FID  
left join (select FMASTERID,F_LQKJ_DATE,F_LQKJ_AMOUNT from  T_LQKJ_MaterialPrice a left join T_BD_MATERIAL b on a.F_LQKJ_MATERIAL=b.FMATERIALID) T7 
				ON T7.F_LQKJ_DATE=convert(varchar,YEAR(TSK.FDATE))+'-'+ convert(varchar,MONTH(TSK.FDATE))
				 AND TB.FMASTERID=t7.FMASTERID--计划单价
left join (select FENTRYID,case  when (CHARINDEX('-1', FDATAVALUE)- 1)>0  then SUBSTRING(FDATAVALUE, 1, CHARINDEX('-1', FDATAVALUE) - 1) else FDATAVALUE end as FDATAVALUE from T_BAS_ASSISTANTDATAENTRY_L WHERE FLOCALEID = 2052)
CP on CP.FENTRYID=T.FITEM
WHERE T.F_ZTW_SETTLEMENT = '1' AND (convert(varchar(6),TSK.FDATE,112)  like '#FDATE#%' or '#FDATE#' = '') 
and TSK.FDOCUMENTSTATUS='C' and TSK.FCANCELSTATUS='A'
GROUP BY  CP.FDATAVALUE
 )T3)T1  on T1.[承包&产品系列]=A.FITEM
) B
)T
where 产品系列!=''  and T.[计价&销售金额] is not null or T.[承包&销售金额] is not null
order by 产品系列