Calculating the Tally

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

  • 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

  • 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:

  • 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”

  • Love this puzzles.
    Thx! πŸ™‚

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

  • 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.

  • 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

  • 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

  • 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

  • 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.

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

  • 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;

  • 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.

  • 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 22 total)

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