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