Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- TO_CHAR(m.create_time, 'yyyy-mm-dd') create_time,
- m.customer_name,
- m.salesmen_indo::json->>'name' sales_name,
- m.salesmen_indo,
- totalTestingCost,
- t.test_items
- FROM t_inq_order m
- LEFT JOIN t_inq_sync_crm_detail t
- ON t.inq_order_id = m."id"
- CROSS JOIN LATERAL (
- SELECT
- sum((x.obj->>'testingCost')::float) as totalTestingCost
- FROM json_array_elements(
- case when t.test_items LIKE '{%' OR t.test_items LIKE '[%' then t.test_items::json else null end
- ) as x(obj)
- ) totalTestingCost
- -- LEFT JOIN (
- -- SELECT
- -- t.id,
- -- SUM((j->>'testingCost')::FLOAT) totalTestingCost
- -- FROM (
- -- SELECT id, json_array_elements(t.test_items::json) j
- -- FROM t_inq_sync_crm_detail t
- -- WHERE test_items LIKE '{%' OR test_items LIKE '[%' -- TODO check if is json, NOT best condition
- -- ) t
- -- GROUP BY t.id
- -- ) test_summary
- -- ON test_summary.id = t.id
- WHERE m.create_time >= '2023-01-01'
- --and to_number(to_char(m.create_time, 'yyyymmdd'),'9999999900')<20230206
- AND t.ask_type ='药物类产品'
- AND m.source_type <>'老系统转入'
- AND t.sales_order_code IS NOT NULL;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement