SHARE
TWEET

Untitled

a guest Jun 18th, 2019 51 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select zdsm.numb_id, zdsm.product_name, zdsm.product_number,
  2. 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,
  3. zdsm.shop_name as zd_shop_name, zdsm.shop_code as zd_shop_code, zdsm.shop_area_name as zd_area, zdsm.sm_date,
  4. zdsm.zd_num_id, jxssm.jx_num_id
  5.  
  6. from (select gusl.numb_id, cast(gusl.created_at as date) as sm_date, gusl.product_name, gusl.product_number,
  7. gs.code as shop_code, gs.name as shop_name, ga.name as shop_area_name, ga.code as shop_area_code,
  8. substring(gusl.numb_id, 4, 8) as zd_num_id
  9. from trin.dbo.gbc_user_sm_logs as gusl
  10. left join trin.dbo.gbc_shops as gs on gusl.shop_id=gs.id
  11. left join trin.dbo.gbc_ad_areas as gaa on gs.ad_code=gaa.ad_code
  12. left join trin.dbo.gbc_areas as ga on ga.id=gaa.area_id
  13. where gusl.status in (0, 1)) as zdsm
  14.  
  15. left join
  16. (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
  17. from
  18. (
  19.     select * from
  20. (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
  21. left join ck_list as ckl1 on substring(ckl1.idnum, 4, 8)=convert(varchar(20), fi1.bag_id)
  22. where substring(ckl1.idnum, 1, 3)='VFB' and ckl1.[to] is not null) as t1
  23. union
  24. (select fi2.num_id, ckl2.jx_shop_name, ckl2.out_time, ckl2.product_id from fwcx_id as fi2 left join
  25. (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
  26. left join ck_list as ckl1 on substring(ckl1.idnum, 4, 8)=convert(varchar(20), fi1.bag_id)
  27. where substring(ckl1.idnum, 1, 3)='VFP' and ckl1.[to] is not null) as ckl2
  28. on ckl2.num_id=fi2.bag_id)
  29. ) as f3
  30. left join trin.dbo.cangku as ck on ck.name=f3.jx_shop_name
  31. left join trin.dbo.product as p on f3.product_id=p.id
  32. left join trin.dbo.gbc_areas as ga on ga.code=ck.number
  33. ) as jxssm
  34.  
  35. on zdsm.zd_num_id=jxssm.jx_num_id
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top