Query advise

Question

Hi

My Orders table has OrderID, OrderDate, InvoiceNo, InvoiceDate and DeliveryDate columns. DDl is given below. Multiple orders can belong to a single invoice and thus have the same InvoiceNo.

I need to get orders in a given DeliveryDate range. However the tricky bit is that I also need orders that have the same InvoiceNo as any of the orders returned in the previous DeliveryDate range query.

In below data (using dd/MM/yyyy format dates) for a delivery date range 4/1/2021 - 6/1/2021, orders 3, 4 and 5 would be returned. However since order 3 has the same invoice number as orders 1 and 2 so orders 1 and 2 should also be part of result set.

ID,     OrderDate,  InvoiceNo, InvoiceDate, DeliveryDate
1,          '1/1/2021',     1,             '5/1/2021',  '2/1/2021'
2,  '2/1/2021',     1,             '5/1/2021',  '3/1/2021'
3,  '3/1/2021',     1,             '4/1/2021',  '4/1/2021'
4,  '4/1/2021',     2,             '6/1/2021',  '5/1/2021'
5,  '5/1/2021',     2,             '6/1/2021',  '6/1/2021'
6,  '6/1/2021',     3,             '8/1/2021',  '7/1/2021'
7,  '7/1/2021',     3,             '9/1/2021',  '8/1/2021'

How do I do that please?

Thanks

Regards

CREATE TABLE [dbo].[Orders](
    [ID] [int] NOT NULL,
    [OrderDate] [datetime] NULL,
    [InvoiceNo] [int] NULL,
    [InvoiceDate] [datetime] NULL,
    [DeliveryDate] [datetime] NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

Please try:

SELECT * FROM [dbo].[Orders] 
WHERE invoiceno IN (SELECT invoiceno FROM [dbo].[Orders] 
WHERE deliverydate BETWEEN '4/1/2021' AND '6/1/2021')

Or:

;WITH cte
as(SELECT * FROM [dbo].[Orders] 
WHERE deliverydate BETWEEN '4/1/2021' AND '6/1/2021')

SELECT * FROM [dbo].[Orders] 
WHERE invoiceno IN (SELECT invoiceno FROM cte)

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @-- -- ,

Please try:

SELECT * FROM Orders o  
JOIN (SELECT * FROM invoicetable  
WHERE InvoiceDate IN InvoiceDaterange) t  
ON o.InvoiceNo=t.InvoiceNo  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.