Weird BAQ Request


(Randy Stulce) #1

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

(Rick Bird) #2

Randy,
Have you tried using the COUNT( ) OVER() in a calculated field?

It’s the SQL windowing feature:


(Randy Stulce) #3

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)

image


(Rick Bird) #4

I’m a bit fuzzy on this but try:
Count(SONumShipTo) Over(Partition by OrderRel.OrderNum, OrderRel.ShipToNum)


(Rick Bird) #5

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.


(Randy Stulce) #6

@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

(Simon Hall) #7

That’s a gem