0 Replies Latest reply: Nov 30, 2011 5:33 AM by Alexander Gonchar

# Problem calculation stocks- null value where no transaction.

A problem calculatin stocks.

We have a stock table, calculaten from stocks form the first date of month and table of all transactions.

The problem is that we don't have transactions- we have null stock on that date.

The question is how to write dates with null stock with the right stock value- shop-date-stock_value-

is we don't have transaction we want to have the same stock value as previous value.

Calculation stocks:

Writing null value with the same.

The problem is that data loads to long.

Stocks:

sql select *

from 1sbkttl;

Stocks :

sc2 as Код_партии,

if(kind=1,sd) as Sum_Stocks  ,

if(kind=3,sd) as Quantity_Stocks  ,

date as Date_Stocks  ,

sc1 as GOODS,

sc0 as SHOP

Resident Stocks

Where (trim(accid)='3J' and kind=1 and sd>0) or(trim(accid)='3J' and kind=3 and sd>0) and date>=MakeDate(2011,09,01)

Order by sc0,sc1,date;

DROP Table Stocks;

Stocks:

SHOP,

GOODS,

Sum(Quantity_Stocks  ) as Quantity,

SuM(Sum_Stocks  ) as Sum,

Date_Stocks   as Date_stock,

QuarterName(Date_Stocks  ) as квр

Resident Stocks

Group by SHOP,GOODS,Date_Stocks  ;

Drop Table Stocks      ;

проводки:

SQL SELECT *

FROM `1sentry`;

ATP:

if((accdtid='3J' and accktid='57') or(accdtid='3J' and accktid='3Q') or (accdtid='3J' and accktid='3O') ,dtsc0, if((accdtid='8K' and accktid='3J' )or (accdtid='95' andaccktid='3J'), ktsc0)) as SHOP,

if((accdtid='3J' and accktid='57') or(accdtid='3J' and accktid='3Q') or (accdtid='3J' and accktid='3O'),dtsc1,if((accdtid='8K' and accktid='3J' ) or (accdtid='95' and accktid='3J'), ktsc1))as GOODS,

if((accdtid='3J' and accktid='57') or(accdtid='3J' and accktid='3Q') or (accdtid='3J' and accktid='3O' ),sum) as Sum_прихода,

if((accdtid='3J' and accktid='57') or(accdtid='3J' and accktid='3Q') or (accdtid='3J' and accktid='3O') ,amount) as Кол_приход,

if((accdtid='8K' and accktid='3J') or(accdtid='95' and accktid='3J') ,sum) as Sum_расхода,

if((accdtid='8K' and accktid='3J') or(accdtid='95' and accktid='3J') ,amount) as Кол_расхода,

date as Date,

QuarterName(date) as квр

Resident проводки

where (accktid='57' and accdtid='3J' or (accdtid='8K'and accktid='3J') or (accdtid='95' and accktid='3J') or  (accdtid='3J' andaccktid='3Q') or (accdtid='3J' and accktid='3O' )) anddate>=MakeDate(2011,09,01);

drop Table проводки;

b:

SHOP,

GOODS,

Sum_прихода,

Кол_приход,

Sum_расхода,

Кол_расхода,

Date

Resident ATP

Order by SHOP,GOODS,Date;

DROP Table ATP;

a:

NoConcatenate

SHOP,

GOODS,

Date,

Sum(Sum_прихода) as Plus,

sum(Sum_расхода) as Minus,

Sum(if(IsNull(Кол_приход),'0.000',Кол_приход)) as Plus_quantity,

Sum(if(IsNull(Кол_расхода),'0.000',Кол_расхода)) as Minus_quantity

Resident b

Group by SHOP,GOODS,Date;

DROP Table b;

Right join (a)

Date

Resident Calendar;

left Join(Stocks)

GOODS,

SHOP,

QuarterName(Date) as квр,

Plus,

Minus,

Plus_quantity,

Minus_quantity,

Date

Resident a

Order by SHOP,GOODS,Date;

DROP Table a;

обороты:

Date,

[Date] as DateЗ ,

[SHOP]

,[GOODS]

,[Plus_quantity]

,[Minus_quantity]

,[Quantity],

Plus,

Minus,

квр

, if (   Previous([GOODS])=[GOODS]

and Previous([SHOP])=[SHOP]

andPrevious(квр)=квр

,

rangesum(num(Peek(Stock_quantity)),num([Plus_quantity]),-Num([Minus_quantity])),

rangesum(Num([Quantity]), Num([Plus_quantity]),-Num([Minus_quantity])))as Stock_quantity

,   if (   Previous([GOODS])=[GOODS]

and Previous([SHOP])=[SHOP]

and Previous(квр)=квр

,

rangesum(num(peek([Stock])), Minus,-Plus),

rangesum([Sum], Plus,-Minus)) as [Stock],

RowNo() as н

resident Stocks

order by [SHOP],[GOODS] ,Date;

Drop tables Stocks;

LET n=NoOfRows('обороты');

FOR i = 1 TO \$(n)

let date= Peek('ДатаЗ', \$(i),'обороты') ;

let zap=peek('Запас_кол', \$(i),'обороты');

let proj=peek('Код_склада',\$(i),'обороты');

let proj1=peek('Код_склада',\$(i)-1,'обороты');

let t=peek('Код_товара',\$(i),'обороты');

let t1=peek('Код_товара',\$(i)-1,'обороты');

let d=day(peek('ДатаЗ', \$(i),'обороты'))-day(peek('ДатаЗ', \$(i)-1,'обороты'));

//  if peek('Код_склада', \$(i),'обороты')='6' and peek('Код_товара', \$(i),'обороты')='1BD' then

if (peek('Код_склада', \$(i),'обороты')=peek('Код_склада', \$(i)-1,'обороты')) and (Peek('Код_товара', \$(i),'обороты')=Peek('Код_товара', \$(i)-1,'обороты')) and ((day(Peek('ДатаЗ', \$(i),'обороты'))-day(Peek('ДатаЗ', \$(i)-1,'обороты'))>1)and (Month(Peek('ДатаЗ', \$(i),'обороты'))=Month(Peek('ДатаЗ', \$(i)-1,'обороты')))) then

for j=1 to day(Peek('ДатаЗ', \$(i),'обороты'))-day(Peek('ДатаЗ', \$(i)-1,'обороты'))-1

let date=MakeDate(Year(date),num(Month(date)),day(date)+1);

Concatenate(обороты)

\$(date),\$(zap),0,0,\$(proj1),\$(t1)];

NEXT

endif

endif

NEXT