Update transaction record with FIFO in SQL

Question

I have an application for receiving and shipping. I am handling these 2 processes in the transaction table. While receiving the item manually giving BOE number, so all receiving record having BOE number. But while shipping out an item am not updating this BOE column. Actually I need to know this Item came from which BOE (I want to use FIFO) criteria. Now my transaction table has a lot of data. But I want to know each BOE how many item came and how many item out.

I have made query like this

select 
    F_lot_number, sum(INq) 'IN', sum(OUTs) 'OUT', sum(BOE) 'BOEcount' 
from 
    (select 
         F_lot_number,
         case 
            when F_Stock_Type = 'IN' then sum(F_qty) 
         end as 'INq',
         case 
            when F_Stock_Type = 'out' and  F_IsBook = '2' then sum(F_qty)  
         end as 'OUTs',
         case 
            when F_Stock_Type = 'IN' 
               then count(distinct F_BillOf_entryNumber) 
         end as 'BOE'
     from 
         T_Tra_Transaction 
     group by 
         F_lot_number, F_Stock_Type, F_IsBook) d 
group by 
    F_lot_number

Output looks like this:

if example

select 
    F_QTY, F_lot_number, F_BillOf_entryNumber 
from 
    T_Tra_Transaction 
where 
    F_lot_number = '150723A151' 
    and F_Stock_Type = 'IN'

select 
    F_QTY, F_lot_number, F_BillOf_entryNumber 
from 
    T_Tra_Transaction 
where 
    F_lot_number = '150723A151' 
    and F_Stock_Type = 'out'

Output looks like this:

I need to update all out status with corresponding BOE number. How can I do that? i need to updated all of my existing recordes according to that.if any help am very thank full

In my table i have transdatetime field also there.

i have checked other lot number..

select 
    F_QTY, F_lot_number, F_BillOf_entryNumber 
from 
    T_Tra_Transaction 
where 
    F_lot_number = '150919C131' 
    and F_Stock_Type = 'IN'

select 
    F_QTY, F_lot_number, F_BillOf_entryNumber 
from 
    T_Tra_Transaction 
where 
    F_lot_number = '150919C131' 
    and F_Stock_Type = 'out'

but am getting out put like this:

In first image its showing Lot number,IN qty,Out Qty,Boe number count — user3262364, Jan 22 '17 at 07:50
dear @marc_s..did you removed my out put images from question? — user3262364, Jan 22 '17 at 08:00
Possible duplicate of [How to implement FIFO in sql](http://stackoverflow.com/questions/25152990/how-to-implement-fifo-in-sql) — Serg, Jan 22 '17 at 08:43
dear @Serg ,,my condition is not same..i want to update my all existing records,,,according to i explained? — user3262364, Jan 22 '17 at 08:52
Please add Transactions table schema. is there any datetime, timestamp o r sequenced value in this table? — McNets, Jan 22 '17 at 08:58
The referred answer explains FIFO. You need to decide which way you need to modify your data according to FIFO results. What if 150723A151 "ins" are (10,10) and "outs" are (5,7,3) for example? — Serg, Jan 22 '17 at 08:59
@serg 150723A151 (10,10) ..that particular lot number 10 qty came and 10 qty out — user3262364, Jan 22 '17 at 10:10
Then it looks like FIFO groups are identified by `Lot number` and `qty` contrary to original FIFO which groups data just by `Lot number`. — Serg, Jan 22 '17 at 10:20
@serg ..how i can achieve this? is there any way? any help is very appreciable..in my table i have transdatetime field also there — user3262364, Jan 22 '17 at 10:40
150919C131 data you added contradict to what you said before, in/out lots are not the same size. You really want FIFO. Try read the topic carefully http://stackoverflow.com/questions/25152990/how-to-implement-fifo-in-sql — Serg, Jan 22 '17 at 12:37
My in and out are not the same..in that case how i can update then? — user3262364, Jan 22 '17 at 12:41

Serg · Answer 1 · 2017-01-22T12:27:32.517

0

I assume that every lot is always sold with the same qty it was acquired. So the idea is just row_number() ins and outs properly and update outs from corresponding ins. Not tested.

update tout
set F_BillOf_entryNumber = tin.F_BillOf_entryNumber 
from (
    select F_QTY, F_lot_number, F_BillOf_entryNumber 
    , rn = row_number() over (partition by F_QTY, F_lot_number order by transdatetime desc)
    from 
        T_Tra_Transaction 
    where 
        F_Stock_Type = 'out'
    ) tout
join (
    select 
        F_QTY, F_lot_number, F_BillOf_entryNumber
        , rn = row_number() over (partition by F_QTY, F_lot_number order by transdatetime desc)
    from 
        T_Tra_Transaction 
    where 
        F_Stock_Type = 'IN'
    ) tin 
    on tout.F_QTY = tin.F_QTY
       and tout.F_lot_number = tin.F_lot_number
       and tout.rn = tin.rn;

EDIT

I tested it on sample data

create table T_Tra_Transaction(
F_QTY int, F_lot_number int , F_BillOf_entryNumber int,
     transdatetime int, F_Stock_Type varchar(3));
insert  T_Tra_Transaction (F_QTY, F_lot_number, F_BillOf_entryNumber,transdatetime,F_Stock_Type)
values
(10,1,101,200,'IN'),
(20,1,102,201,'IN'),
(20,1,null,200,'out');

edited Jan 22 '17 at 12:27

answered Jan 22 '17 at 11:07

Serg

22,285
5
21
48

Invalid column name 'transdatetime'. – user3262364 Jan 22 '17 at 11:15
I take it from your comment "i have transdatetime field also there". Correct it according to your real schema. – Serg Jan 22 '17 at 11:27
i wan to update all out shipment lot number with corresponding BOE number – user3262364 Jan 22 '17 at 11:42
Provide sample data so we can see the problem. – Serg Jan 22 '17 at 11:45
sir how i can give my data to you,,,? – user3262364 Jan 22 '17 at 11:57
if you provide any mail or somthing i will send to you across – user3262364 Jan 22 '17 at 11:57
This looks like a request for a professional service. :) You can get the answer for free if you publish some samples, obfuscating or excluding sensitive data here. – Serg Jan 22 '17 at 12:14
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/133739/discussion-between-user3262364-and-serg). – user3262364 Jan 22 '17 at 12:19
still confused how i can give sample data here..thatsy i asked sir,, – user3262364 Jan 22 '17 at 12:21
See edit You may provide it the same way. Change data types as needed. – Serg Jan 22 '17 at 12:28

Update transaction record with FIFO in SQL

1 Answers1