| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950 |
- with sa_salestarget as (
- select 0+CAST(sum(amount) AS CHAR) amount,point,sys_enterpriseid,year from(SELECT (t5.sumqty*t1.price) amount, MONTH (t2.createdate) point,ifnull(t2.sys_enterpriseid,0) sys_enterpriseid,YEAR(t2.createdate) year
- FROM sa_orderitems t1
- LEFT JOIN sa_order t2 ON t1.sa_orderid=t2.sa_orderid AND t1.siteid=t2.siteid
- left join sys_enterprise_tradefield t3 on t2.sys_enterpriseid=t3.sys_enterpriseid and t2.siteid=t3.siteid
- left join sa_salearea t4 on t4.sa_saleareaid=t3.sa_saleareaid and t4.siteid=t3.siteid
- inner join (
- SELECT t1.sa_orderitemsid,t1.siteid,
- SUM(ifnull(t1.qty,0)) sumqty
- FROM st_stockbill_items t1
- inner join st_stockbill t2 on t1.siteid = t2.siteid and t1.st_stockbillid = t2.st_stockbillid
- inner join sa_orderitems t4 on t1.sa_orderitemsid=t4.sa_orderitemsid and t1.siteid = t4.siteid
- WHERE t2.STATUS ='审核' and $where1$ and t2.rb=1 group by t1.sa_orderitemsid,t1.siteid) t5 on t5.sa_orderitemsid=t1.sa_orderitemsid and t5.siteid=t1.siteid
- WHERE YEAR (t2.createdate)=$year$ and t2.sys_enterpriseid in $sys_enterpriseid$ AND t1.siteid=$siteid$) a
- GROUP BY point,sys_enterpriseid,year
- )
- SELECT DISTINCT m.year,
- m.sys_enterpriseid,
- ROUND((ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0)+ ifnull(m4.amount,0)+ ifnull(m5.amount,0)+ ifnull(m6.amount,0)+ ifnull(m7.amount,0)+ ifnull(m8.amount,0)+ ifnull(m9.amount,0) + ifnull(m10.amount,0)+ ifnull(m11.amount,0)+ ifnull(m12.amount,0))/10000,2) as y1a,
- ROUND((ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0))/10000,2) as s1a,
- ROUND((ifnull(m4.amount,0)+ ifnull(m5.amount,0)+ifnull(m6.amount,0))/10000,2) as s2a,
- ROUND((ifnull(m7.amount,0)+ ifnull(m8.amount,0)+ifnull(m9.amount,0))/10000,2) as s3a,
- ROUND((ifnull(m10.amount,0)+ ifnull(m11.amount,0)+ifnull(m12.amount,0))/10000,2) as s4a,
- ROUND(ifnull(m1.amount,0)/10000,2) as m1a,
- ROUND(ifnull(m2.amount,0)/10000,2) as m2a,
- ROUND(ifnull(m3.amount,0)/10000,2) as m3a,
- ROUND(ifnull(m4.amount,0)/10000,2)as m4a,
- ROUND(ifnull(m5.amount,0)/10000,2) as m5a,
- ROUND(ifnull(m6.amount,0)/10000,2) as m6a,
- ROUND(ifnull(m7.amount,0)/10000,2) as m7a,
- ROUND(ifnull(m8.amount,0)/10000,2) as m8a,
- ROUND(ifnull(m9.amount,0)/10000,2) as m9a,
- ROUND(ifnull(m10.amount,0)/10000,2) as m10a,
- ROUND(ifnull(m11.amount,0)/10000,2) as m11a,
- ROUND(ifnull(m12.amount,0)/10000,2) as m12a
- from sa_salestarget m
- LEFT JOIN sa_salestarget m1 ON m1.year = m.year and m1.point = 1 and m.sys_enterpriseid = m1.sys_enterpriseid
- LEFT JOIN sa_salestarget m2 ON m2.year = m.year and m2.point = 2 and m.sys_enterpriseid = m2.sys_enterpriseid
- LEFT JOIN sa_salestarget m3 ON m3.year = m.year and m3.point = 3 and m.sys_enterpriseid = m3.sys_enterpriseid
- LEFT JOIN sa_salestarget m4 ON m4.year = m.year and m4.point = 4 and m.sys_enterpriseid = m4.sys_enterpriseid
- LEFT JOIN sa_salestarget m5 ON m5.year = m.year and m5.point = 5 and m.sys_enterpriseid = m5.sys_enterpriseid
- LEFT JOIN sa_salestarget m6 ON m6.year = m.year and m6.point = 6 and m.sys_enterpriseid = m6.sys_enterpriseid
- LEFT JOIN sa_salestarget m7 ON m7.year = m.year and m7.point = 7 and m.sys_enterpriseid = m7.sys_enterpriseid
- LEFT JOIN sa_salestarget m8 ON m8.year = m.year and m8.point = 8 and m.sys_enterpriseid = m8.sys_enterpriseid
- LEFT JOIN sa_salestarget m9 ON m9.year = m.year and m9.point = 9 and m.sys_enterpriseid = m9.sys_enterpriseid
- LEFT JOIN sa_salestarget m10 ON m10.year = m.year and m10.point = 10 and m.sys_enterpriseid = m10.sys_enterpriseid
- LEFT JOIN sa_salestarget m11 ON m11.year = m.year and m11.point = 11 and m.sys_enterpriseid = m11.sys_enterpriseid
- LEFT JOIN sa_salestarget m12 ON m12.year = m.year and m12.point = 12 and m.sys_enterpriseid = m12.sys_enterpriseid
- ORDER BY m.year desc
|
