Advertisement
Pandaaaa906

Untitled

Mar 1st, 2023 (edited)
1,298
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2. TO_CHAR(m.create_time, 'yyyy-mm-dd') create_time,
  3. m.customer_name,
  4. m.salesmen_indo::json->>'name' sales_name,
  5. m.salesmen_indo,
  6. totalTestingCost,
  7. t.test_items
  8. FROM  t_inq_order m
  9. LEFT JOIN t_inq_sync_crm_detail t
  10. ON t.inq_order_id = m."id"
  11. CROSS JOIN LATERAL (
  12.     SELECT
  13.     sum((x.obj->>'testingCost')::float) as totalTestingCost
  14.     FROM json_array_elements(
  15.         case when t.test_items LIKE '{%' OR t.test_items LIKE '[%' then t.test_items::json else null end
  16.         ) as x(obj)
  17. ) totalTestingCost
  18. -- LEFT JOIN (
  19. --     SELECT
  20. --     t.id,
  21. --     SUM((j->>'testingCost')::FLOAT) totalTestingCost
  22. --     FROM (
  23. --         SELECT id, json_array_elements(t.test_items::json) j
  24. --         FROM t_inq_sync_crm_detail t
  25. --         WHERE test_items LIKE '{%' OR test_items LIKE '[%'  -- TODO check if is json, NOT best condition
  26. --         ) t
  27. --     GROUP BY t.id
  28. -- ) test_summary
  29. -- ON test_summary.id = t.id
  30.  
  31. WHERE m.create_time >= '2023-01-01'
  32. --and to_number(to_char(m.create_time, 'yyyymmdd'),'9999999900')<20230206
  33. AND t.ask_type ='药物类产品'
  34. AND m.source_type <>'老系统转入'
  35. AND t.sales_order_code IS NOT NULL;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement