CODEWITHSUNDEEP
sql-server-2008select
Tania Mofflin
Tania Mofflin

Reputation: 35

Combine results from 2 SQL Server queries into 2 columns

I currently have 2 SQL queries:

select 
    SUM(CASE T1.DOCTYPE 
          WHEN '1' THEN T1.CURTRXAM *1
          WHEN '4' THEN T1.CURTRXAM *-1
          WHEN '5' THEN T1.CURTRXAM *-1
          WHEN '6' THEN T1.CURTRXAM *-1
       END) as [Payables TB]
from PM20000 T1

select
    sum(PERDBLNC) as [GL Balance]
from GL10110
where ACTINDX = '130'

which return 2 results like this:

Payables TB
1520512.30

GL Balance
-1520512.30

I would like to combine the results into 2 columns and have a variance column like below -

Payables TB      GL Balance       Variance
1520512.30       -1520512.30      0.00

Thank you

Upvotes: 0

Views: 144

Answers (2)

StuartLC
StuartLC

Reputation: 107387

You can wrap these into CTE's to reuse the values to compute the difference. With no join condition you will just need to CROSS JOIN, as long as these return just one row each :

WITH Payables AS
(
  SELECT 
    SUM(
      CASE  
        WHEN T1.DOCTYPE IN ('1') THEN T1.CURTRXAM *1
        WHEN T1.DOCTYPE IN ('4','5','6') THEN T1.CURTRXAM *-1
        -- ? ELSE
      END) as [Payables TB]
  FR PM20000 T1
),
Balance AS
(
  SELECT
  SUM(PERDBLNC) as [GL Balance]
  FROM GL10110
  WHERE ACTINDX = '130'
)
SELECT 
  Payables.[Payables TB], 
  Balance.[GL Balance],  
  Payables.[Payables TB] + Balance.[GL Balance] AS Variance
  FROM 
    Payables, Balance; -- OR Payables CROSS JOIN Balance

Since you seem to be doing the same projection for T1.DOCTYPE 4, 5 and 6 in the first query, you can replace it with a CASE WHEN x IN (...)

Upvotes: 0

Hieu Vo
Hieu Vo

Reputation: 3284

simply

select
    (select 
        SUM(CASE T1.DOCTYPE 
        WHEN '1' THEN T1.CURTRXAM *1
        WHEN '4' THEN T1.CURTRXAM *-1
        WHEN '5' THEN T1.CURTRXAM *-1
        WHEN '6' THEN T1.CURTRXAM *-1
        END) as [Payables TB]
        from PM20000 T1) as Payables TB,

    (select
        sum(PERDBLNC) as [GL Balance]
        from GL10110
        where ACTINDX = '130') as GL Balance,

    0.00 as Variance

Upvotes: 1

Related Questions