Complex query 1

[Queryman]

I have a table with the following columns with ten different network types and 60K account numbers.
Account_nbr,
Network_Type,
Brand_AWP,
Brand_Rxs,
Generic_Rxs,
Brand_Fee,
Generic_Fee,
Brand_Rate,
Generic_Rate

I need to do the following:
Sum the AWP and RXS by Network_type so I can get a few weighted percentages. I tried doing this with an update statement, and ran into the aggregates not allowed in updates. I didn't want to create views or tables but try & do the whole thing with select statements.

I only need to calculate stats on the table as a whole and I am looking for suggestions to do the following

1. Network Yield = BRAND_RATE * AWP/SUM(AWP) this is a percentage for each account number, but when all 60K of them are added, you will have one yield number.

2. Network Brand Fee = Brand_fee * Brand_Rxs/SUM(Brand_Rxs)

3. Network Generic Fee = Generic_fee * Generic_Rxs/SUM(Generic_Rxs)

4. Network Average Fee = (Brand_Fee + Generic_Fee)/Sum((Brand_Fee + Generic_Fee))

Any and all suggestions will be highly appreciated


Michael

 

[dnoeth]

Which release you're running?

Looks like a case for OLAP-functions:

select
BRAND_RATE * AWP/
SUM(AWP) over (partition by Network_Type rows between
unbounded preceding and unbounded following)
as "Network Yield"
...

If you run an older release you'll have to calculate the SUMs in a derived table and join it.
And this is the way for an update, too. You can't do
update ... set col = (select sum(..)....)

but
update ... from (select netweork_type nt, sum() as x) dt
set col = x
where network_type = nt

Dieter

 

[Queryman]

Version = V2R5.0

I was able to get the follwing update query to work
update tempdb.MD_TABLE from (select sum(Brand_awp)as X from
tempdb.MD_TABLE)DT
set BRAND_AWP_RESPONSE_PCT = X where RESPONSE = 'Y'

but not this

update tempdb.MD_TABLE from (select (BRAND_RATE * BRAND_AWP/sum(Brand_awp)) as X from
tempdb.TABLE)DT
set BRAND_AWP_RESPONSE_PCT = X where RESPONSE = 'Y'

Michael

 

[dnoeth]

You're really running R5 already? ;-)

You need the OLAP version of SUM:
update tempdb.MD_TABLE from (select (BRAND_RATE * BRAND_AWP/sum(Brand_awp) OVER (partition by Network_Type) as X from
tempdb.TABLE)DT
set BRAND_AWP_RESPONSE_PCT = X where RESPONSE = 'Y'

Dieter