Get charges percentage per entity\channel
-
It includes them becuase the Prod Line is different.
Your original requirement (original post) says:
" I only want to include ME# where there are 2 different Prod Lines associated with single ME"
So it is including them as per your description. If you want to exclude them, you would need to change how it is doing the summations for the percentage and the total to partition (group) by.
Now, if you partition it by MEID, ENTITY and CHANNEL and NOT by Prodline, the new data you provided is excluded. What I mean is, this query:
WITH cte AS (
SELECT DISTINCT
[tmp_meid]AS [ME]
, [tmp_channel]AS [Channel]
, [tmp_entity]AS [Entity]
, [tmp_prodline]AS [ProdLine]
, SUM([tmp_charges]) OVER (PARTITION BY
[tmp_meid]
,[tmp_entity]
, [tmp_channel]
) AS [Charges]
, CAST(ROUND(100 * (SUM([tmp_charges]) OVER (PARTITION BY
[tmp_meid]
,[tmp_entity]
, [tmp_channel]
) / SUM([tmp_charges]) OVER (PARTITION BY
[tmp_meid]
)
)
, 0
) AS INT)AS [PctofTotal]
FROM[#MonthlyChrgs]
)
SELECT *
FROM cte
WHERE PctofTotal < 100;with the original data set PLUS the new values you provided, gives this output:
If you change how it is partitioned, it will change what fields are used for calculating the Changes and the PctofTotal.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Very Nice that's what I was looking for...
MANY THANKS !!!!!!!
-
If I get a divide by zero error any way to code around that, over figure out the data causing the error?
Thanks.
-
Easiest way - change the division to handle when 0 may be the result. How you handle that is up to you, but my approach would be to set it to NULL because it is easy to find those values and determine why it worked out to be 0 if I need to. You could alternately set the 0 to a -1 (negative one) which would give you a negative number for the percent which would also make it easy to calculate, but I personally like the use of NULL in this scenario.
To set it to NULL, change the query to something like this:
WITH cte AS (
SELECT DISTINCT
[tmp_meid]AS [ME]
, [tmp_channel]AS [Channel]
, [tmp_entity]AS [Entity]
, [tmp_prodline]AS [ProdLine]
, SUM([tmp_charges]) OVER (PARTITION BY
[tmp_meid]
,[tmp_entity]
, [tmp_channel]
) AS [Charges]
, CAST(ROUND(100 * (SUM([tmp_charges]) OVER (PARTITION BY
[tmp_meid]
,[tmp_entity]
, [tmp_channel]
) / NULLIF(SUM([tmp_charges]) OVER (PARTITION BY
[tmp_meid]
)
,0)
)
, 0
) AS INT)AS [PctofTotal]
FROM[#MonthlyChrgs]
)
SELECT *
FROM cte
WHERE PctofTotal < 100;Basically, all I did was if the sum for the divisor works out to be 0, we are setting it to NULL (via NULLIF). This way you get X/NULL which will give you NULL.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Awesome Man!!!!
Thanks..
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply