Results 1 to 4 of 4

Thread: Orders containing products

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    173

    Default Orders containing products

    Is it possible to create a report, that can provide me with the JobNumbers of Orders that contain one or multiple products/services using filters?

    My data can look like this:

    Job# 1111
    - OrderDetail1 - Sweeping

    Job # 2222
    - OrderDetail1 - Striping
    - OrderDetail2 - Striping
    - OrderDetail3 - Striping

    Job # 3333
    - OrderDetail1 - Asphalt
    - OrderDetail2 - Striping
    - OrderDetail3 - Sweeping

    Here is what I started with, but I am getting redundant JobNumber records due to multiple matching ActivityTypes (Services) on each order.
    If I filter to see all Striping Jobs, I will see multiple records for one Job where I only want to see one record per JobNumber:

    JobNumber | Activity Type
    2222 | Striping
    2222 | Striping
    2222 | Striping
    3333 | Striping


    Code:
    SELECT
    JobNumber, 
    MastOps.ActivityType AS ActivityType
    FROM dbo.[OrderDtl] OrdDtl
    LEFT JOIN dbo.MASTOPS MastOps ON OrdDtl.MastOpsIdno = MastOps.MastOpsIdno
    WHERE @FILTER_T:ActivityType@
    Ben

  2. #2
    Join Date
    Feb 2009
    Location
    Hengelo (NL)
    Posts
    1,720

    Default Re: Orders containing products

    Code:
    SELECT
    distinct JobNumber, 
    MastOps.ActivityType AS ActivityType
    FROM dbo.[OrderDtl] OrdDtl
    LEFT JOIN dbo.MASTOPS MastOps ON OrdDtl.MastOpsIdno = MastOps.MastOpsIdno
    WHERE @FILTER_T:ActivityType@

  3. #3
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    173

    Default Re: Orders containing products

    OK, that is giving me the expected result but I noticed that I may have not specified properly.

    Using the same example, when I filter for two values (Asphalt & Striping) I only want to see one JobNumber but it shows it multiple times.

    Job# 1111

    - OrderDetail1 - Sweeping

    Job # 2222
    - OrderDetail1 - Striping
    - OrderDetail2 - Striping
    - OrderDetail3 - Striping

    Job # 3333
    - OrderDetail1 - Asphalt
    - OrderDetail2 - Striping
    - OrderDetail3 - Sweeping

    Results in:
    JobNumber | Activity Type
    2222 | Striping
    2222 | Striping
    2222 | Striping
    3333 | Asphalt
    3333 | Striping

    Ben
    Last edited by Benjamin; 15-Jun-2018 at 09:21 AM.

  4. #4
    Join Date
    Feb 2009
    Posts
    1,462

    Default Re: Orders containing products

    Do a grouping on job number. If you experiment with Dynamic AI design tool then you can see the SQL that it generates to achieve the result. You'll want to make the details in their own level.
    -Bob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •