Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT ENGAGED.*, REALIZED.* from
- (SELECT SUM(total_sum) AS SUM_ENGAGED
- FROM (SELECT mo.mission_id, SUM(ex.amount_total) AS total_sum
- FROM fc_bs_mission_order mo INNER JOIN
- fc_bs_expense ex ON mo.mission_id = ex.mission_id INNER JOIN
- su_an_expense_kind ek ON ex.EXPENSE_KIND_ID = ek.EXPENSE_KIND_ID INNER JOIN
- su_an_kinds_group gr ON gr.GROUP_ID = ek.KINDS_GROUP_ID
- inner join fc_bs_mo_event ev on ev.MISSION_ID = mo.MISSION_ID
- WHERE
- ev.EVENT_TYPE=11 and ev.event_date >= to_date('01.01.2009','DD.MM.YYYY') and ev.event_date <= to_date('31.12.2009','DD.MM.YYYY') and
- gr.GROUP_ID = 3621 AND mo.ACTIVITY_ID IN
- (SELECT ACTIVITY_ID
- FROM SU_AN_ACTIVITY
- WHERE code IN
- (SELECT code
- FROM SU_ST_SERVICE
- WHERE attribute1 = 'STR')) AND mo.mission_id IN
- (SELECT mission_id
- FROM fc_bs_split_pattern
- WHERE mission_id != 0 AND rubric1_id =8310 AND rubric2_id = 2090 AND rubric3_id = 2120 and rubric4_id = 1000)
- GROUP BY mo.mission_id) ) ENGAGED,
- (select sum(inv.AMOUNT_TTC) AS SUM_REALIZED from fc_ii_invoice inv
- inner join fc_bs_mission_order mo on mo.MISSION_ID=inv.MISSION_ID
- inner join fc_bs_inv_event ev on ev.INVOICE_ID = inv.INVOICE_ID
- where mo.ACTIVITY_ID IN
- (SELECT ACTIVITY_ID
- FROM SU_AN_ACTIVITY
- WHERE code IN
- (SELECT code
- FROM SU_ST_SERVICE
- WHERE attribute1 = 'STR'))
- and ev.EVENT_TYPE=4 and ev.event_date >= to_date('01.01.2009','DD.MM.YYYY') and ev.event_date <= to_date('31.12.2009','DD.MM.YYYY')
- and inv.MISSION_ID in (
- SELECT mo.mission_id
- FROM fc_bs_mission_order mo INNER JOIN
- fc_bs_expense ex ON mo.mission_id = ex.mission_id INNER JOIN
- su_an_expense_kind ek ON ex.EXPENSE_KIND_ID = ek.EXPENSE_KIND_ID INNER JOIN
- su_an_kinds_group gr ON gr.GROUP_ID = ek.KINDS_GROUP_ID
- inner join fc_bs_mo_event ev on ev.MISSION_ID = mo.MISSION_ID
- WHERE
- ev.EVENT_TYPE=11 and ev.event_date >= to_date('01.01.2009','DD.MM.YYYY') and ev.event_date <= to_date('31.12.2009','DD.MM.YYYY') and
- gr.GROUP_ID =3621 AND mo.ACTIVITY_ID IN
- (SELECT ACTIVITY_ID
- FROM SU_AN_ACTIVITY
- WHERE code IN
- (SELECT code
- FROM SU_ST_SERVICE
- WHERE attribute1 = 'STR')) AND mo.mission_id IN
- (SELECT mission_id
- FROM fc_bs_split_pattern
- WHERE mission_id != 0 AND rubric1_id =8310 AND rubric2_id =2090 AND rubric3_id =2120 and rubric4_id =1000)
- )
- ) REALIZED
Add Comment
Please, Sign In to add comment