Union ALL with partition

I have two separate queries and i want to combine them into one. on their own they are working fine, I tried using union but i can't seem to get it right. Basically one query is getting balance as of a certain date and the other is calculating activity for a range of dates. I want the results to appear into columns next to each other.

Tried writing query without partition and as a simple group by. used union but then i can't get the columns for the second query to appear on the main select statement.

Declare @Date datetime = '04/01/2019'

Code for beginning balance:

    Select Left(Account,4)Entity, right(Account,9)Account, sum(debit+credit)BBal
From GLT_CURRENT__TRANSACTION 
Where Left(Account,4) = '9452'  and 
Right(Account,9) IN ('1110.0130','1110.0131','2110.0061','2110.0062') 
and Accounting_Date <= EOMONTH('04-01-2019',-1)
Group By Left(Account,4), right(Account,9)

Code for sum of activity

    Select Left(Account,4)Entity,Right(Account,9),Sum(debit+credit)Activity
From GLT_CURRENT__TRANSACTION AS A Where 
Left(Account,4) = '9452' and Accounting_Date >= '04-01-2019' and Accounting_Date <= Eomonth('04-01-2019')
AND Right(Account,9) IN ('1110.0130','1110.0131','2110.0061','2110.0062')
Group By Left(Account,4), Right(Account,9)

How to combine the two on 1 row? Tried union but didn't work on the second query.

Entity   Account  BBalance Activity
  9452 1110.0130     50.00    2,500

Solution

I think you're over-complicating things

SELECT
    LEFT(Account,4) as Entity, 
    RIGHT(Account,9) as Account, 
    SUM(CASE WHEN accounting_date <= EOMONTH(@date, -1) THEN debit+credit END) as BBal,
    SUM(CASE WHEN accounting_date > EOMONTH(@date, -1) THEN debit+credit END) as Activity
FROM
    GLT_CURRENT__TRANSACTION 
WHERE
    Left(Account,4) = '9452'  and 
    Right(Account,9) IN ('1110.0130','1110.0131','2110.0061','2110.0062')  and
    Accounting_Date <= EOMONTH(@Date) 
GROUP BY 
    Left(Account,4), 
    right(Account,9)

This query, when given an @date like 14-Mar-2018, selects all transactions before the 31-Mar-2018. For each row, the CASE WHEN inside the SUM tests whether the accounting date is in the past (for bbal) or current month (for activity). If the accounting date for a particular transaction does not comply with the rule CASE WHEN test returns false) then the return value from the CASE WHEN is null, so it is not summed

To understand more about how this query works, run it without the grouping/summing:

SELECT
    LEFT(Account,4) as Entity, 
    RIGHT(Account,9) as Account, 
    Accounting_date,
    (CASE WHEN accounting_date <= EOMONTH(@date, -1) THEN debit+credit END) as BBal,
    (CASE WHEN accounting_date > EOMONTH(@date, -1) THEN debit+credit END) as Activity
FROM
    GLT_CURRENT__TRANSACTION 
WHERE
    Left(Account,4) = '9452'  and 
    Right(Account,9) IN ('1110.0130','1110.0131','2110.0061','2110.0062')  and
    Accounting_Date <= EOMONTH(@Date)

Transactions would be like this for @date in march:

1, 1, 02-Feb-18, $100, null --previous month
1, 1, 28-Feb-18, $300, null --previous month
1, 1, 02-Mar-18, null, $400 --current month
1, 1, 28-Mar-18, null, $500 --current month

See how the case when has split the transaction amount into either the bbal or activity column depending on the accounting date? Now when we sum those and group them (remove the date column from the query results):

1, 1, $400, $900  --the $400 is £100+$300, the $900 is $400+$500