Grab oldest Date

Transact-SQL
1

I have a trx table that records issues and receipts into a bin. I'm trying to find a way to find the bin that has a qty with the oldest receipt date.

Data will look like this:  R = Receipt   I = Issue     tablename: QIVBinTrx
     item_no      Bin_no     Qty               date                         doc_type
     BTEST        BINA      20            2015-07-01                        R
     BTEST        BINA       6             2015-07-02                         I 
     BTEST        BINA       14           2015-07-03                         I
     BTEST        BINA       10           2015-07-04                        R
     BTEST        BINA        5            2015-07-05                        R

What I'm looking for is the last receipt date that filled this bin.
The Bin was fully issued on 07/03/2015 which brought it to zero.
Then a receipt was done the next day for 10. I need to know the last receipt date in that bin was 07/04/2015

Then if it get zeroed again I would need it to find the next receipt date that started to fill it again.

What I would like returned is:
Item BIN QTY Last Receipt Date
BTEST BINA 15 2014-07-04

I know I can get the qty by
select item_no, bin_no, SUM(qty) as BinQty
from QIVBinTrx
group by item_no, bin_no

Not sure if there is a way to get the date I want.

2

Option 1 take records after latest "issued" and shows sum of qty and earliest date:

select b.item_no
      ,b.Bin_no
      ,sum(b.Qty) as Qty
      ,min(b.[date]) as [date]
  from (select item_no
              ,Bin_no
              ,max([date]) as [date]
          from QIVBinTrx
         where doc_type='I'
         group by item_no
                 ,Bin_no
       ) as a
       inner join QIVBinTrx as b
               on b.item_no=a.item_no
              and b.Bin_no=a.Bin_no
              and b.[date]>a.[date]
 group by b.item_no
         ,b.Bin_no
 order by b.item_no
         ,b.Bin_no

Option 2 takes records after sum of qty reaches 0 and shows sum of qty and earliest date:

select b.item_no
      ,b.Bin_no
      ,sum(b.Qty) as Qty
      ,min(b.[date]) as [date]
  from (select a.item_no
              ,a.Bin_no
              ,a.[date]
              ,row_number() over(partition by a.item_no,a.Bin_no order by a.[date] desc) as rn
          from QIVBinTrx as a
               left outer join QIVBinTrx as b
                            on b.item_no=a.item_no
                           and b.Bin_no=a.Bin_no
                           and b.[date]<a.[date]
         group by a.item_no
                 ,a.Bin_no
                 ,a.[date]
         having sum(isnull(case when b.doc_type='R' then 1 else -1 end*b.Qty,0))=0
       ) as a
       inner join QIVBinTrx as b
               on b.item_no=a.item_no
              and b.Bin_no=a.Bin_no
              and b.[date]>=a.[date]
 where a.rn=1
 group by b.item_no
         ,b.Bin_no
 order by b.item_no
         ,b.Bin_no
1 Like
3

The first one doesn't quite work. Pulling very old dates and not calculating Qty's correctly.

If I just do a

select item_no, bin_no, sum(qty) as Qty
from qivbintrx
group by item_no, bin

I get the correct qty on hand for each bin. So I think my table has correct data.

4

Looks like the 2nd one is working.