Search code examples
sqlt-sqlsubqueryssmsunion

Adding multiple select statements without using Union ALL


I currently wrote a query that adds multiple select statements using UNION ALL and returns the sum of the amounts within each select statement. My question: is there any way or approach to make this query smaller (not that lengthy)?

The only thing that changes within each subquery is the ID# used to grab ID1 from the Accnt Table

 SELECT SUM(TOTAL) TOTAL
 FROM
(
SELECT ISNULL (round(sum(signeddata),2),0)
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
    SELECT ID1 FROM DataSrc
    WHERE ID7 = 'Logic')
AND ACCOUNT IN (
    select ID1 from Accnt
    where ID7 = 'EXPENSES')
AND ENTITY IN (
    SELECT ID1 FROM Entity
    where ID9 = 'H2')

UNION ALL

SELECT ISNULL (round(sum(signeddata),2),0)*-1
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
    SELECT ID1 FROM DataSrc
    WHERE ID7 = 'Logic')
AND ACCOUNT IN (
    select ID1 from Accnt
    where ID7 = 'MERC')
AND ENTITY IN (
    SELECT ID1 FROM Entity
    where ID9 = 'H2')

    UNION ALL

SELECT ISNULL (round(sum(signeddata),2),0)*-1
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
    SELECT ID1 FROM DataSrc
    WHERE ID7 = 'Logic')
AND ACCOUNT IN (
    select ID1 from Accnt
    where ID11 = 'SYNP')
AND ENTITY IN (
    SELECT ID1 FROM Entity
    where ID9 = 'H2')
) S

Solution

  • One idea is to sum case-semijoins instead of putting them in the FROM clause:

    SELECT 
        ISNULL(round(sum(signeddata),2),0)
        *
        (
            case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'EXPENSES') 
            then 1 else 0 end * 1
            +
            case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'MERC') 
            then 1 else 0 end * (-1)
            +
            case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'SYNP') 
            then 1 else 0 end * (-1)
        )
    FROM BUDGET
    WHERE CATEGORY = 'ACTL'
    AND TIME BETWEEN '20210100' AND '20210800'
    AND CURRENCY = 'DOL'
    AND DATASRC IN (
        SELECT ID1 FROM DataSrc
        WHERE ID7 = 'Logic')
    AND ENTITY IN (
        SELECT ID1 FROM Entity
        where ID9 = 'H2')