Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lappin
Posting Yak Master
182 Posts |
 Posted - 2011-07-06 : 11:43:15
|
| I have a query which uses OVER PARTITION BY to get two averages across different ranges. These are then compared to get a multiplication factor to modify another field. The two values are the overall average for Customer, and the average for Customer in a location. range1:SUM(Sale) OVER (PARTITION BY Custid,LOC_code) as CUST_AVG_FOR_LOCATIONrange2:SUM(Sale)OVER (PARTITION BY Custid) as CUST_OVERALL_AVGThese are compared to give a multiplication factor:(CUST_AVG_FOR_LOCATION/CUST_OVERALL_AVG) as MFIf we add all the "MF" for a customer the total=1This works fine, but I want to exclude a particular location, e.g. where LOC_code='10'But if I use a WHERE LOC_code<>'10' then the total for "MF" is less than 1. I want the split the CUST_AVG_FOR_LOCATION evenly among the other locations not ignore it.I've tried CASE WHEN LOC_code<>'10' then etc. but this still ignores it rather than include it with the others. |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-07-06 : 12:53:08
|
Let's see the whole query with the "Where LOC_Code<>'10'" in it... it should return the records with MF totalling to 1.Otherwise, you could add a couple Case Whens in there to make it work i suppose:SUM(case when LOC_Code <> '10' then Sale else 0 end) OVER (PARTITION BY Custid,LOC_code) as CUST_AVG_FOR_LOCATIONrange2:SUM(case when LOC_Code <> '10' then Sale else 0 end) OVER (PARTITION BY Custid) as CUST_OVERALL_AVGCorey I Has Returned!! |
 |
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-07-07 : 04:51:43
|
| Cheers that fixed it. |
|
|
|
|
|
|
Partition by but exclude values