Somebody Somebody - 3 months ago 14
SQL Question

Getting MAX date from two tables after INNER JOIN

I have below tables:

declare @tbl1 table (Id1 int, crtdate datetime, InvcNbr varchar(10), ShipperId varchar(10), InvtId varchar(10))
insert into @tbl1 values (1,'01/01/2016','001','S111','111')
insert into @tbl1 values (2,'01/10/2016','002','S111','111')
insert into @tbl1 values (3,'01/02/2016','003','S112','112')
insert into @tbl1 values (4,'01/08/2016','004','S112','112')
insert into @tbl1 values (5,'01/04/2016','005','S113','113')
insert into @tbl1 values (6,'01/05/2016','006','S114','113')

declare @tbl2 table (Id2 int, SerialNo varchar(10), InvcNbr varchar(10), ShipperId varchar(10), InvtId varchar(10))
insert into @tbl2 values (1,'1111111','001','S111','111')
insert into @tbl2 values (2,'1111111','002','S111','111')
insert into @tbl2 values (3,'1111112','003','S112','112')
insert into @tbl2 values (4,'1111112','004','S112','112')
insert into @tbl2 values (5,'1111113','005','S113','113')
insert into @tbl2 values (6,'1111113','006','S114','113')


These two tables are related by fields:
InvcNbr, ShipperId and InvtId


Serials from
@tbl2
are present in two different invoices (
InvcNbr
). How to show only the results from the latest
InvcNbr


The result should be like this:

Id1 crtdate InvcNbr ShipperId InvtId Id2 SerialNo InvcNbr ShipperId InvtId
2 2016-01-10 00:00:00.000 002 S111 111 2 1111111 002 S111 111
4 2016-01-08 00:00:00.000 004 S112 112 4 1111112 004 S112 112
6 2016-01-05 00:00:00.000 006 S114 113 6 1111113 006 S114 113

Answer
with lastInvoices as (
        select SerialNo, MAX(InvcNbr) LastInvcNbr
        from @tbl2
        group by SerialNo
    )
select t1.*,t2.*
from lastInvoices li
    join @tbl2 t2 on (li.SerialNo = t2.SerialNo and li.LastInvcNbr=t2.InvcNbr)
    join @tbl1 t1 on (t1.InvcNbr = t2.InvcNbr and t1.ShipperId = t2.ShipperId and t1.InvtId = t2.InvtId)
Comments