local ldStartDate, ldEndDate, lcSclad,lcKli

ldStartDate = date()- 10 
ldEndDate = date()
lcSclad = '000001'
lcKli='000051'

select
      l.postav as postav,
      l.sgrup as grup,
      l.snnum as nnum,
      l.sartic as artic,
      l.ed as ed,
      t.start_kol as start_kol,
      l.rskolout as kolout,
      l.rssumout as sumout
      from
(select
      ml.marka as postav,
      ml.grup as sgrup,
      ml.nnum as snnum,
      ml.name as sartic,
      ml.ed as ed,
      Round(SUM(IIF(md.vid ='2' and md.type ='1',md.kolout, 0 )), 3 )+Round(SUM(IIF(md.vid ='1' and md.type ='5',-md.kolout, 0 )), 3 ) as rskolout,
      Round(SUM(IIF(md.vid ='2' and md.type ='1',md.sumout, 0 )), 3 )+Round(SUM(IIF(md.vid ='1' and md.type ='5',-md.sumout, 0 )), 3 ) as rssumout
from sclad_mlabel ml
inner join sclad_mdocm md on ml.grup = md.grup and ml.nnum=md.nnum
where md.date between ldStartDate and ldEndDate and Substr(md.agentcode, 1 , 6 )=lcKli and md.sclad = lcSclad and md.schet_a like '361*'and len(md.agentcode)> 6  and md.sclad not in ('000010', '000021', '000022', '000023')
group by ml.marka, ml.grup,ml.nnum,ml.name,ml.ed) as l,
(select
 mkt.sclad as ksclad,
 mkt.grup as kgrup,
 mkt.nnum as knnum,
 mkt.kol0+Round(SUM(IIF(mdm.vid ='1',mdm.kolout, 0 )), 3 )-Round(SUM(IIF(mdm.vid ='2',mdm.kolout, 0 )), 3 ) as start_kol
from sclad_mkart mkt
inner join sclad_mdocm mdm on mkt.grup = mdm.grup and mkt.nnum=mdm.nnum and mdm.sclad=mkt.sclad
where mdm.date < ldStartDate and Substr(mdm.agentcode, 1 , 6 )=lcKli and mdm.sclad = lcSclad and mdm.schet_a like '361*and len(mdm.agentcode)>6 and mdm.sclad not in (' 000010 ', ' 000021 ', ' 000022 ', ' 000023 ')
group by mkt.kol0,mkt.sclad,mkt.grup,mkt.nnum) as t

where l.sgrup=t.kgrup and l.snnum=t.knnum

local ldStartDate, ldEndDate, lcSclad,lcKli

ldStartDate = date()- 10 
ldEndDate = date()
lcSclad = '000002'
lcKli='000258'

select ;
      l.postav as postav, ;
      l.sgrup as grup, ;
      l.snnum as nnum, ;
      l.sartic as artic, ;
      l.ed as ed, ;
      t.start_kol as start_kol, ;
      l.rskolout as kolout, ;
      l.rssumout as sumout ;
      from ;
(select ;
      ml.marka as postav, ;
      ml.grup as sgrup, ;
      ml.nnum as snnum, ;
      ml.name as sartic, ;
      ml.ed as ed, ;
      Round(SUM(IIF(md.vid ='2' and md.type ='1',md.kolout, 0 )), 3 )+Round(SUM(IIF(md.vid ='1' and md.type ='5',-md.kolout, 0 )), 3 ) as rskolout, ;
      Round(SUM(IIF(md.vid ='2' and md.type ='1',md.sumout, 0 )), 3 )+Round(SUM(IIF(md.vid ='1' and md.type ='5',-md.sumout, 0 )), 3 ) as rssumout ;
from sclad_mlabel ml ;
inner join sclad_mdocm md on ml.grup = md.grup and ml.nnum=md.nnum ;
where md.date between ldStartDate and ldEndDate and Substr(md.agentcode, 1 , 6 )=lcKli and md.sclad = lcSclad and md.schet_a like '%361%'and len(md.agentcode)> 6  and (not INLIST(md.sclad'000010', '000021', '000022', '000023')) ;
group by ml.marka,ml.grup,ml.nnum,ml.name,ml.ed) as l, ;
(select ;
 	mkt.sclad as ksclad, ;
 	mkt.grup as kgrup, ;
 	mkt.nnum as knnum, ;
 	mkt.kol0+Round(SUM(IIF(mdm.vid ='1',mdm.kolout, 0 )), 3 )-Round(SUM(IIF(mdm.vid ='2',mdm.kolout, 0 )), 3 ) as start_kol ;
from sclad_mkart mkt ;
inner join sclad_mdocm mdm on mkt.grup = mdm.grup and mkt.nnum=mdm.nnum and mdm.sclad=mkt.sclad ;
where mdm.date <ldStartDate and mdm.sclad = cSclad ;
group by mkt.sclad,mkt.grup,mkt.nnum,mkt.kol0) as t ;
where l.sgrup=t.kgrup and l.snnum=t.knnum

