SELECT 产品系列,CONVERT(DECIMAL(18,4),标准金额) 标准金额,CONVERT(DECIMAL(18,4),销售金额) 销售金额,CONVERT(DECIMAL(18,4),产品成本金额) 产品成本金额
,convert(varchar(20),CONVERT(DECIMAL(18,2),round((1.0*(标准金额))/(1.0*(nullif(销售金额,0))),5,5)*100))+'%'  as '标准销售比值',
convert(varchar(20),CONVERT(DECIMAL(18,2),round((1.0*(产品成本金额))/(1.0*(nullif(销售金额,0))),5,5)*100))+'%'  as 'BOM成本销售比值'
,convert(varchar(20),convert(decimal(18,2),isnull((销售金额-产品成本金额)/nullif(销售金额,0)*100,0)))+'%' as '毛利率',
row_number() over( order by isnull((销售金额-产品成本金额)/nullif(销售金额,0)*100,0) desc) as 名次  FROM(SELECT TOP 100 PERCENT T4.FDATAVALUE as 产品系列,
SUM((TB.F_ZTW_PRICE*T.FREALQTY)) as 标准金额,SUM((FALLAMOUNT)) as 销售金额,SUM((T.FREALQTY*F_LQKJ_AMOUNT)) as 产品成本金额
,日期
FROM (
select a.*,convert(varchar(6),FDATE,112) 日期,FDATE as YearMonth from   T_SAL_OUTSTOCKENTRY a left join  T_SAL_OUTSTOCK b on a.FID=b.FID where F_ZTW_SETTLEMENT in(1)--包线
union all 
select a.*,convert(varchar(6),F_ZTW_SUBMITDATE,112) 日期,F_ZTW_SUBMITDATE AS YearMonth from T_SAL_OUTSTOCKENTRY a 
left join T_SAL_OUTSTOCKENTRY_F d on d.FENTRYID=a.FENTRYID
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 ) c on c.FSID=a.FENTRYID 
where   F_ZTW_SETTLEMENT in(3)  and (c.FDOCUMENTSTATUS='C' or c.FDOCUMENTSTATUS='B') and FSTABLENAME='T_SAL_OUTSTOCKENTRY' and d.FISFREE='0' --计价  
and FSETACCOUNTTYPE='2'  
) 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 
INNER JOIN (select FID,FCUSTOMERID,FDATE,FCANCELSTATUS,FDOCUMENTSTATUS from  T_SAL_OUTSTOCK ) TOU on T.FID =TOU.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(T.YearMonth))+'-'+ convert(varchar,MONTH(T.YearMonth))
				 AND TB.FMASTERID=t7.FMASTERID--计划单价
inner join T_BD_CUSTOMER_L TC ON TOU.FCUSTOMERID = TC.FCUSTID 
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) T4 ON T.FITEM = T4.FENTRYID 
WHERE 
TOU.FDOCUMENTSTATUS='C' and TOU.FCANCELSTATUS='A' 
GROUP BY T4.FDATAVALUE,日期 )T0
where 产品系列<>'' and 产品系列 is not null and (日期 like '#FDATE#%' or '#FDATE#'='')