Results 1 to 8 of 8

Thread: FIFO in SQL

  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unanswered: FIFO in SQL

    Hi... I have another problem... I want to combine these two tables

    Sales:
    A - 70pcs
    B - 30pcs

    Buying:
    10 feb 2003 A, supplierA, 60pcs
    10 feb 2003 B, supplierB, 40pcs
    13 feb 2003 B, supplierA, 10pcs
    13 feb 2003 A, supplierB, 30pcs


    into these tables
    Stock:
    A, SupplierB, 20 pcs
    B, SupplierA, 10 pcs
    B, SupplierB, 10 pcs

    and:
    Sales:
    A, SupplierA, 60 pcs
    A, SupplierB, 10 pcs
    B, SupplierB, 30 pcs

    How I can achieve that result? I am completely lost in this... thanks..

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    I tried to use clear sql ...

    Code:
    --Timeline must be unique !!! 
    alter table Buying add constraint UK_Buying unique (Item,"Date")
    
    select 
     Item    =coalesce(xxx.Item,xxy.Item)
    ,Supplier=coalesce(xxx.Supplier,xxy.Supplier)
    ,Pieces  =isnull(sum(xxx.Pieces),0)+isnull(sum(xxy.Pieces),0)
    INTO STOCK
    from
    (
     select b.Item,b.Supplier,Pieces=x.Pieces-isnull(s.Pieces,0) from
     (
       select b2.Item,b2."Date",Pieces=sum(b1.Pieces)
       from Buying b1
       join Buying b2 on b1.Item=b2.Item and b1."Date"<=b2."Date" 
       left join Sales  s on b2.Item=s.Item
       group by b2.Item,b2."Date"
     ) x join
     (
      select x.Item,StopDate=min("Date") from 
      (
       select b2.Item,b2."Date",Pieces=sum(b1.Pieces)
       from Buying b1
       join Buying b2 on b1.Item=b2.Item and b1."Date"<=b2."Date" 
       left join Sales  s on b2.Item=s.Item
       group by b2.Item,b2."Date"
      ) x
      left join Sales  s on x.Item=s.Item
      where x.Pieces>=isnull(s.Pieces,0)
      group by x.Item
     ) xx on x.Item=xx.Item and x."Date"=xx.StopDate 
     join Buying b on xx.Item=b.Item and xx.StopDate=b."Date"
     left join Sales s on x.Item=s.Item and x.Pieces>s.Pieces
    ) xxx
    full join
    (
     select b.Item,b.Supplier,Pieces=sum(b.Pieces)
     from Buying b join 
     (
      select x.Item,StopDate=min("Date") from 
      (
       select b2.Item,b2."Date",Pieces=sum(b1.Pieces)
       from Buying b1
       join Buying b2 on b1.Item=b2.Item and b1."Date"<=b2."Date" 
       left join Sales  s on b2.Item=s.Item
       group by b2.Item,b2."Date"
      ) x
      left join Sales  s on x.Item=s.Item
      where x.Pieces>=isnull(s.Pieces,0)
      group by x.Item
     ) xx on b.Item=xx.Item and b."Date">xx.StopDate 
     group by b.Item,b.Supplier
    ) xxy on xxx.Item=xxy.Item and xxx.Supplier=xxy.Supplier
    where (xxx.Item is not null) or (xxy.Item is not null)
    group by coalesce(xxx.Item,xxy.Item),coalesce(xxx.Supplier,xxy.Supplier)
    
    select x.Item,x.Supplier,Pieces=x.Pieces-isnull(s.Pieces,0)
    INTO NEWSALES
    from
    (
     select b.Item,b.Supplier,Pieces=sum(b.Pieces)
     from      Buying b
     group by b.Item,b.Supplier
    ) x
    left join STOCK s on x.Item=s.Item and x.Supplier=s.Supplier
    where x.Pieces-isnull(s.Pieces,0)>0
    
    select * from STOCK
    select * from NEWSALES
    Good luck !

  3. #3
    Join Date
    Sep 2002
    Posts
    30

    Cannot put unique date into buying

    I have read the script, and, honestly i only understand the code partially, but I saw one thing, about unique date and sensing the split between supplier between date and time. This cannot be done, because for example, if someone buys 10 pcs at once in one transaction, it is possible to have the split inside those 10 pcs, e.g. 3 pcs was from SupplierA stuff, and the rest came from SupplierB.

    Thx for the code... I'll try it first.. then let you know the result..
    Last edited by prd00; 02-21-03 at 08:43.

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    You must have unique timeline in table Buying.
    What primary key do you use ? ("Date", Item, Supplier) ?

    Is it ordered by pseudo-timeline ?
    Record 1: "Date"=x Item =y Supplier='A'
    Record 2: "Date"=x,Item =y,Supplier='B'
    Means "Record 1" before "Record 2" ?
    Last edited by ispaleny; 02-21-03 at 09:38.

  5. #5
    Join Date
    Sep 2002
    Posts
    30

    Re: Cannot put unique date into buying

    Originally posted by prd00
    I have read the script, and, honestly i only understand the code partially, but I saw one thing, about unique date and sensing the split between supplier between date and time. This cannot be done, because for example, if someone buys 10 pcs at once in one transaction, it is possible to have the split inside those 10 pcs, e.g. 3 pcs was from SupplierA stuff, and the rest came from SupplierB.

    Thx for the code... I'll try it first.. then let you know the result..
    Sorry... I misread it, I thought it was selling that you put Date unique key.. it works, but I still don't understand the logic.. and still trying to look for it.. Another small question, how can I compensate with this script if I add date at Selling, and it also in FIFO, so the stuff sold firstis the stuff bought first? Thx...

  6. #6
    Join Date
    Sep 2002
    Posts
    30
    Originally posted by ispaleny
    You must have unique timeline in table Buying.
    What primary key do you use ? ("Date", Item, Supplier) ?

    Is it ordered by pseudo-timeline ?
    Record 1: "Date"=x Item =y Supplier='A'
    Record 2: "Date"=x,Item =y,Supplier='B'
    Means "Record 1" before "Record 2" ?
    Actually it's unique in ID. When we receive stuff, we made a goods reception note. The primary key is based on those note... The supplier could be 3-4 at once in one day, because if we don't get enough qty, we need to scavenge into some agents to find some small stock left on a couple of suppliers. And worst, this is always happen with high sales stuff, which got depleted pretty fast. And worst, we'd never know which album got sold fast or a dud without first try to sell it. So the resupplying these kind of album always need to scavenge from a couple of another agent and some suppliers and almost impossible to make it only 1 supplier per item per day
    This is our real data

    RM_Jan - Received Master
    ID, HQID, Date, Supplier, UserID

    RD_Jan - Received Detail
    ID, HQID, Code, Qty

    SM_Jan - Sales Master
    ID, BranchID, Date, UserID, Returned

    SD_Jan - Sales Detail
    ID, BranchID, Code, Qty, Disc, Price

    Primary key on HQ is ID and HQID, and primary key on sales outpost is ID and BranchID.
    If it could help, Date recorded in smalldatetime along with time when the transaction happen. I think that will limit the supplier at once, but it can also happen that in the same second, someone else input the very same item but from different supplier on another terminal. Chances are slim, but it could happen.

    I tried your code and it fits fine when there is only one supplier a day per item, but when I add another supplier that day the stock qty got messed up
    Last edited by prd00; 02-21-03 at 12:38.

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Some virus is sucking me out. I am sick and unable to solve it now.
    I tried to create unique timeline, precalculate sum and find breaking recods faster.
    This is working fine, but I also tried to treat the stock underrun and simplify final select.
    I am posting what I have done yet.

    Code:
    select d.Code,m.Supplier,d.Qty,SumQty=cast(NULL as int), IdTimeline=identity(int,1,1)
    into dbo.RM_Jan_Timeline
    from dbo.RM_Jan m
    join dbo.RD_Jan d on m.ID=d.ID and m.HQID=d.HQID 
    order by d.Code,m.Date,m.HQID,m.ID,m.Supplier
    GO
    create unique clustered index i_ForUpdate on dbo.RM_Jan_Timeline(IdTimeline ASC)
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
    begin tran        
    set nocount on
    declare @c cursor
    set @c=cursor fast_forward for select IdTimeline,Qty,Code from dbo.RM_Jan_Timeline order by IdTimeline
    open @c
    declare @IdTimeline int,@Qty int,@Code int,@SumQty int,@OldCode int 
    fetch @c into @IdTimeline, @Qty, @Code
    while @@fetch_status=0 begin
     update dbo.RM_Jan_Timeline set @SumQty=(case when @OldCode=@Code then @SumQty+Qty else Qty end),@OldCode=Code,SumQty=@SumQty
     where IdTimeLine=@IdTimeline
     fetch @c into @IdTimeline, @Qty, @Code
    end close @c deallocate @c
    set nocount off
    commit
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
    GO
    select Code,QtyTotal=sum(Qty)
    into dbo.TotalSales
    from dbo.SD_Jan
    group by Code
    GO
    alter table dbo.TotalSales add primary key (Code)
    GO
    
    select Code=COALESCE(R.Code,S.Code),SumRQty=isnull(R.SumQty,0),SumSQty=isnull(S.SumQty,0) 
      into dbo.StockBalance from
      (
       select Code,SumQty=sum(Qty)
       from dbo.RD_Jan
       group by Code
      ) R
      full join
      (
       select Code,SumQty=sum(Qty)
       from dbo.SD_Jan
       group by Code
      ) S on R.Code=S.Code
      order by Code
    create unique clustered index I on dbo.StockBalance(Code)
    
    --Final select, incorrect
    select Code,Supplier,SaledQty,OnStockQty from
    (
     select Code,Supplier
     ,SaledQty  =case when IsReminder=1     then SalSumQty-(SumRQty-SumSQty) 
                      when Supplier is null then SumSQty-SumRQty
                      when IsReminder=0     then SalSumQty
                 end 
     ,OnStockQty=case when IsReminder=1   then StoSumQty+(SumRQty-SumSQty) 
                    when Supplier is null then -(SumSQty-SumRQty)
                    when IsReminder=0     then StoSumQty
                 end
     from
     (
      select su.Code,L.Supplier
     ,IsReminder=sum(case when MaxId.IdTimeline=L.IdTimeline and SumRQty>SumSQty then 1 else 0 end)
     ,su.SumSQty,su.SumRQty
     ,SalSumQty=sum(case when MaxId.IdTimeline>=L.IdTimeline then L.Qty else 0 end)
     ,StoSumQty=sum(case when MaxId.IdTimeline< L.IdTimeline then L.Qty else 0 end)
      from      dbo.StockBalance    su
      inner join dbo.RM_Jan_Timeline L  on L.Code=su.Code
      left join
      (
       select su.Code,IdTimeline=max(L.IdTimeline) 
       from dbo.StockBalance   su 
       join dbo.RM_Jan_Timeline L  on L.Code=su.Code and L.SumQty<=su.SumSQty
       group by su.Code
      ) MaxId on su.Code=MaxId.Code
      group by su.Code,L.Supplier,su.SumSQty,su.SumRQty
     ) x
     union all
     select Code,Supplier=NULL,SaledQty=SumSQty,OnStockQty=-SumSQty from dbo.StockBalance su where SumRQty<SumSQty
    ) xx
    order by Code,Supplier
    Maybe, you will be more lucky.

  8. #8
    Join Date
    Feb 2008
    Posts
    4
    I suggest to process FIFO method, better to use a stack buffer. So when is a receipt go on adding. if there is an issue deduct from there and update the balance. pls note your need two columns two maintain a stack (quantity, rate).

    Suppose if the issuance is higher for example 10 issuance. check your first stack location taken the balance receipt quantity and rate and update. Then check the next receipt quantity, rate.

    Stack

    Qty Rate
    10 2.3
    12 3.4

    issuance

    8 Take 8 from stack first location and keep balance 2.
    6 Taken 2 from stack first location and take 4 from second location.

    so whenever you take quantity from stack add and find out the average of issuance quantity.

    in fact costing is a costly process. it is better to program through component rather than going with stored procedures.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •