select zdsm.numb_id, zdsm.product_name, zdsm.product_number,
jxssm.out_time, jxssm.shop_code as jx_shop_code, jxssm.shop_name as jx_shop_name, jxssm.shop_area_name as jx_shop_area,
zdsm.shop_name as zd_shop_name, zdsm.shop_code as zd_shop_code, zdsm.shop_area_name as zd_area, zdsm.sm_date,
zdsm.zd_num_id, jxssm.jx_num_id

from (select gusl.numb_id, cast(gusl.created_at as date) as sm_date, gusl.product_name, gusl.product_number,
gs.code as shop_code, gs.name as shop_name, ga.name as shop_area_name, ga.code as shop_area_code,
substring(gusl.numb_id, 4, 8) as zd_num_id
from trin.dbo.gbc_user_sm_logs as gusl
left join trin.dbo.gbc_shops as gs on gusl.shop_id=gs.id
left join trin.dbo.gbc_ad_areas as gaa on gs.ad_code=gaa.ad_code
left join trin.dbo.gbc_areas as ga on ga.id=gaa.area_id
where gusl.status in (0, 1)) as zdsm

left join
(select f3.num_id as jx_num_id, f3.jx_shop_name, f3.out_time, f3.product_id, ck.name as shop_name, ck.yw_number as shop_code, ga.name as shop_area_name, ga.code as shop_area_code
from
(
    select * from
(select fi1.num_id, ckl1.[to] as jx_shop_name, ckl1.time as out_time, ckl1.cp_type_id as product_id from fwcx_id as fi1
left join ck_list as ckl1 on substring(ckl1.idnum, 4, 8)=convert(varchar(20), fi1.bag_id)
where substring(ckl1.idnum, 1, 3)='VFB' and ckl1.[to] is not null) as t1
union
(select fi2.num_id, ckl2.jx_shop_name, ckl2.out_time, ckl2.product_id from fwcx_id as fi2 left join
(select fi1.num_id, ckl1.[to] as jx_shop_name, ckl1.time as out_time, ckl1.cp_type_id as product_id from fwcx_id as fi1
left join ck_list as ckl1 on substring(ckl1.idnum, 4, 8)=convert(varchar(20), fi1.bag_id)
where substring(ckl1.idnum, 1, 3)='VFP' and ckl1.[to] is not null) as ckl2
on ckl2.num_id=fi2.bag_id)
) as f3
left join trin.dbo.cangku as ck on ck.name=f3.jx_shop_name
left join trin.dbo.product as p on f3.product_id=p.id
left join trin.dbo.gbc_areas as ga on ga.code=ck.number
) as jxssm

on zdsm.zd_num_id=jxssm.jx_num_id