|
Объединение запросов
#39958870
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Здравствуйте, уважаемые коллеги!
Помогите сджойнить...
НЕ так силен в sql как в php (там могу это сделать).
Вопрос, а можно ведь и в общем запросе как то это сделать?
общие поля CENTER в обоих запросах
1.
2.
3.
select SUBSTR (t.kgp, 1,5) AS CENTER, nvl(u.name, 'n/r') AS ORG, l.inn
from service_point t left join ufps_names u on u.NUM = SUBSTR (t.kgp, 1,5), legal_person l
where t.is_delete=0 and t.legal_person=l.object_id and t.legal_person not in (90,96)
и
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SELECT SUBSTR (o.Kgp_Of_Getpoint, 1,5) AS CENTER,
sum (case when (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then o.client_sum else 0 end) as GET_SUM,
sum (case when (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then 1 else 0 end) as GET_NUM,
sum(case when o.TYPE_OF_SENDING = 6 and o.COD_FLAG = 1 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then o.client_sum else 0 end) as GET_SUM_NP_card,
sum(case when o.TYPE_OF_SENDING = 6 and o.COD_FLAG = 1 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then 1 else 0 end) as GET_NUM_NP_card,
sum(case when o.TYPE_OF_SENDING = 6 and o.order_category=6 and o.COD_FLAG = 0 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then o.client_sum else 0 end) as GET_SUM_B_card,
sum(case when o.TYPE_OF_SENDING = 6 and o.order_category=6 and o.COD_FLAG = 0 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then 1 else 0 end) as GET_NUM_B_card,
sum(case when o.TYPE_OF_SENDING = 6 and o.order_category!=6 and o.COD_FLAG = 0 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then o.client_sum else 0 end) as GET_SUM_other_card,
sum(case when o.TYPE_OF_SENDING = 6 and o.order_category!=6 and o.COD_FLAG = 0 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then 1 else 0 end) as GET_NUM_other_card,
sum(case when o.TYPE_OF_SENDING != 6 and o.COD_FLAG = 1 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then o.client_sum else 0 end) as GET_SUM_NP,
sum(case when o.TYPE_OF_SENDING != 6 and o.COD_FLAG = 1 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then 1 else 0 end) as GET_NUM_NP,
sum(case when o.TYPE_OF_SENDING != 6 and o.order_category=6 and o.COD_FLAG = 0 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then o.client_sum else 0 end) as GET_SUM_B,
sum(case when o.TYPE_OF_SENDING != 6 and o.order_category=6 and o.COD_FLAG = 0 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then 1 else 0 end) as GET_NUM_B,
sum(case when o.TYPE_OF_SENDING != 6 and o.order_category!=6 and o.COD_FLAG = 0 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then o.client_sum else 0 end) as GET_SUM_other,
sum(case when o.TYPE_OF_SENDING != 6 and o.order_category!=6 and o.COD_FLAG = 0 AND (o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004) then 1 else 0 end) as GET_NUM_other,
sum(case when o.Order_Status = 6 AND (o.Paid_Rep_Stamp IS NULL OR o.Paid_Rep_Stamp > 202004) then o.client_sum else 0 end) as RETN_S,
sum(case when o.Order_Status = 6 AND (o.Paid_Rep_Stamp IS NULL OR o.Paid_Rep_Stamp > 202004) then 1 else 0 end) as RETN_N
FROM Work_Order o
WHERE o.Wrk_Regime = 2
AND o.Order_Status IN (1, 3, 5, 6, 7)
AND ((o.Paid_Rep_Stamp IS NULL OR o.Paid_Rep_Stamp > 202004) OR
(o.Get_Rep_Stamp IS NULL OR o.Get_Rep_Stamp > 202004))
group by SUBSTR (o.Kgp_Of_Getpoint, 1,5)
|
|
|
