问题描述:维修领料明细报表中按不同的结算方式,毛利显示不正确
问题截图:
后台查询语句
后台sql
select business_nature,pick_id,a.repair_id,a.repair_sort,a.car_platenum,a.car_id,carbrand,product_num,product_name,amount,money,convert(varchar(100),pick_time,23)as pick_time,convert(varchar(100),a.sign_time,23)as sign_time,carsort,product_unit,pick_amount,rpick_amount,isnull(discount_rate,1)*price as discount_price,price,(isnull(discount_rate,1))*price as nprice, cost_price=isnull((select top 1 isnull(cost_money/nullif(amount,0),0) from t_product_storedetal where document_id=a.pick_id and product_id=a.product_id and store_name=a.store_name),0),cost=amount*isnull((select top 1 isnull(cost_money/nullif(amount,0),0) from t_product_storedetal where document_id=a.pick_id and product_id=a.product_id and store_name=a.store_name),0),gross=(case when paysort = '空开(派)' then 0 else(money-amount*isnull((select top 1 isnull(cost_money/nullif(amount,0),0) from t_product_storedetal where document_id=a.pick_id and product_id=a.product_id and store_name=a.store_name),0)) end ),discount_gross=(case when paysort = '空开(派)' then 0 else (money*isnull(discount_rate,1)-amount*isnull((select top 1 isnull(cost_money/nullif(amount,0),0) from t_product_storedetal where document_id=a.pick_id and product_id=a.product_id and store_name=a.store_name),0)) end ),grossrate=(case when (paysort = '空开(派)' or money*isnull(discount_rate,1)=0) then 0 else (convert(decimal(18,0),(money*isnull(discount_rate,1)-amount*isnull((select top 1 isnull(cost_money/nullif(amount,0),0) from t_product_storedetal where document_id=a.pick_id and product_id=a.product_id and store_name=a.store_name),0))/(money*isnull(discount_rate,1))*100)) end ),product_model,car_brand,car_sort,a.cust_name,product_home,store_name,waiter,b.business_man,pick_man,paysort,(case when favorableflg=1 then '√' else '×' end) as favorableflg,pick_dealman,pick_createman,pick_createtime,a.rem,discount_rate*10 as discount_rate,money*isnull(discount_rate,1) as discount_money,unit_name from v_repair_pickdetal a left join t_repair b on a.repair_id=b.repair_id left join (select product_id as pid,unit_id from t_product) c on a.product_id=c.pid left join t_repair_settle e on a.repair_id=e.repair_id left join (select unit_id as uid,unit_name from t_unit) d on c.unit_id=d.uid where 1=1 and convert(varchar(100),a.pick_time,23) >='2018-11-01' and convert(varchar(100),a.pick_time,23) <='2018-11-16' and a.repair_id like '%WX181115005%' and store_name in ('百货库','本地仓库','仓库12','测试','测试1232','测试仓库','常州仓库','城北库','得到','东环店仓库','固定资产库','默认仓库','内部仓库','配件仓库','汽修配件库','前台部','--全部--','市场部库','市场部一南通库','市场部一无锡库','市场部一吴江库','一汽申荣库','油漆库','总仓库') and b.organizationid='7' and exists(select organizationid from t_user_organization where user_name='超级用户' and organizationid = b.organizationid)