Calculating the Tally

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717408

    Comments posted to this topic are about the item Calculating the Tally

  • TomThomson

    SSC Guru

    Points: 104772

    That looks as if it will use up a lot of your beer money, Steve.  πŸ˜€

    And your net beer-debt is the answer to the Ultimate Question of Life, the Universe, and Everything.
    So clearly the Ultimate Question is "How many (net) beers does Steve owe his colleagues?"
    A question whose answer changes from time to time does of course fit the bill!:hehe:

    Tom

  • Ed Wagner

    SSC Guru

    Points: 286969

    What a great question!  My sincere compliments on coming up with a cleaver scenario.  The real question, Steve, is what do you do at work to warrant such a ratio? :hehe:

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71615

    Nice twist, thanks Steve
    Just hope Bart has someone to drive him home after all that...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • diego.perdices

    SSCommitted

    Points: 1701

    Love this puzzles.
    Thx! πŸ™‚

  • allinadazework

    SSCarpal Tunnel

    Points: 4365

    At a rough estimate you owe about 150 quid in beer! You need to get down the pub more and pay your debts Steve!! πŸ˜€

  • Thomas Franz

    Hall of Fame

    Points: 3631

    How did you get your result?
    I used the following query and get other numbers:
    WITH myBeers (<see CTE in the question> )
    SELECT ISNULL(bt.OwedTo, bb.OwedBy) who, SUM(bt.Beer) owedTo, SUM(bb.Beer) owedBy, SUM(bt.Beer) - SUM(bb.Beer) net
    FROM myBeers bt
    FULL JOIN myBeers bb
     ON bb.OwedBy = bt.OwedTo
    GROUP BY ISNULL(bt.OwedTo, bb.OwedBy)
    ORDER BY net

    This returns
    who  owedTo  owedBy  net
    ------- ----------- ----------- -----------
    Andrew   80    128   -48
    Tugberk 140   165   -25
    Steve    22    44    -22
    Bart    780   798   -18
    Tony    140   133   7
    Ally     16    6    10
    Rob     77    35    42
    Dave    136   77    59

    God is real, unless declared integer.

  • diego.perdices

    SSCommitted

    Points: 1701

    WITH myBeers (<see CTE in the question> )
    ,nto as (
    SELECT OwedTo as pp, sum(beer) as beers FROM myBeers
    group by owedto),
    nby as (SELECT Owedby as pp, sum(beer) as beers FROM myBeers
    group by owedby)
    select nto.pp,nto.beers - nby.beers from nto inner join nby on nto.pp=nby.pp
    order by nto.beers - nby.beers desc

    Not the optimal way but, It works :p

  • Joel Ewald

    SSCertifiable

    Points: 6060

    SELECT X.Person, SUM(X.NetBeerCount) as NetBeerTotal
    FROM (    SELECT OwedTo as Person, Beer as NetBeerCount FROM myBeers WHERE EarnedDate < GETDATE()
            UNION ALL
            SELECT OwedBy, Beer * -1 FROM myBeers WHERE EarnedDate < GETDATE()
    ) X
    GROUP BY X.Person
    ORDER BY NetBeerTotal DESC

  • jeberhard-623651

    SSCrazy

    Points: 2644

    replace the SELECT * from myBeers with:

    select b1.owedTo, sum(b1.beer) - (select sum(beer) from myBeers b2 where b2.OwedBy = b1.OwedTo)

    from myBeers b1

    group by b1.OwedTo

    order by b1.OwedTo

  • Bob Razumich

    SSCrazy

    Points: 2019

    diego.perdices - Monday, September 18, 2017 5:57 AM

    WITH myBeers (<see CTE in the question> )
    ,nto as (
    SELECT OwedTo as pp, sum(beer) as beers FROM myBeers
    group by owedto),
    nby as (SELECT Owedby as pp, sum(beer) as beers FROM myBeers
    group by owedby)
    select nto.pp,nto.beers - nby.beers from nto inner join nby on nto.pp=nby.pp
    order by nto.beers - nby.beers desc

    Not the optimal way but, It works :p

    Given that it started as a CTE, I decided the goal was to practice multiple CTEs. So, I came up with the same solution method, of course with my own silly names for the sums. As other posts show, as with everything with SQL, there are several ways to get the same answer. I'm sure we could turn on stats IO, look at actual execution plans, etc, to find the most optimal way, But since it's about beer (for those who like it), all solutions are optimal. Except for perhaps Steve, who apparently keeps betting on the wrong horse.

  • latkinson

    Ten Centuries

    Points: 1195

    Clearly I have a lot more to learn about CTE's, among other things.

  • Aaron N. Cutshall

    SSCrazy Eights

    Points: 8756

    So, with Bart a happy man, that makes Steve VERY UNHAPPY funding the pool!! πŸ˜€

    I tend to break down the problem into segments then write the code accordingly.   I also allowed for the scenario that someone could be owed beers without owing any themselves so here's the code I used:
    WITH myBeers AS(
        /* snippet */
        ),
    cte_OwedToTotals(OwedTo, OwedToSum) AS (
        SELECT OwedTo, SUM(Beer)
        FROM myBeers
        GROUP BY OwedTo
        ),
    cte_OwedByTotals(OwedBy, OwedBySum) AS (
        SELECT OwedBy, SUM(Beer)
        FROM myBeers
        GROUP BY OwedBy
        )
    SELECT a.OwedTo, (a.OwedToSum - ISNULL(b.OwedBySum,0)) as NbrBeers,
            ROW_NUMBER() OVER (ORDER BY (a.OwedToSum - ISNULL(b.OwedBySum,0)) DESC) as RankNbr
    FROM cte_OwedToTotals a
    LEFT JOIN cte_OwedByTotals b
        ON b.OwedBy = a.OwedTo
    ORDER BY 3;

  • Ed Wagner

    SSC Guru

    Points: 286969

    Aaron N. Cutshall - Monday, September 18, 2017 7:19 AM

    So, with Bart a happy man, that makes Steve VERY UNHAPPY funding the pool!! πŸ˜€

    I tend to break down the problem into segments then write the code accordingly.   I also allowed for the scenario that someone could be owed beers without owing any themselves so here's the code I used:
    WITH myBeers AS(
        /* snippet */
        ),
    cte_OwedToTotals(OwedTo, OwedToSum) AS (
        SELECT OwedTo, SUM(Beer)
        FROM myBeers
        GROUP BY OwedTo
        ),
    cte_OwedByTotals(OwedBy, OwedBySum) AS (
        SELECT OwedBy, SUM(Beer)
        FROM myBeers
        GROUP BY OwedBy
        )
    SELECT a.OwedTo, (a.OwedToSum - ISNULL(b.OwedBySum,0)) as NbrBeers,
            ROW_NUMBER() OVER (ORDER BY (a.OwedToSum - ISNULL(b.OwedBySum,0)) DESC) as RankNbr
    FROM cte_OwedToTotals a
    LEFT JOIN cte_OwedByTotals b
        ON b.OwedBy = a.OwedTo
    ORDER BY 3;

    Heh - That's nearly identical to what I did, Aaron.  The only difference is that I made a temp table.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717408

    I was actually owed beers, but I thought this would be a funnier scenario.

    Fortunately in the real world, the tally was much closer to zero.

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

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