SQLISHARD SQLISHARD - 2 months ago 5
SQL Question

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

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

Answer

In SELECT *, you can change it to

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