select the lastest invoiced date from each customer invoice, with multiple invoices from same customer

Question

I just need it to pull the top 'last invoiced date' from the table not everything. any idea how to do that? Or could anyone point me in the right direction. I am a novice to sql queries. I'm using MS sql sever 2016.

Vendor_Number   Vendor_Name       VendorInvoiceDate     DaysLastInvoiced
    3568    a INDUSTRIES INC    2016-11-12 07:02:41.743   165
    3568    a INDUSTRIES INC    2016-10-08 07:03:31.170   200
    3568    a INDUSTRIES INC    2016-10-06 07:09:46.880   202
    3568    a INDUSTRIES INC    2016-09-21 07:04:30.267   217
    3568    a INDUSTRIES INC    2016-04-26 07:04:27.570   365
    3215    o Outdoors, Inc.    2016-03-02 07:39:30.137   420
    3215    o Outdoors, Inc.    2016-02-25 07:24:04.207   426
3215    o Outdoors, Inc.    2016-02-22 07:02:51.787 429

enter code here

Select distinct
      REF02_VR as Vendor_Number
    , N102_RI as Vendor_Name
    , VendorInvoiceDate
    , DATEDIFF(day,(select VendorInvoiceDate),Getdate()) AS DaysLastInvoiced
From 
     INSCIO_EDI810 a
WHERE
     1 = 1   
and 
    a.VendorInvoiceDate >= '2016-01-01 00:37:21.370'
order by
    N102_ri, a.VendorInvoiceDate desc

Please include how the output should looks like. – Juan Carlos Oropeza Apr 26 '17 at 20:29 — Juan Carlos Oropeza, Apr 26 '17 at 20:29

SqlZim · Answer 1 · 2017-05-12T16:39:44.523

Using top with ties with row_number() to get the latest invoice for each vendor:

select top 1 with ties
    ref02_vr as Vendor_Number
  , N102_ri  as Vendor_Name
  , VendorInvoiceDate
  , datediff(day,VendorInvoiceDate,getdate()) as DaysLastInvoiced
from inscio_edi810 a
where a.VendorInvoiceDate >= '2016-01-01 00:37:21.370'
order by row_number() over (partition by ref02_vr order by VendorInvoiceDate desc)

To add an additional order by put the above in a subquery:

select *
from (
  select top 1 with ties
      ref02_vr as Vendor_Number
    , N102_ri  as Vendor_Name
    , VendorInvoiceDate
    , datediff(day,VendorInvoiceDate,getdate()) as DaysLastInvoiced
  from inscio_edi810 a
  where a.VendorInvoiceDate >= '2016-01-01 00:37:21.370'
  order by row_number() over (partition by ref02_vr order by VendorInvoiceDate desc)
  ) s
order by DaysLastInvoiced desc

with common_table_expression as () using row_number() version:

;with cte as (
  select  *
      , rn = row_number() over (
              partition by ref02_vr
              order by VendorInvoiceDate desc
            )
    from inscio_edi810
)
select 
    ref02_vr as Vendor_Number
  , N102_ri  as Vendor_Name
  , VendorInvoiceDate
  , datediff(day,VendorInvoiceDate,getdate()) as DaysLastInvoiced
from cte
where rn = 1
order by DaysLastInvoiced desc

cross apply version:

select distinct
   a.ref02_vr as Vendor_Number
 , a.N102_ri  as Vendor_Name
 , x.VendorInvoiceDate
 , datediff(day,x.VendorInvoiceDate,getdate()) as DaysLastInvoiced
from inscio_edi810 a
  cross apply (
    select top 1 VendorInvoiceDate
      from inscio_edi810 as i
      where i.ref02_vr =a.ref02_vr
      order by i.VendorInvoiceDate desc
     ) as x;
order by DaysLastInvoiced desc

rextester demo for each of the three version: http://rextester.com/UBX8146

each returns:

+---------------+------------------+---------------------+------------------+
| Vendor_Number |   Vendor_Name    |  VendorInvoiceDate  | DaysLastInvoiced |
+---------------+------------------+---------------------+------------------+
|          3568 | a INDUSTRIES INC | 2016-11-12 07:02:41 |              165 |
|          3215 | o Outdoors, Inc. | 2016-03-02 07:39:30 |              420 |
+---------------+------------------+---------------------+------------------+

@Jenesis Updated to add `order by DaysLastInvoiced desc` for each version. — SqlZim, May 12 '17 at 16:40

select the lastest invoiced date from each customer invoice, with multiple invoices from same customer

1 Answers1