sql where clause help

Question

sql where clause help

Gary Keramidas 41

was wondering if someone could help with a where clause. in the data below i need a query based on the PO and Stat fields. the bolded rows (hopefully they're bolded, because i'm not seeing it as i'm writing. if not, rows 1-4 and 21-23 ) are the rows i need to return.

the criteria is a PO number that only has a status < 40 and no other status codes.

you can see po's 590064 has items with status of 10 and 90. so i don't return that.

po 590065 has status 10, 40, and 90, so don't return that.

i can't figure out how to exclude those.

Loc PO Item wt Stat qty rec

CL 000587408 790121 0 10 450

CL 000587409 790121 0 10 120

CL 000587410 790121 0 10 450

CL 000587411 790121 0 10 450

PCS 000590064 790121 4200 90 70

CL 000590064 790121 0 10 450

PCS 000590064 790121 6300 90 105

PCS 000590065 790121 6300 90 0

PCS 000590065 790121 0 10 1

PCS 000590065 790121 4200 90 70

PCS 000590065 790121 4140 90 0

PCS 000590065 790121 6300 90 105

PCS 000590065 790121 60 40 0

CL 000590065 790121 0 10 450

PCS 000590065 790121 4140 90 70

PCS 000590066 790121 12600 90 210

PCS 000590066 790121 4200 90 70

CL 000590066 790121 28560 90 476

FP 000590066 790121 18900 90 315

CL 000590067 790121 28620 90 477

CL 000591787 790121 0 10 500

CL 000591788 790121 0 10 500

CL 000591789 790121 0 10 500

CL 000591790 790121 28320 90 472

Gary Keramidas 41 Reputation points

2020-10-09T14:25:33.657+00:00

sorry the table didn't line up`, couldn't see a way to change the font or insert a table.
tried attaching a png i have no idea what happened when i clicked upload.
Ronen Ariely 15,221 Reputation points

2020-10-09T15:10:41.917+00:00

yes... this is poor interface, but at the same time this is NOT how you should provided the information

You present a question in professional technical forum by using stories and trying to format the display of some text instead of simply provide code.

All you need to do is to provide queries to create the table and insert the sample data instead of stories. and for the expected result you can add image if needed

This could make our life simpler to reproduce the issue and provide a solution which we tested.

Conclusions: next time, do not fight the system but simply provide code over stories 🙂
EchoLiu-MSFT 14,626 Reputation points

2020-10-13T01:31:36.3+00:00

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo
EchoLiu-MSFT 14,626 Reputation points

2020-10-14T08:46:35.547+00:00

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Echo

Answer accepted by question author

Tom Phillips 17,786

Try this:
DECLARE @Bluemchen TABLE (Loc VARCHAR(100),PO VARCHAR(100),Item VARCHAR(100),wt VARCHAR(100),Stat INT, qty VARCHAR(100));

INSERT INTO @tbl VALUES  
('CL','000587408','790121','0','10','450'),  
('CL','000587409','790121','0','10','120'),  
('CL','000587410','790121','0','10','450'),  
('CL','000587411','790121','0','10','450'),  
('PCS','000590064','790121','4200','90','70'),  
('CL','000590064','790121','0','10','450'),  
('PCS','000590064','790121','6300','90','105'),  
('PCS','000590065','790121','6300','90','0'),  
('PCS','000590065','790121','0','10','1'),  
('PCS','000590065','790121','4200','90','70'),  
('PCS','000590065','790121','4140','90','0'),  
('PCS','000590065','790121','6300','90','105'),  
('PCS','000590065','790121','60','40','0'),  
('CL','000590065','790121','0','10','450'),  
('PCS','000590065','790121','4140','90','70'),  
('PCS','000590066','790121','12600','90','210'),  
('PCS','000590066','790121','4200','90','70'),  
('CL','000590066','790121','28560','90','476'),  
('FP','000590066','790121','18900','90','315'),  
('CL','000590067','790121','28620','90','477'),  
('CL','000591787','790121','0','10','500'),  
('CL','000591788','790121','0','10','500'),  
('CL','000591789','790121','0','10','500'),  
('CL','000591790','790121','28320','90','472');  
  
SELECT *  
FROM @tbl  
WHERE PO IN (  
	SELECT PO   
	FROM @tbl  
	GROUP BY PO  
	HAVING MAX(Stat) < 40  
)

EchoLiu-MSFT 14,626 Reputation points

2020-10-12T06:49:37.42+00:00

Hi Tom,
Your code does not seem to satisfy the conditions of op:

What op wants is that a PO has only one status <40 and no other status values.
Echo

2 additional answers

Your answer

Gary Keramidas 41 Reputation points

2020-10-09T14:25:33.657+00:00

sorry the table didn't line up`, couldn't see a way to change the font or insert a table.
tried attaching a png i have no idea what happened when i clicked upload.
Ronen Ariely 15,221 Reputation points

2020-10-09T15:10:41.917+00:00

yes... this is poor interface, but at the same time this is NOT how you should provided the information

You present a question in professional technical forum by using stories and trying to format the display of some text instead of simply provide code.

All you need to do is to provide queries to create the table and insert the sample data instead of stories. and for the expected result you can add image if needed

This could make our life simpler to reproduce the issue and provide a solution which we tested.

Conclusions: next time, do not fight the system but simply provide code over stories 🙂
EchoLiu-MSFT 14,626 Reputation points

2020-10-13T01:31:36.3+00:00

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo
EchoLiu-MSFT 14,626 Reputation points

2020-10-14T08:46:35.547+00:00

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Echo
EchoLiu-MSFT 14,626 Reputation points

2020-10-12T06:49:37.42+00:00

Hi Tom,
Your code does not seem to satisfy the conditions of op:

What op wants is that a PO has only one status <40 and no other status values.
Echo

Answer 1

The CTE requires more time, the other solutions are equal. But the last has the simplest execution plan:

DECLARE @tbl TABLE (
    Loc VARCHAR(100) ,
    PO VARCHAR(100) ,
    Item VARCHAR(100) ,
    wt VARCHAR(100) ,
    Stat INT ,
    qty VARCHAR(100)
);

INSERT INTO @tbl
VALUES ( 'CL', '000587408', '790121', '0', '10', '450' ) ,
       ( 'CL', '000587408', '790121', '0', '50', '450' ) ,
       ( 'CL', '000587409', '790121', '0', '10', '120' ) ,
       ( 'CL', '000587410', '790121', '0', '10', '450' ) ,
       ( 'CL', '000587411', '790121', '0', '10', '450' ) ,
       ( 'PCS', '000590064', '790121', '4200', '90', '70' ) ,
       ( 'CL', '000590064', '790121', '0', '10', '450' ) ,
       ( 'PCS', '000590064', '790121', '6300', '90', '105' ) ,
       ( 'PCS', '000590065', '790121', '6300', '90', '0' ) ,
       ( 'PCS', '000590065', '790121', '0', '10', '1' ) ,
       ( 'PCS', '000590065', '790121', '4200', '90', '70' ) ,
       ( 'PCS', '000590065', '790121', '4140', '90', '0' ) ,
       ( 'PCS', '000590065', '790121', '6300', '90', '105' ) ,
       ( 'PCS', '000590065', '790121', '60', '40', '0' ) ,
       ( 'CL', '000590065', '790121', '0', '10', '450' ) ,
       ( 'PCS', '000590065', '790121', '4140', '90', '70' ) ,
       ( 'PCS', '000590066', '790121', '12600', '90', '210' ) ,
       ( 'PCS', '000590066', '790121', '4200', '90', '70' ) ,
       ( 'CL', '000590066', '790121', '28560', '90', '476' ) ,
       ( 'FP', '000590066', '790121', '18900', '90', '315' ) ,
       ( 'CL', '000590067', '790121', '28620', '90', '477' ) ,
       ( 'CL', '000591787', '790121', '0', '10', '500' ) ,
       ( 'CL', '000591788', '790121', '0', '10', '500' ) ,
       ( 'CL', '000591789', '790121', '0', '10', '500' ) ,
       ( 'CL', '000591790', '790121', '28320', '90', '472' );


WITH cte
AS ( SELECT * ,
            ROW_NUMBER() OVER ( PARTITION BY PO
                                ORDER BY Item ) AS rn
     FROM   @tbl )
SELECT cte.Loc ,
       cte.PO ,
       cte.Item ,
       cte.wt ,
       cte.Stat ,
       cte.qty
FROM   cte
WHERE  cte.PO IN (   SELECT   cte.PO
                     FROM     cte
                     WHERE    cte.Stat < 40
                     GROUP BY cte.PO
                     HAVING   MAX(cte.rn) < 2 );

SELECT O.*
FROM   @tbl O
       CROSS APPLY (   SELECT COUNT(*)
                       FROM   @tbl I
                       WHERE  I.PO = O.PO
                              AND I.Stat > 40 ) Q(Cnt)
WHERE  Q.Cnt = 0;

SELECT O.*
FROM   @tbl O
WHERE  O.PO IN (   SELECT   I.PO
                   FROM     @tbl I
                   GROUP BY I.PO
                   HAVING   MAX(I.Stat) < 40 );

SELECT O.*
FROM   @tbl O
       LEFT JOIN @tbl I ON I.PO = O.PO
                           AND I.Stat > 40
WHERE  I.PO IS NULL;

SELECT O.*
FROM   @tbl O
WHERE  NOT EXISTS (   SELECT *
                      FROM   @tbl I
                      WHERE  I.PO = O.PO
                             AND I.Stat > 40 );

Answer 2

Hi @Gary Keramidas ,

Welcome to Q&ATSQL Forum!
In order to solve your problem faster, please provide relevant tables and codes and the output you expect when posting the problem (image or excel can be used).

According to my understanding, the following code returns those data whose According to my understanding, the following code returns the data that a PO has only one status <40 and no other status values.
Please check:

create table #test (Loc char(25),PO char(25),Item char(25),wt char(25),Stat int, qty char(25));  
insert into #test values  
 ('CL','000587408','790121','0','10','450'),  
 ('CL','000587409','790121','0','10','120'),  
 ('CL','000587410','790121','0','10','450'),  
 ('CL','000587411','790121','0','10','450'),  
 ('PCS','000590064','790121','4200','90','70'),  
 ('CL','000590064','790121','0','10','450'),  
 ('PCS','000590064','790121','6300','90','105'),  
 ('PCS','000590065','790121','6300','90','0'),  
 ('PCS','000590065','790121','0','10','1'),  
 ('PCS','000590065','790121','4200','90','70'),  
 ('PCS','000590065','790121','4140','90','0'),  
 ('PCS','000590065','790121','6300','90','105'),  
 ('PCS','000590065','790121','60','40','0'),  
 ('CL','000590065','790121','0','10','450'),  
 ('PCS','000590065','790121','4140','90','70'),  
 ('PCS','000590066','790121','12600','90','210'),  
 ('PCS','000590066','790121','4200','90','70'),  
 ('CL','000590066','790121','28560','90','476'),  
 ('FP','000590066','790121','18900','90','315'),  
 ('CL','000590067','790121','28620','90','477'),  
 ('CL','000591787','790121','0','10','500'),  
 ('CL','000591788','790121','0','10','500'),  
 ('CL','000591789','790121','0','10','500'),  
 ('CL','000591790','790121','28320','90','472');  
    
;with cte   
as(SELECT *,row_number() over(partition by PO order by Item)rn FROM #test)  
select Loc,PO,Item,wt,Stat,qty from cte   
where PO in (select PO from cte where Stat<40 group by PO having max(rn)<2)  
  
drop table #test

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

sql where clause help

2 additional answers

Your answer