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
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')