Get charges percentage per entity\channel

  • I trying to figure out what percentage of the total charge for a ME that has different Prod Lines on that same ME. If the total charge for the ME is 1040 I want to see the split at a entity\channel level for the charges. I only want to include ME# where there are 2 different Prod Lines associated with single ME. I would like to Grand Total for all charges regardless of entity\channel.

    Thanks.

    Example Output:

    ME                    Channel    Entity   ProdLine  Charges  PctofTotal

    6659                       RID          800           7               331          30%

    6659                       BXS         600           8               709          70%

    total                                                                           1040

    GrandTotal                                                              1040

     

    CREATE TABLE #MonthlyChrgs 
    (
    tmp_channel NVARCHAR(10)
    ,tmp_entity NVARCHAR(10)
    ,tmp_year NVARCHAR(4)
    ,tmp_month NVARCHAR(10)
    ,tmp_day NVARCHAR(10)
    ,tmp_itemId NVARCHAR(35)
    ,tmp_meid NVARCHAR(35)
    ,tmp_prodline NVARCHAR(35
    ,tmp_charges DEC(18,2)
    )
    ;
    Insert Into #MonthlyChrgs
    Values('rid','800','2021','07',10','brz',6659','7',243.00)
    go
    Insert Into #MonthlyChrgs
    Values('rid','800','2021','07',10','xx3',6659','7',88.00)
    go
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07',10','xx8',6659','8',574.00)
    go
    goInsert Into #MonthlyChrgs
    Values('bxs','600','2021','07',10','xx7',6659','8',135.00)
    go
  • First question - what have you tried?

    To me, unless I am misunderstanding it, this sounds pretty easy using windowing functions to handle the first 1/2 and then the total becomes trivial to do inside of the presentation layer.

    You can get the percentage by doing a SUM of the tmp_charges over a partition on tmp_channel divided by a sum of the tmp_charges over a partition on tmp_meid and multiply that value by 100 (to get it to a percentage), and cast to an INT to remove the decimals.  May want to round it instead of casting, but depends on how you want to handle that.

    Now as for adding the total on, I would do that inside the tool that will be presenting it.  SSRS, Excel, powerBI, and other BI tools make adding totals pretty easy to do.  IF you need to add it in to the TSQL, I think you are going to have a rough time of it.  It is possible, but doing it in the tool is going to be a LOT easier.

    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'm not sure how to verify that I have a single tmp_meid spread across multiple tmp_Channels. If I hard code in the tmp_meid

    that I know is broke out like this I can see the split charges.

    Select 
    tmp_channel,
    tmp_meid,
    sum(tmp_Charges)
    from
    #MonthlyChrgs
    where tmp_meid = '??'
    group by
    tmp_channel,tmp_meid
  • Your query will give you the channel, MEID and the sum of the charges, which is half the battle.  But I would take the approach I suggested with windowing functions because then you aren't needing to group by all of the non-aggregate columns.  Basically you select the columns you want, then the aggregates get tossed into windowing functions which work similar to GROUP BY but don't need to be applied across all of the non-aggregate columns.

    Therefore, you end up with a query that looks more like this:

    SELECTDISTINCT
    [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_channel]
    ) AS [Charges]
    , CAST(ROUND(100 * (SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    , [tmp_channel]
    ) / SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    )
    )
    , 0
    ) AS INT)AS [PctofTotal]
    FROM[#MonthlyChrgs];

    the ME, Channel, Entity, and ProdLine columns are straight out of the table.  There is no GROUP BY needed and we have Charges and PctofTotal as windowing function calculations and things are good to go!

    Does the above work for you?  Do you have any questions about the above?

    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!

  • The query is rolling up the channel,entity and Prodline and giving a total for tmp_charges along with a percent. I was looking for ONLY where a tmp_meid has different entity\channels associated with it. Then displaying the total for each and which percentage each have of the 100%.

    I may have not explained well enough. I'm trying to figure who is carrying the higher percent of the Cost when the tmp_meid has different entity\channel attached to a single tmp_meid

    Thanks for your response.

  • As a thought (probably not the most efficient resolution), but toss the above query into a CTE (or nested select) and add a WHERE clause onto the final select where PctofTotal < 100?  Something like:

    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_channel]
    ) AS [Charges]
    , CAST(ROUND(100 * (SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    , [tmp_channel]
    ) / SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    )
    )
    , 0
    ) AS INT)AS [PctofTotal]
    FROM[#MonthlyChrgs]
    )
    SELECT *
    FROM cte
    WHERE PctofTotal < 100;

    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!

  • drop table #MonthlyChrgs ;
    CREATE TABLE #MonthlyChrgs
    (
    tmp_channel NVARCHAR(10)
    ,tmp_entity NVARCHAR(10)
    ,tmp_year NVARCHAR(4)
    ,tmp_month NVARCHAR(10)
    ,tmp_day NVARCHAR(10)
    ,tmp_itemId NVARCHAR(35)
    ,tmp_meid NVARCHAR(35)
    ,tmp_prodline NVARCHAR(35)
    ,tmp_charges DEC(18,2)
    )
    ;
    Insert Into #MonthlyChrgs
    Values('rid','800','2021','07','10','brz','6659','7',243.00)
    go
    Insert Into #MonthlyChrgs
    Values('rid','800','2021','07','10','xx3','6659','7',88.00)
    go
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx8','6659','8',574.00)
    go
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','6659','8',135.00)
    go

    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','6670','8',135.00)
    go

    Select * from
    #MonthlyChrgs;

    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_channel]
    ) AS [Charges]
    , CAST(ROUND(100 * (SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    , [tmp_channel]
    ) / SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    )
    )
    , 0
    ) AS INT)AS [PctofTotal]
    FROM[#MonthlyChrgs]
    )
    SELECT *
    FROM cte;

    Using your latest code post and my sample data the first 2 records in query are what I'm looking for where the Cost was Split on this single tmp_meid between different entity\channel. The 3rd record are the records we can ignore we 100% was paid by a single entity\channel.

    Many Thanks.

  • When I run it on my live data I'm also get a:

     

    Divide by zero error encountered.

    Thanks..

  • I forgot the last statement in the code:

    WHERE PctofTotal < 100;

     

    But even with this I was still getting records at 100% with a single meid and single entity\channel. I did notice on those records the

    ProdLine was different for each record and the sum was duplicated.

    Thanks.

  • I had the data ordered wrong coming out of the cte, but did notice the percentages are off when Prodline isn't populated When

    a meid has multiple items, but the Prodline is empty that's when the percentage and summations are off.

    Thanks.

  • Can you provide some sample data where this happens?

    Giving the text explanation  that you did, I am not able to reproduce the problem.

     

    If I had to guess, I would say the problem is due to me misreading the original request and I was giving the summation based on the Channel, not the Prod Line.  To fix that, change the CTE to:

    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_prodline]
    ) AS [Charges]
    , CAST(ROUND(100 * (SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    , [tmp_prodline]
    ) / SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    )
    )
    , 0
    ) AS INT)AS [PctofTotal]
    FROM[#MonthlyChrgs]
    )
    SELECT *
    FROM cte
    WHERE PctofTotal < 100;

    You will notice that it is now partitioning on the MEID and the Prod Line instead of the channel.  That may (or may not) fix the issue you are having.  But without sample data it is hard to know what is wrong.

    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!

  • It seems to be working, except it should Only display where there is a split costs between channel and entity. If it's all for the same entity\channel no matter if prodline is different is should skip those on the output.

    So 1 MEID is attached to 2 different entity\channel(s) .  If you run my sample from above it it will show different entity and channel attached to the same MEID, it has different Prodlines and it sums the costs for each and does the percentage spilt.

    Thanks.

     

  • I must be misunderstanding.  When I run the example you gave above, I get the expected output you gave above minus the Total and Grand Total values.

    When I run the query I provided, I get:

    and only that.  No other rows.  This matches up with the expected output you gave, except that the percentages don't match, but your percentages were off anyway.  709/1040 is 68%, not 70%.

    In the second example you gave, MEID 6670 is in the list, but when you filter it by PctofTotal < 100, it removes that row as shown in the screenshot above.  I even added in additional MEID's (blank and NULL) to test things and I got only the 2 rows listed above.  The full query I ran was:

    USE [Admin]
    CREATE TABLE #MonthlyChrgs
    (
    tmp_channel NVARCHAR(10)
    ,tmp_entity NVARCHAR(10)
    ,tmp_year NVARCHAR(4)
    ,tmp_month NVARCHAR(10)
    ,tmp_day NVARCHAR(10)
    ,tmp_itemId NVARCHAR(35)
    ,tmp_meid NVARCHAR(35)
    ,tmp_prodline NVARCHAR(35)
    ,tmp_charges DEC(18,2)
    )
    ;
    Insert Into #MonthlyChrgs
    Values('rid','800','2021','07','10','brz','6659','7',243.00)
    go
    Insert Into #MonthlyChrgs
    Values('rid','800','2021','07','10','xx3','6659','7',88.00)
    go
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx8','6659','8',574.00)
    go
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','6659','8',135.00)
    go

    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','6670','8',135.00)
    GO
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','6670','8',135.00)
    GO
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','','8',135.00)
    GO
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','','8',135.00)
    GO
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7',NULL,'8',135.00)
    GO
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7',NULL,'8',135.00)
    GO


    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_prodline]
    ) AS [Charges]
    , CAST(ROUND(100 * (SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    , [tmp_prodline]
    ) / SUM([tmp_charges]) OVER (PARTITION BY
    [tmp_meid]
    )
    )
    , 0
    ) AS INT)AS [PctofTotal]
    FROM[#MonthlyChrgs]
    )
    SELECT *
    FROM cte
    WHERE PctofTotal < 100;

    drop table #MonthlyChrgs ;

    What output are you expecting?

    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!

  • Let me send some more sample data..

    THanks.

  • Use these records as it should bypass them on the output. It has same MEID and entity and channel, the prodline is different but that

    shouldn't matter since charges are split between 2 different entity\channels

    Insert Into #MonthlyChrgs 
    Values('bxs','600','2021','07','10','xx7','zz1','8',135.00)
    GO
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','zz1','8',135.00)
    GO
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','zz1','8',135.00)
    GO
    Insert Into #MonthlyChrgs
    Values('bxs','600','2021','07','10','xx7','zz1','7',135.00)
    GO

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply