select /*переделать выборку версий через group by*/ a.contract_id a.deal_uid a.probability_perc a.agreement a.agreement_version case when a.agreement_version = 0 then 'New sale' else 'Up sale' end as deal_type s.specification si.specification_id d.contract d.dt_contract k.contragent siv.specification_item_uid siv.specification_item_version si.svc_id si.pricing_model_id p.pricing_model_short svc.code siv.dt_from siv.dt_to siv.quantity siv.price siv.price*siv.quantity as cost ver.quantity as quantity_prev ver.price as price_prev ver.price*ver.quantity as cost_prev ver.agreement_version as version_prev COALESCE(siv.price*siv.quantity,0) - COALESCE(ver.price*ver.quantity,0) as cost_diff (COALESCE(siv.price*siv.quantity*a.probability_perc/100,0) - COALESCE(ver.price*ver.quantity*ver.probability_perc/100,0)) as cost_diff_prob from specification_item_version siv join specification_item si on (siv.specification_item_uid=si.specification_item_uid) join specification s on (si.specification_id=s.specification_id) join agreement a on (s.contract_id=a.contract_id AND siv.agreement_version=a.agreement_version) join contract d on s.contract_id=d.contract_id join contragent k on d.contragent_id=k.contragent_id join svc on (si.svc_id=svc.svc_id) join pricing_model p on (si.pricing_model_id=p.pricing_model_id) left outer join (select siv1.agreement_version ,siv1.specification_item_uid ,a1.agreement ,a1.contract_id ,a1.dt_agreement ,a1.is_actual ,a1.probability_perc ,siv1.specification_item_version ,siv1.quantity ,siv1.price ,siv1.dt_from ,siv1.dt_to ,ii1.specification_id from specification_item_version siv1 join specification_item ii1 on (siv1.specification_item_uid=ii1.specification_item_uid) join specification is1 on (ii1.specification_id=is1.specification_id) join agreement a1 on (siv1.agreement_version=a1.agreement_version AND is1.contract_id=a1.contract_id) ) ver on (si.specification_item_uid=ver.specification_item_uid AND ver.specification_id=s.specification_id AND ver.agreement_version = (select max(iiv.agreement_version) from specification_item_version iiv join specification_item ii on (iiv.specification_item_uid=ii.specification_item_uid) join specification isp on (ii.specification_id=isp.specification_id) join agreement ia on (isp.contract_id=ia.contract_id AND iiv.agreement_version=ia.agreement_version) where iiv.specification_item_uid=si.specification_item_uid AND ia.is_actual AND ia.agreement_version < a.agreement_version ) ) where a.agreement_version=(select max(iiv.agreement_version) from specification_item_version iiv join specification_item ii on (iiv.specification_item_uid=ii.specification_item_uid) join specification isp on (ii.specification_id=isp.specification_id) join agreement ia on (isp.contract_id=ia.contract_id AND iiv.agreement_version=ia.agreement_version) where iiv.specification_item_uid=si.specification_item_uid --AND ia.is_actual ) order by select count(*) as cnt from specification_item where 1=1 Экземпляры услуг (текущее состояние) title="редактировать" class="edit"title="просмотр" class="view"> #agreement# [#agreement_version#] #specification_item_uid# #contract# #dateformat(dt_contract,"YYYY-MM-DD")# #specification# #version_prev# #gridFooter#