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

By -

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

Comments

Post a Comment

Popular posts from this blog

Today Walkin 14th-Sept

By -
Image
14th Sept 2017 ( GO Through all List) Read Walkin details properly , Try to call company before attending if possible !!! ==================================== 1. Myntra through fresherworld (Call Based , Venue shared to shortlisted candidates) _____________________________ 2. Magna : Hiring for Client Harman : C++
Read more

Spring Elasticsearch Operations

By -
Elasticsearch Operations Spring Data Elasticsearch uses two interfaces to define the operations that can be called against an Elasticsearch index. These are ElasticsearchOperations and ReactiveElasticsearchOperations. Whereas the first is used with the classic synchronous implementations, the second one uses reactive infrastructure. The default implementations of the interfaces offer: Read/Write mapping support for domain types. A rich query and criteria api. Resource management and Exception translation. ElasticsearchTemplate The ElasticsearchTemplate is an implementation of the ElasticsearchOperations interface using the Transport Client. @Configuration public class TransportClientConfig extends ElasticsearchConfigurationSupport {   @Bean   public Client elasticsearchClient() throws UnknownHostException {                      Settings settings = Settings.builder().put("cluster.name", "elasticsearch")....
Read more

Hibernate Search - Elasticsearch with JSON manipulation

By -
Elasticsearch with JSON manipulation Elasticsearch ships with many features. It is possible that at some point, one feature you need will not be exposed by the Search DSL. To work around such limitations, Hibernate Search provides ways to: Transform the HTTP request sent to Elasticsearch for search queries. Read the raw JSON of the HTTP response received from Elasticsearch for search queries. Direct changes to the HTTP request may conflict with Hibernate Search features and be supported differently by different versions of Elasticsearch. Similarly, the content of the HTTP response may change depending on the version of Elasticsearch, depending on which Hibernate Search features are used, and even depending on how Hibernate Search features are implemented. Thus, features relying on direct access to HTTP requests or responses cannot be guaranteed to continue to work when upgrading Hibernate Search, even for micro upgrades (x.y.z to x.y.(z+1)). Use this at your own risk....
Read more