CREATE TABLE [mlabel]
(
[grup]		CHAR		( 5 ),
[nnum]		CHAR		( 13 ),
[name]		CHAR		( 60 ),
[ED]		CHAR		( 5 ),
[cena]		NUMERIC		( 19 , 8 ),
[cena0]		NUMERIC		( 19 , 8 ),
[date]		DATE,
[norma]		NUMERIC		( 19 , 4 ),
[odate]		DATE,
[ocena1]	NUMERIC		( 19 , 8 ),
[vcena1]	NUMERIC		( 19 , 8 ),
[ocena2]	NUMERIC		( 19 , 8 ),
[vcena2]	NUMERIC		( 19 , 8 ),
[ocena3]	NUMERIC		( 19 , 8 ),
[vcena3]	NUMERIC		( 19 , 8 ),
[ocena4]	NUMERIC		( 19 , 8 ),
[vcena4]	NUMERIC		( 19 , 8 ),
[codeval]	CHAR		( 3 ),
[cenaval]	NUMERIC		( 13 , 5 ),
[nacen]		NUMERIC		( 10 , 4 ),
[marka]		CHAR		( 25 ),
[sort]		CHAR		( 25 ),
[profil]	CHAR		( 25 ),
[razmer]	CHAR		( 25 ),
[koef]		NUMERIC		( 14 , 4 ),
[ed1]		CHAR		( 5 ),
[ed2]		CHAR		( 5 ),
[ed3]		CHAR		( 5 ),
[algo1]		CHAR		( 40 ),
[algo2]		CHAR		( 40 ),
[algo3]		CHAR		( 40 ),
[skid]		CHAR		( 4 ),
[skidq]		CHAR		( 4 ),
[kol1]		NUMERIC		( 19 , 4 ),
[kol2]		NUMERIC		( 19 , 4 ),
[kol3]		NUMERIC		( 19 , 4 ),
[kol4]		NUMERIC		( 19 , 4 ),
[kol5]		NUMERIC		( 19 , 4 ),
[skidka1]	NUMERIC		( 5 , 2 ),
[skidka2]	NUMERIC		( 5 , 2 ),
[skidka3]	NUMERIC		( 5 , 2 ),
[skidka4]	NUMERIC		( 5 , 2 ),
[skidka5]	NUMERIC		( 5 , 2 ),
[status]	CHAR		( 1 ),
[ins]		CHAR		( 1 ),
[stat]		CHAR		( 1 ),
[r1]		NUMERIC		( 14 , 5 ),
[r2]		NUMERIC		( 14 , 5 ),
[r3]		NUMERIC		( 14 , 5 ),
[koef1]		NUMERIC		( 14 , 4 ),
[cod_okdp]	CHAR		( 250 ),
[sclads]	INT,
[nnoper]	NUMERIC		( 10 , 0 ),
[n_r]		NUMERIC		( 1 , 0 ),
[n_p]		NUMERIC		( 1 , 0 ),
[oper]		CHAR		( 22 ),
[cenan]		NUMERIC		( 19 , 8 ),
[cena0n]	NUMERIC		( 19 , 8 ),
[daten]		DATE,
[wrk_cl]	CHAR		( 1 ),
[pereoc_d]	DATE,
[damso]		DATE,
[damson]	DATE,
[cdattr1]	CHAR		( 30 ),
[cdattr2]	CHAR		( 30 ),
[cdattr3]	CHAR		( 30 ),
[cdattr4]	CHAR		( 30 ),
[ndattr1]	NUMERIC		( 19 , 4 ),
[ndattr2]	NUMERIC		( 19 , 4 ),
[id_label]	CHAR		( 22 )
)

create table mkart
(
sclad		char		( 6 ),
grup		char		( 5 ),
nnum		char		( 13 ),
prim		char		( 15 ),
kol0		numeric		( 19 , 4 ),
koltek		numeric		( 19 , 4 ),
kolres0		numeric		( 19 , 4 ),
kolres		numeric		( 19 , 4 ),
kollim		numeric		( 19 , 4 ),
kol_wd		numeric		( 19 , 4 ),
schet		char		( 4 ),
[name]		char		( 34 ),
[status]	char		( 1 ),
price		char		( 1 ),
mesto1		char		( 10 ),
mesto2		char		( 10 ),
partia		char		( 5 ),
[datepart]	date,
nnoper		numeric		( 10 , 0 ),
marka		char		( 25 ),
sort		char		( 25 ),
profil		char		( 25 ),
razmer		char		( 25 )
)