# 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.OwedToGROUP BY ISNULL(bt.OwedTo, bb.OwedBy)ORDER BY net`
This returns
`who  owedTo  owedBy  net------- ----------- ----------- -----------Andrew   80    128   -48Tugberk 140   165   -25Steve    22    44    -22Bart    780   798   -18Tony    140   133   7Ally     16    6    10Rob     77    35    42Dave    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 RankNbrFROM cte_OwedToTotals aLEFT JOIN cte_OwedByTotals b    ON b.OwedBy = a.OwedToORDER 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 RankNbrFROM cte_OwedToTotals aLEFT JOIN cte_OwedByTotals b    ON b.OwedBy = a.OwedToORDER 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)