September 16, 2017 at 7:52 pm
Comments posted to this topic are about the item Calculating the Tally
September 17, 2017 at 10:45 am
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
September 17, 2017 at 5:06 pm
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:
September 18, 2017 at 12:17 am
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”
September 18, 2017 at 12:20 am
Love this puzzles.
Thx! 🙂
September 18, 2017 at 1:14 am
At a rough estimate you owe about 150 quid in beer! You need to get down the pub more and pay your debts Steve!! 😀
September 18, 2017 at 3:45 am
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.
September 18, 2017 at 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
September 18, 2017 at 6:45 am
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
September 18, 2017 at 6:49 am
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
September 18, 2017 at 6:57 am
diego.perdices - Monday, September 18, 2017 5:57 AMWITH 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 descNot 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.
September 18, 2017 at 7:03 am
Clearly I have a lot more to learn about CTE's, among other things.
September 18, 2017 at 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;
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
September 18, 2017 at 7:26 am
Aaron N. Cutshall - Monday, September 18, 2017 7:19 AMSo, 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.
September 18, 2017 at 8:59 am
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