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:
- DocumentDate must be before the InvoiceDate
- 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