CODEWITHSUNDEEP
sqlsql-server-2014
Nick
Nick

Reputation: 372

Get Orders with single line with specific status

I have a query for purchase order data. Orders contain a header table (PS_PO_HDR) and a corresponding Lines table (PS_PO_LINE) that are joined by BUSINESS_UNIT and PO_ID. There can be 1 or more lines of a PO_ID that map to 1 PO_ID on the header table. I need a query that will identify PO_ID's that only have only either a single line (or another way put PO_ID's with no more than 1 line) with a CANCEL_STATUS = 'X' OR multiple lines that all have CANCEL_STATUS = 'X', along with the other criteria below. I've tried a combination of GROUP BY and HAVING but the below query will pick up PO_ID's with multiple lines where one of those lines has CANCEL_STATUS = 'X'. That is not what I need, I need to identify PO_ID's from the PS_PO_LINE table that only have a single row (1 line).

SELECT LINE.PO_ID, COUNT(LINE.PO_ID), COUNT(LINE.CANCEL_STATUS)
FROM PS_PO_LINE LINE
INNER JOIN PS_PO_HDR HEADER ON HEADER.BUSINESS_UNIT = LINE.BUSINESS_UNIT AND HEADER.PO_ID = LINE.PO_ID
WHERE 
HEADER.PO_DT >= '01-JAN-2019'
 AND HEADER.PO_STATUS NOT IN ('C', 'X', 'PX')
 AND LINE.CANCEL_STATUS = 'X'
GROUP BY LINE.PO_ID
HAVING COUNT(LINE.PO_ID) = 1 AND COUNT(LINE.CANCEL_STATUS) = 1

Example data: Below is an example from the PS_PO_LINE table. This is ordered by BUSINESS_UNIT and PO_ID. The Green highlighted row (PO_ID A030010686) is a PO_ID with only 1 line (row) that has CANCEL_STATUS = 'X'. You can see the other PO_ID's A030010688 and L010006793 each does have a row with CANCEL_STATUS = 'X' but it also contains an additional with a different CANCEL_STATUS value (A) so I would not want these included in my results. I only want to return PO_ID's that have 1 or more lines that all have CANCEL_STATUS = 'X' (with the other criteria above).

enter image description here

Upvotes: 0

Views: 384

Answers (2)

irnerd
irnerd

Reputation: 411

Probably many better ways of doing it - but I suggest labelling each PO_ID with markers up front - the common table expression linesAndStates below can then be used to cherry pick required conditions later

with cte_linesAndStates as (
select line.PO_ID
     , TotalLines = count(line.PO_ID)
     , LineCancelStatusX = sum(case when line.cancel_status in ('X') 
                                    then 1 else 0 end)
     , LineCancelStatusNotX = sum(case when line.cancel_status not in ('X') 
                                     then 1 else 0 end)
  from ps_po_line line
  group by line.PO_ID
)
SELECT header.Business_Unit
     , line.PO_ID
     , line.line_nbr
     , line.cancel_status
  FROM PS_PO_LINE LINE
  inner join cte_linesAndStates on cte_linesAndStates.po_id = ps_po_line.po_id
     and (cte_linesAndStates.LineCancelStatusX > 0   -- Explicitly asking for X
     and cte_linesAndStates.LineCancelStatusNotX = 0)  -- Explicitly excluding non X
     --- insert other critieria here with OR AND etc
  inner join ps_po_hdr header 
     on header.business_unit = line.business_unit and header.po_id = line.po_id
 where header.po_dt >= '01-jan-2019'
   and header.po_status not in ('C', 'X', 'PX')

Upvotes: 1

nachospiu
nachospiu

Reputation: 2049

Try this (return all HEADER.PO_ID with one or more LINES all with LINE.CANCEL_STATUS = 'X' AND HEADER.PO_DT >= '01-JAN-2019'):

SELECT HEADER.PO_ID
FROM PS_PO_HDR HEADER
INNER JOIN PS_PO_LINE LINE ON HEADER.BUSINESS_UNIT = LINE.BUSINESS_UNIT AND HEADER.PO_ID = LINE.PO_ID
WHERE HEADER.PO_DT >= '01-JAN-2019'
GROUP BY HEADER.PO_ID
HAVING COUNT(*) = SUM(CASE WHEN LINE.CANCEL_STATUS = 'X' THEN 1 ELSE 0 END)

You need to GROUP BY for all HEADER (table) fields part of the primary key, so if BUSINESS_UNIT is part of the primary key add it to GROUP BY clause.

Upvotes: 1

Related Questions