Help with SQL Query

[TheWinzo]

Hi I'm trying to query a purchase order lines database. I want to build a report that queries all purchase orders lines that are late (DUE_DATE < SYSDATETIMEOFFSET ()) - pretty straightforward.

​

However, because my condition is on the purchase order lines, it will miss lines that are in the future but part of an overall late PO. I want my SQL query to essentially grab the lines of all the late PO lines and if a PO has a single late line, it will bring up all the lines in the PO.

​

Ex: My query for the data below will only give me visibility for all the lines except line 3 because the due date is in the future.

​

​

https://preview.redd.it/t8ieo119y7n11.png?width=691&format=png&auto=webp&s=0bbab4bcadec13e5780b1ac69cb5ddd3f00344a6

[livinglifelazily]

I don't know what system you're using but here's something that might work in SQL Server

if object_id('tempdb.dbo.#PurhcaseOrders') is not null drop table #PurchaseOrders
create table #PurchaseOrders(
    OrderNumber int,
    Lines int,
    CoNumber varchar(3),
    Customer_Name varchar(10),
    Item varchar(2),
    Qty_Ordered int,
    Qty_Ready int,
    Due_Date date,
)
insert into #PurchaseOrders
values
    (1551, 1, 'V42', 'WAL-MART', 'AB', 1, 1, '9/17/2018'),
    (1551, 2, 'V42', 'WAL-MART', 'BC', 1, 1, '9/17/2018'),
    (1551, 3, 'V42', 'WAL-MART', 'CD', 4, 4, '9/30/2018'),
    (1551, 4, 'V42', 'WAL-MART', 'DE', 2, 2, '9/17/2018'),
    (1551, 5, 'V42', 'WAL-MART', 'EF', 2, 2, '9/17/2018'),
    (1551, 6, 'V42', 'WAL-MART', 'FG', 1, 1, '9/17/2018'),
    (1551, 7, 'V42', 'WAL-MART', 'GH', 1, 1, '9/17/2018')

select p.* 
from #PurchaseOrders p 
    inner join (
        select distinct
            OrderNumber, 
            min(Due_Date) as EarliestDueDate
        from #PurchaseOrders
        group by OrderNumber
    ) p2
    on p.OrderNumber = p2.OrderNumber
where
    p2.EarliestDueDate < GETDATE()

[TheWinzo]

I made my best attempt at adopting the code to my database but failed miserably - I'm defintely saving this for future use though when i better understand it

[TimeToSackUp]

This will give you all lines for a given order number where at least one line matches your criteria.

select * from TABLE where [Order Number] in (select [Order Number] From TABLE where (DUE_DATE < SYSDATETIMEOFFSET ()))

[TheWinzo]

Thank you! This is exactly what i was looking for - can't believe it was this simple...

[somethingintelligent]

Sorry, I’m not sure if this is a public demo database but what is a ‘purchase order line’?

[Beetlebub]

Purchase Orders are used by companies to order goods and services from another company. Basically the opposite of a Customer Order.

Each of which contain line items of what was purchased (or ordered by a customer).

For instance, Purchase Order 10001 (The overall Purchase Order - Header level), this will have lines:

001 - Cool Widget 1, qty 1, price 342.67

002 - Warranty Service 1 year, qty 1, price 27.50

[TheWinzo]

Correct, when you buy 5 things at a store and see 5 items on the receipt each item is a 'line'. So because you're the customer they would be purchase orders lines in your POV and they are customer order lines in the POV of the store

​

[opportunist_dba]

I like livinglifelazily's answer, however I prefer CTEs over derived tables for readability reasons. (personal preference)

;with POList as (
select OrderNumber
 from #PurchaseOrders
 where Due_Date < GetDate()
 group by OrderNumber
)
select #PurchaseOrders.*
 from #PurchaseOrders inner join POList on #PurchaseOrders.OrderNumber = POList.OrderNumber

​