更新时间:2023-01-27 17:31:19
首先感谢大家的帮助
这是返回确切结果的查询
Here is the query which return exact result
select p_id,ag_id,
p_abb,ag_name
,sum(-1.00 * miles * price - coalesce(fees,0) - coalesce(cost,0)) as profit
,sum(miles) 'Total Miles',avg(price / miles) 'Average'
,DATEDIFF(min(buy_dt),min(sell_dt)) as 'Days'
From
(
SELECT p.id 'p_id',ag.id 'ag_id',p.abbreviation 'p_abb',ag.name 'ag_name'
,miles
,price
,fees
,c.cost
,case when t.trans_type ='Sell' then transaction_date end 'sell_dt'
,case when t.trans_type ='Buy' then transaction_date end 'buy_dt'
,transaction_date
FROM transactions t
inner join accounts a on t.account_id = a.id
inner join programs p on a.program_id = p.id
inner join agents ag on t.agent_id = ag.id
LEFT JOIN (
SELECT rp.sell_id, sum(rp.miles * t.price) as cost
from report_profit rp
inner join transactions t on rp.buy_id = t.id
where t.miles > 50000
group by rp.sell_id
order by rp.sell_id
) c on t.id = c.sell_id
) t1
group by p_id, ag_id
再次感谢大家..