ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL needed

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL needed

    I have 2 files, ORDERH and ORDERD. The common link is order#. ORDERH contains the customer# and the order date and ORDERD contains the product type.

    For a selected date, I need to determine the number of UNIQUE customers with orders for each product type


  • #2
    Something like this might work, ORHED is ORDERH, ORDET is ORDERD. I used item number instead of product type but should be the same query.

    select oditno,count(oditno),ohcsno from ordet as a
    left join orhed as b on a.odorno=b.ohorno
    group by oditno,ohcsno
    order by ohcsno,oditno

    Comment


    • #3
      Originally posted by gregwga50 View Post
      I have 2 files, ORDERH and ORDERD. The common link is order#. ORDERH contains the customer# and the order date and ORDERD contains the product type.

      For a selected date, I need to determine the number of UNIQUE customers with orders for each product type
      Wouldn't something like this work?

      Code:
      SELECT PRODTYPE,CUSTNO FROM ORDERD GROUP BY PRODTYPE,CUSTNO ORDER BY PRODTYPE,CUSTNO

      Comment


      • #4
        Originally posted by Rocky View Post

        Wouldn't something like this work?

        Code:
        SELECT PRODTYPE,CUSTNO FROM ORDERD GROUP BY PRODTYPE,CUSTNO ORDER BY PRODTYPE,CUSTNO
        Problem is OP wants it for a "selected date" which is in the order header.

        Comment


        • #5
          Try something like this:
          Code:
          Select ProdType, Count(Distinct CustNo)
            from OrderHdr h join OrderDet d on h.order# = d.order#
            Where OrderDate = '2018-09-29'  
            Group By ProdType;
          Birgitta

          Comment

          Working...
          X