Home Adding rows from case statement - SQL Server
Reply: 2

Adding rows from case statement - SQL Server

Ryan Gadsdon
1#
Ryan Gadsdon Published in 2018-01-11 15:21:34Z

The current results I have are this

I am comparing the totals from the two tables on a monthly basis. When the total is different between the 2 tables i want to add the difference to another column

| MonthYear | Person | Table1 Amount | Table2 Amount | Unknown |
+-----------+--------+---------------+---------------+---------+
|    Jun-17 |    Tom |           100 |           125 |      25 |
|    Nov-17 |        |            50 |           150 |     100 |
|    Sep-17 |    Ben |            50 |            50 |       0 |

Which i have achieved but how do i add the case statement as a row instead instead of a column e.g output like this. I can then group via the 'unknown' category.

| MonthYear | Person | Table1 Amount | Table2 Amount |  Difference |
+-----------+--------+---------------+---------------+-------------+
|    Jun-17 |    Tom |           100 |           125 |          25 |
|    Nov-17 |        |            50 |           150 |         100 |
|    Sep-17 |    Ben |            50 |            50 |           0 |
|    Jun-17 |Unknown |            25 |          Null |           0 |
|    Nov-17 |Unknown |           100 |          Null |           0 |

Can this be done? Any help would be appreciated. Thanks

SQL FIDDLE - http://sqlfiddle.com/#!6/8843b/24

Peter Lalovsky
2#
Peter Lalovsky Reply to 2018-01-11 16:10:39Z
SELECT  
  T2.MonthYear
  , MAX(T1.Person) AS Person
  , SUM(T1.Amount) AS [Table1 Amount]
  , SUM(T2.Amount) AS [Table2 Amount]
  , CASE
        WHEN SUM(T1.Amount) <> SUM(T2.Amount)
        THEN ABS(SUM(T1.Amount) - SUM(T2.Amount)) ELSE 0 
  END AS [Difference]
FROM
    Invoice AS T1
    JOIN Invoice2 AS T2
        ON T1.Code1 = T2.Code1
        AND T1.InvoiceNumber = T2.InvoiceNumber
GROUP BY T2.MonthYear

UNION ALL

SELECT
    T2.MonthYear
    , 'Unknown' AS Person
    , ABS(SUM(T1.Amount) - SUM(T2.Amount)) AS [Table1 Amount]
  , NULL AS [Table2 Amount]
    , 0 AS [Difference]
FROM
    Invoice AS T1
    JOIN Invoice2 AS T2
        ON T1.Code1 = T2.Code1
        AND T1.InvoiceNumber = T2.InvoiceNumber
GROUP BY T2.MonthYear
HAVING SUM(T1.Amount) <> SUM(T2.Amount);
shawnt00
3#
shawnt00 Reply to 2018-01-11 19:37:26Z
SELECT  
    T1.MonthYear, T1.Person,
    min(T1.Amount) AS "Table1 Amount", min(T2.Amount) AS "Table2 Amount",
    min(CASE WHEN T1.Amount <> T2.Amount 
             THEN ABS(T1.Amount - T2.Amount)
             ELSE 0 END) AS [Difference]
FROM Invoice AS T1 LEFT OUTER JOIN Invoice AS T2
    ON T1.Code1 = T2.Code1 AND T1.InvoiceNumber = T2.InvoiceNumber AND T1.Person <> ''
GROUP BY T1.MonthYear, T1.Person;

Personally, I don't like to use sum() as a dummy aggregate so I've changed it to min().

And actually I really don't think you need the groupings at all. If you do have multiple matching rows per code and invoice then the join would end up multiplying those rows as a cross product:

SELECT  
    t1.MonthYear, T1.Person, t1.Amount AS [Table1 Amount], t2.Amount AS [Table2 Amount],
    CASE WHEN T1.Amount <> T2.Amount 
         THEN ABS(T1.Amount - T2.Amount) ELSE 0 END AS [Difference]
FROM Invoice AS T1 LEFT OUTER JOIN Invoice AS T2
    ON T1.Code1 = T2.Code1 AND T1.InvoiceNumber = T2.InvoiceNumber AND t1.Person <> ''
ORDER BY CASE WHEN t1.Person <> '' then 0 else 1 end, T1.MonthYear, t1.Person;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302194 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO