Query to filter orders according to posted date?

Question

I have requirement where I have to write a query to get something with date filter monthwise like this.

1. we have can multiple invoices against an order (Each invoice with new posted date)
2. I have to get records of such invoices which are posted in very first month and for subsequent month's query with new date filter such records should not be part of resultset.

For Example

Now another invoice for the same order posted in February, 2019.

Now when I run this query for January only record with January posted date should appear and when I run this query for February, then this order should not be part of resultset as we have already decided that it will be part of previous month (January) resultset.

I am using following query to get result but it gives correct result for January but for it is again showing same result for February as well. Kindly help in this regard.

DECLARE @From DATETIME
DECLARE @To DATETIME

SET @From = '2018-01-01 00:00:00.000'
SET @To = '2018-01-30 23:59:59.997'

SELECT *
FROM (
    SELECT s.OrderNumber
        ,i.InvoiceNumber
        ,i.new_PostedDate
        ,i.new_CanceledDate
        ,s.new_OrderStatus
        ,i.StatusCode
        ,s.ModifiedOn
        ,ROW_NUMBER() OVER (
            PARTITION BY s.OrderNumber ORDER BY i.new_PostedDate ASC
            ) AS RowNumber
    FROM SalesOrders s
    INNER JOIN Invoices i ON s.SalesOrderId = i.SalesOrderId
    LEFT JOIN StatusReasonsLookup sl ON i.StatusCode = sl.Id
        AND (i.new_PostedDate >= @From)
        AND (i.new_PostedDate <= @To)
        --Where sl.StatusCodeName  <> 'Canceled' 
    ) tblInvoice
WHERE RowNumber = 1

Answer 1

I suspect that you want:

select *
from (
    select 
        s.*, 
        min(posted_date) over(partition by order_number) first_posted_date
    from salesorders s
) s
where 
    s.posted_date = s.first_posted_date
    s.first_posted_date between @from and @to

The inner query uses a window min() to recover the first posted_date of each invoice. Then the outer query filters on the first record per group, whose first posted_date matches the filter.