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

    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:

      LOAD * Where date>=MakeDate(2011,09,01);

      sql select *

      from 1sbkttl;

      Stocks :

      LOAD

          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:

      LOAD

          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:

      LOAD

         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:

      LOAD

      SHOP,

      GOODS,

      Sum_прихода,

      Кол_приход,

      Sum_расхода,

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

      Date

      Resident ATP

      Order by SHOP,GOODS,Date;

      DROP Table ATP;

      a:

      NoConcatenate

      LOAD

          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)

      load

        Date

      Resident Calendar;

      left Join(Stocks)

      LOAD

          GOODS,

          SHOP,

          QuarterName(Date) as квр,

          Plus,

          Minus,

          Plus_quantity,

          Minus_quantity,

          Date

      Resident a

      Order by SHOP,GOODS,Date;   

      DROP Table a;

      обороты:

      LOAD

        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;

       

       

       

       

      Script adding values instead of nulls

       

      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(обороты)

                LOAD * INLINE [Дата,Запас_кол,Приход_кол,Расход_кол,Код_склада,Код_товара

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

           NEXT      

          endif

          endif

          NEXT