Adding a difference or sum column within SQL that has pivot?

Question

This is in SQL Server. I'm looking for a way to add a column to subtract column 201601 from 201602.

IE if after the program is run, 201601's tranamt is 10,000 and 201602's tranamt is 11,000, I want a column that will yield 1,000 to appear next to those two columns.

This is for thousands of lines so brackets are not possible.

I had tried a subselect after my where clause and it did not come out even remotely close to correct and made my pivot returned nothing, as if it had been negated. I have about a month of experience in SQL.

SELECT *


FROM (
SELECT  c.BLDGID AS 'BLDGID', 
        c.LEASID AS 'LEASID', 
        l.OCCPNAME AS 'OCCPNAME', 
        l.SUITID AS 'SUITID', 
        c.INCCAT AS 'INCCAT', 
        c.SRCCODE AS 'SRCCODE', 
        c.TRANAMT AS 'TRANAMT', 
        c.PERIOD AS 'PERIOD'
FROM SQLDATA.dbo.LEAS l
INNER JOIN SQLDATA.dbo.CMLEDG c
    ON l.BLDGID = c.BLDGID AND l.LEASID = c.LEASID
WHERE  c.BLDGID 87000
    AND c.INCCAT JYL
    AND c.SRCCODE NOT LIKE 'CR'
    AND c.SRCCODE NOT LIKE 'PR'
    AND DESCRPTN NOT LIKE 'SECURITY APPLIED'


    AND c.PERIOD BETWEEN 201601 and 201602

) as t
PIVOT (
SUM(TRANAMT) 
FOR PERIOD IN ([201601],[201602])




) revenueperspace

ORDER BY BLDGID, SUITID

score 1 · Accepted Answer · answered Sep 09 '16 at 01:27

1

In SELECT *, you can change it to

SELECT *, revenueperspace.[201602]-revenueperspace.[201601] as Diff

answered Sep 09 '16 at 01:27

Anton

2,846
1
10
15

This answer adds a whole new level to, "Way over thinking the problem." Thank you! – SQLISHARD Sep 09 '16 at 02:12

Adding a difference or sum column within SQL that has pivot?

1 Answers1