September 18, 2017 at 10:05 am
Why would everyone read the table twice instead of just unpivoting it?
WITH myBeers AS(
/* snippet */
)
SELECT Person, SUM(OwedBeer) NetBeerOwed
FROM myBeers
CROSS APPLY (VALUES(OwedTo, Beer), (OwedBy, -Beer)) up(Person,OwedBeer)
--WHERE EarnedDate <= GETDATE()
GROUP BY up.Person
ORDER BY NetBeerOwed DESC;
September 18, 2017 at 11:08 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
That's how I did it. If it works, it works. Wasn't overly complex to write. KISS is preferred over MADCAP (Make All Development Complex As Possible) <-- trying to get this into the common vernacular
September 18, 2017 at 11:22 am
Luis Cazares - Monday, September 18, 2017 10:05 AMWhy would everyone read the table twice instead of just unpivoting it?
WITH myBeers AS(
/* snippet */
)
SELECT Person, SUM(OwedBeer) NetBeerOwed
FROM myBeers
CROSS APPLY (VALUES(OwedTo, Beer), (OwedBy, -Beer)) up(Person,OwedBeer)
--WHERE EarnedDate <= GETDATE()
GROUP BY up.Person
ORDER BY NetBeerOwed DESC;
Nicely done; I knew there had to be a way to use cross apply, but couldn't get my brain to come up with the syntax. I'm one of the two reads people (in fact I even managed to sneak in a third SELECT):
SELECT Name, SUM(OwedTo+OwedBy)
FROM (
SELECT OwedTo Name, SUM(Beer) OwedTo, 0 OwedBy FROM myBeers GROUP BY OwedTo
UNION
SELECT OwedBy, 0, -SUM(Beer) FROM myBeers GROUP BY OwedBy
) sub
GROUP BY Name
ORDER BY SUM(OwedTo+OwedBy) DESC
September 18, 2017 at 1:05 pm
SELECT A.OwedTo, A.plus - B.minus AS Beer
FROM
(
SELECT OwedTo, SUM(Beer) AS plus
FROM myBeers
GROUP BY OwedTo
) A
INNER JOIN
(
SELECT OwedBy, SUM(Beer) AS minus
FROM myBeers
GROUP BY OwedBy
) B
ON A.OwedTo = B.OwedBy
ORDER BY Beer DESC
The way I fumbled through
September 18, 2017 at 2:29 pm
Luis Cazares - Monday, September 18, 2017 10:05 AMWhy would everyone read the table twice instead of just unpivoting it?
WITH myBeers AS(
/* snippet */
)
SELECT Person, SUM(OwedBeer) NetBeerOwed
FROM myBeers
CROSS APPLY (VALUES(OwedTo, Beer), (OwedBy, -Beer)) up(Person,OwedBeer)
--WHERE EarnedDate <= GETDATE()
GROUP BY up.Person
ORDER BY NetBeerOwed DESC;
Luis,
I have to admit that I've never seen the VALUES clause used like that. I had always taken it to be used only for literal values. With the CROSS APPLY, you basically doubled the number of rows for both positive and negative beer counts then simply summed that up grouped by the name. Brilliant!! :smooooth: I will definitely have to remember that particular trick!!
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
September 18, 2017 at 2:50 pm
Aaron N. Cutshall - Monday, September 18, 2017 2:29 PMLuis,I have to admit that I've never seen the VALUES clause used like that. I had always taken it to be used only for literal values. With the CROSS APPLY, you basically doubled the number of rows for both positive and negative beer counts then simply summed that up grouped by the name. Brilliant!! :smooooth: I will definitely have to remember that particular trick!!
Here's some explanation about this and a comparison with UNPIVOT.
An Alternative (Better?) Method to UNPIVOT (SQL Spackle) - SQLServerCentral
You could also use UNION ALL:
CROSS APPLY (SELECT OwedTo, Beer
UNION ALL
SELECT OwedBy, -Beer) u(Person,OwedBeer)
September 19, 2017 at 12:20 am
Luis Cazares - Monday, September 18, 2017 10:05 AMWhy would everyone read the table twice instead of just unpivoting it?
WITH myBeers AS(
/* snippet */
)
SELECT Person, SUM(OwedBeer) NetBeerOwed
FROM myBeers
CROSS APPLY (VALUES(OwedTo, Beer), (OwedBy, -Beer)) up(Person,OwedBeer)
--WHERE EarnedDate <= GETDATE()
GROUP BY up.Person
ORDER BY NetBeerOwed DESC;
Amazing.
I didn´t know this VALUES syntax , this is why I love this puzzles so much, there is always something to learn.
Thx Luis.
September 26, 2017 at 3:09 am
That CROSS APPLY with VALUE is a neat solution - and rather better than mine particularly where I got the OwedBy and OwedTo sum around the wrong way 🙂 Easy error, however certain individuals would be very happy!
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply