Ok, here is a weird one. I’ve been asked to make a BAQ that’ll parse though all Open Releases and count how many orders have “more than one ShipTo --or MarkForNum-- per Sales Order Number”.
My BAQ has concatenate field that check if there is a MarkFor and then combines OrderNum & ShipToNum/MarkForNum accordingly. Visually it works, but my problem is getting the query to count how many per unique hits. I’ve tried Count, Max, etc in various ways in sub-queries but nothing seems to work. Any ideas?
Rick’s runntion totals are AWESOME but not totally valid for my need. But props to him as I never would have found it without your post linking to the MS site.
It’s a two part calculation:
dense_rank() over (partition by OrderRel.OrderNum order by SONumShipTo) + dense_rank() over (partition by OrderRel.OrderNum order by SONumShipTo desc) - 1