I have a doubt in logic of SQL query please help me in resolving this.
How to select a row with a earliest posting date for a single invoice number in SQL Server:
Existing table:
| INVOICE NUMBER | NAME | Posting date | Place |
|---|---|---|---|
| 2050 | Dinosaur | 20.05.1998 | London |
| 2050 | Dinosaur | 25.04.1995 | Australia |
| 2045 | Birds | 18.03.1997 | America |
| 2045 | Birds | 27.07.1995 | China |
| 2075 | Lion | 12.06.2012 | India |
I want to query the output like this:
| INVOICE NUMBER | NAME | Posting date | Place |
|---|---|---|---|
| 2050 | Dinosaur | 25.04.1995 | Australia |
| 2045 | Birds | 27.07.1995 | China |
| 2075 | Lion | 12.06.2012 | India |
I am struggling with logic for how to compare dates of two posting date and return the row with lowest posting date column value for each invoice number in SQL Server.
I tried to use MIN() and INNER JOIN before but it didn't help in my complex codebase so I am displaying it in simple manner here.
EDIT: updated from the previous one I want this change to occur only to newly created invoices not already existing ones, so i want to be applied if the invoice date is greater than the a particular date or other way occur only if the invoice number is already existing in database AND also having two posting dates for the same invoice number.
The query should produce a table if the invoice date is not existing in sample database and also having two posting dates then it should display a single row with earliest posting date (having same invoice number) eg :china
if the invoice date is existing in a sample database and having two posting dates then it should display all the rows with same invoice number with no change eg: london, australia
Other rows which have single posting date ( whether they exist or not existing in a sample database) they should display their row. eg:India
Existing Example table:
| INVOICE NUMBER | NAME | Invoice Date | Posting date | Place |
|---|---|---|---|---|
| 2050 | Dinosaur | 20.03.1999 | 20.05.1998 | London |
| 2050 | Dinosaur | 20.03.1999 | 25.04.1995 | Australia |
| 2045 | Birds | 26.06.2005 | 18.03.1997 | America |
| 2045 | Birds | 26.06.2005 | 27.07.1995 | China |
| 2075 | Lion | 22.04.2012 | 12.06.2012 | India |
I want to query as single Query to display output like this:
| INVOICE NUMBER | NAME | Invoice Date | Posting date | Place |
|---|---|---|---|---|
| 2050 | Dinosaur | 20.03.1999 | 20.05.1998 | London |
| 2050 | Dinosaur | 20.03.1999 | 25.04.1995 | Australia |
| 2045 | Birds | 26.06.2005 | 27.07.1995 | China |
| 2075 | Lion | 22.04.2012 | 12.06.2012 | India |
I'm not able to that as a single SELECT query and I also checked with particular date instead of checking in database, it too failed. Please help me on this.
Remark : this is sample table, it will be filled with dynamic values with dynamic columns from codebase, so, ommiting American row is my not expected output, getting output based on the logic is the expected output.
Thank you
The window functions are your friends. Well worth your time to get comfortable with them. Also not clear if you need NAME in the partition by
Also, if you want to see ties ... use dense_rank() instead of row_number()
More Performant
Select *
From (
Select *
,RN = row_number() over (partition by InvoiceNumber,Name order by PostingDate)
From YourTable
) A
Where RN=1
Another Option
Select top 1 with ties *
From YourTable
Order by row_number() over (partition by InvoiceNumber,Name order by PostingDate)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments