2021-11-29

How to select next MAX() date in a partition without getting duplicates?

My goal is to select the closest document to each invoice with these conditions:

  1. DocumentDate must be before the InvoiceDate
  2. Any DocumentDate cannot be selected more than once (ordered by InvoiceDate DESC)

My first table contains clients and all their invoices. My second table contains a list of document ids and corresponding dates for each client. Please see the queries I have tried below and share your suggestions!

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9c49ec4ea9b22bbd4a2eb816407d708f

Data:

Invoices:

clientid    invoiceid   invoicedate
18924       2819        2019-01-17
18924       4524        2019-01-15
18924       9897        2018-12-31
18924       1591        2018-12-25
27113       5808        2020-10-16
27113       4359        2020-10-11
27113       3405        2020-10-01
27113       9889        2020-09-21
27113       1976        2020-09-19

Documents:

clientid    documentid  documentdate
18924       2851        2019-01-27
18924       2500        2019-01-25
18924       9979        2019-01-12
18924       8913        2019-01-06
18924       3363        2019-01-02
27113       9533        2020-10-14
27113       9525        2020-10-12
27113       9521        2020-09-25
27113       8680        2020-09-11
27113       3504        2020-09-03
27113       5188        2020-08-17

Desired Output:

clientid    invoiceid   invoicedate     documentid  documentdate
18924       2819        2019-01-17      9979        2019-01-12
18924       4524        2019-01-15      8913        2019-01-06
18924       9897        2018-12-31
18924       1591        2018-12-25
27113       5808        2020-10-16      9533        2020-10-14
27113       4359        2020-10-11      9521        2020-09-25
27113       3405        2020-10-01      8680        2020-09-11
27113       9889        2020-09-21      3504        2020-09-03
27113       1976        2020-09-19      5188        2020-08-17

What I've Tried:

When I use this query, it doesn't constrain the documentdate to being chosen only once.

SELECT
    invoice.clientid,
    invoiceid,
    invoicedate,
    MAX(documentdate) 'documentdate'
FROM Invoice
    LEFT JOIN Document ON Invoice.clientid = Document.clientid
        AND Invoice.invoicedate > Document.documentdate
GROUP BY invoice.clientid,
invoiceid, 
invoicedate
ORDER BY invoice.clientid, 
invoicedate desc
clientid    invoiceid   invoicedate documentdate
18924       2819        2019-01-17  2019-01-12
18924       4524        2019-01-15  2019-01-12
18924       9897        2018-12-31  
18924       1591        2018-12-25  
27113       5808        2020-10-16  2020-10-14
27113       4359        2020-10-11  2020-09-25
27113       3405        2020-10-01  2020-09-25
27113       9889        2020-09-21  2020-09-11
27113       1976        2020-09-19  2020-09-11

This query suggested by user @eshirvana only works as long as every documentdate is chosen.

WITH
    data
    AS
    (
        SElECT
            Invoice.clientid 'clientid',
            invoiceid,
            invoicedate,
            dense_rank() over (partition by Invoice.clientid order by invoicedate desc) 'InvoiceRank',
            documentid,
            documentdate,
            dense_rank() over (partition by Invoice.clientid order by documentdate desc) 'DocumentRank'
        FROM Invoice
            LEFT JOIN Document ON Invoice.clientid = Document.clientid
                AND Invoice.invoicedate > Document.documentdate
    )


SELECT *
FROM data
WHERE InvoiceRank = DocumentRank 
or Documentid is null
ORDER BY clientid , InvoiceRank
clientid    invoiceid   invoicedate InvoiceRank documentid  documentdate    DocumentRank
18924       2819        2019-01-17  1           9979        2019-01-12      1
18924       4524        2019-01-15  2           8913        2019-01-06      2
18924       9897        2018-12-31  3                                       4   
18924       1591        2018-12-25  4                                       4   
27113       5808        2020-10-16  1           9533        2020-10-14      1
27113       3405        2020-10-01  3           9521        2020-09-25      3
27113       9889        2020-09-21  4           8680        2020-09-11      4
27113       1976        2020-09-19  5           3504        2020-09-03      5


from Recent Questions - Stack Overflow https://ift.tt/31aOJWL
https://ift.tt/eA8V8J

No comments:

Post a Comment