Сальдо спецоснастки ??

[Masygreen]

У когонить есть готовый рецепт получения сальдо МБП на дату ??

Код: Выделить всё

sql select
  a1.cGroupMc cGroupMc, a1.kMC kMC, a1.sMC sMC, a1.cMC cMC, a1.cPodr cPodr
  ,a1.InEkspl InEkspl
  , sum(SumOb) SumOb
  , sum(Kol)Kol
from
(
select
  mbpin.InEkspl InEkspl
,  katmc.cGroupMc cGroupMc
  , katmc.BarKod kMC
  , katmc.name sMC
  , katmc.nrec cMC
  , mbpin.cpodr cPodr
  , MBPIN.SUMMA- MBPIN.SUMMAISNN SumOb
  , mbpin.Koldoc Kol
from mbpin inner join katmbp on (mbpin.cmbp = katmbp.nrec) and (mbpin.din <= #date(31,05,2015))
inner join katmc on (katmbp.cmc = katmc.nrec)
union all
select
 mbpin.InEkspl InEkspl
  ,katmc.cGroupMc cGroupMc
  , katmc.BarKod kMC
  , katmc.name sMC
  , katmc.nrec cMC
  , mbpin.cpodr cPodr
   ,-(MBPout.SUMMA- MBPout.SUMMAISNN) SumOb
  , -mbpout.Kol Kol
from mbpout inner join mbpin on (mbpout.cMBPIn = mbpin.nrec) and (mbpout.dout <= #date(31,05,2015))
inner join katmbp on (mbpin.cmbp = katmbp.nrec)
inner join katmc on (katmbp.cmc = katmc.nrec)
)a1
group by a1.cGroupMc, a1.kMC, a1.sMC, a1.cMC, a1.cPodr,  a1.InEkspl

[Masygreen]

остановился на этом . .. тут не все операции только те которые используются у меня ..

Код: Выделить всё

sql select
  a1.cGroupMc cGroupMc, a1.kMC kMC, a1.sMC sMC, a1.cMC cMC, a1.cPodr cPodr
  , a1.InEkspl InEkspl
  , sum(SumOb) SumOb
  , sum(Kol)Kol
  from
  (
  select
  mbpin.InEkspl InEkspl
  , katmc.cGroupMc cGroupMc
  , katmc.BarKod kMC
  , katmc.name sMC
  , katmc.nrec cMC
  , mbpin.cpodr cPodr
  , MBPIN.SUMMA - MBPIN.SUMMAISNP - MBPIN.SUMMAISNN SumOb
  , mbpin.Koldoc Kol
  from mbpin inner join katmbp on (mbpin.cmbp = katmbp.nrec) and (mbpin.din <=: w_dEnd)
  inner join katmc on (katmbp.cmc = katmc.nrec)
  union all
  select
  mbpin.InEkspl InEkspl
  , katmc.cGroupMc cGroupMc
  , katmc.BarKod kMC
  , katmc.name sMC
  , katmc.nrec cMC
  , mbpin.cpodr cPodr
  , - (MBPOUT.SUMMAISN) SumOb
  , - mbpout.Kol Kol
  from mbpout inner join mbpin on (mbpout.cMBPIn = mbpin.nrec) and (mbpout.dout <=: w_dEnd) and (MBPOUT.STATUS = 1)//начисление износа
  inner join katmbp on (mbpin.cmbp = katmbp.nrec)
  inner join katmc on (katmbp.cmc = katmc.nrec)
  union all
  select
  mbpin.InEkspl InEkspl
  , katmc.cGroupMc cGroupMc
  , katmc.BarKod kMC
  , katmc.name sMC
  , katmc.nrec cMC
  , mbpin.cpodr cPodr
  , - (MBPOUT.Summa - MBPOUT.SUMMAISN) SumOb
  , - mbpout.Kol Kol
  from mbpout inner join mbpin on (mbpout.cMBPIn = mbpin.nrec) and (mbpout.dout <=: w_dEnd) and (MBPOUT.STATUS = 2)//передача в эксплуатацию
  inner join katmbp on (mbpin.cmbp = katmbp.nrec)
  inner join katmc on (katmbp.cmc = katmc.nrec)
  union all
  select
  mbpin.InEkspl InEkspl
  , katmc.cGroupMc cGroupMc
  , katmc.BarKod kMC
  , katmc.name sMC
  , katmc.nrec cMC
  , mbpin.cpodr cPodr
  , - (MBPOUT.Summa - MBPOUT.SUMMAISN) SumOb
  , - mbpout.Kol Kol
  from mbpout inner join mbpin on (mbpout.cMBPIn = mbpin.nrec) and (mbpout.dout <=: w_dEnd) and (MBPOUT.STATUS = 4)//списание
  inner join katmbp on (mbpin.cmbp = katmbp.nrec)
  inner join katmc on (katmbp.cmc = katmc.nrec)
  )a1
  group by a1.cGroupMc, a1.kMC, a1.sMC, a1.cMC, a1.cPodr, a1.InEkspl
  into tmSaldoMBP byname;