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
Thanks again for the save @Rick_Bird I’m much closer than I was.
Looks like it’s counting the open releases and not how many unique ShipToNums per SONumber. If you look 217310 should have three unique hits on SONum-ShipTo instead of seven. I’m going to keep beating on it.
The formula is: Count(SONumShipTo) Over(Partition by OrderRel.OrderNum)
or better how about this:
I’m a bit fuzzy on this but try:
Count(OrderNum) Over(Partition by SONumShipTo)
Or
Count(*) Over(Partition by SONumShipTo)
(I’ve been in tax software training all day and my brain is mush…)
Sorry, maybe someone else in here can advise, I know I’ve seen this topic in here before.
@Rick_Bird props to you sir as I never would have found it without your post linking to the MS site. It’s a two part calculation of dense_rank() :
dense_rank() over (partition by OrderRel.OrderNum order by SONumShipTo)
+ dense_rank() over (partition by OrderRel.OrderNum order by SONumShipTo desc) - 1