Calculating the Tally

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

    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

  • Luis Cazares - Monday, September 18, 2017 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;

    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


  • 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

  • Luis Cazares - Monday, September 18, 2017 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;

    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!!

  • Aaron N. Cutshall - Monday, September 18, 2017 2:29 PM

    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!!

    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)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Monday, September 18, 2017 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;

    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.

  • 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