edit check query

Reply
Frequent Contributor
Posts: 145

edit check query

I Have to run to edit check query on table having collumns customerid, product and orderno.

ordernumber has values(1-5). not all ordernumbers(1-5) are valid for every product.If the same product has all the values from 1-5 , then it is discrepancy. I need to pull out those records with discrepany.

ID   product orderno

001 apple      1

001 apple      2

001 apple      3

001 apple      4

001 apple      5

002 orange    1

002 orange    3

003 banana   1

003 banana   2

004 berry      1

004 berry       2

In the above table id(001) with product apple is discrepany because it has all the values 1-5. i need to pull out those reocord from the table.

Thansk in advance

Rakesh

Super Contributor
Posts: 644

Re: edit check query

Posted in reply to rakeshvvv

Something like this (not tested)

Proc sql ;

     create table want as

          select a.*

          from have a

               , (Select product

                    ,  count (distinct order) as tot

                    from have

                 )     b

          where a.product = b.product

               and b.tot = 5

     ;

quit ;


Richard

Frequent Contributor
Posts: 145

Re: edit check query

Posted in reply to RichardinOz

Thanks Richard......


Ask a Question
Discussion stats
  • 2 replies
  • 191 views
  • 1 like
  • 2 in conversation