Man_Without_Face
Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
select c.contracts_number,
c.contracts_fullname,
c.contracts_unn,
c.contracts_account,
i.invoicecards_number,
i.invoicecards_datetime,
i.invoicecards_countcards,
i.invoicecards_countcards * i.invoicecards_pricecard as invoicecards_summ,
(if p.payments_summa = 0 then p.payments_forcardsumma else '' endif) as payments_forcardsumma,
abs(if p.payments_summa <> 0 then p.payments_summa else '' endif) as payments_summa,
p.payments_number,
p.payments_registerdate,
r.periodstatus_cardstartsumma,
r.periodstatus_period
from roman.contracts c left OUTER JOIN berlio.invoicecards i
on (c.contracts = i.invoicecards_contract and i.invoicecards_datetime >= '2010.03.01' and i.invoicecards_datetime < '2010.04.01')
left OUTER JOIN roman.payments p on ((p.payments_flags & 128) <> 0 and p.payments_registerdate >= '2010.03.01' and p.payments_registerdate < '2010.04.01' and c.contracts = p.payments_contract)
left OUTER JOIN roman.periodstatus r on r.periodstatus_contract = c.contracts, roman.periods n
where
c.contracts_type = 5
and n.periods = r.periodstatus_period
and n.periods_begindate = '2010.03.01'
and (if n.periods_enddate is null then today(*) else n.periods_enddate endif) = (if n.periods_enddate is null then '2010.04.01' -- -1
else '2010.04.01' endif)
and (coalesce(invoicecards_summ, p.payments_summa) is not null or r.periodstatus_cardstartsumma <>0)
order by c.contracts_number
|
