blocks|key|3951995|text|试试这个-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3951996|DECLARE+@Date+DATETIME+=+'04/01/2019'

SELECT++
A.Entity,+
A.Account,
SUM(A.BBalance)+BBalance,
SUM(A.Activity)+Activity
FROM
(
++++SELECT+LEFT(Account,4)Entity,+
++++RIGHT(Account,9)Account,
++++CAST(SUM(debit%2BCredit)+OVER+(+PARTITION+BY+Right(Account,9))+AS+NUMERIC(8,2))+As+BBalance,
++++0+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,-1)+As+D
++++GROUP+BY+Entity,+D.Account,+BBalance
++++--ORDER+BY+Account

++++UNION+ALL

++++SELECT+LEFT(Account,4)Entity,
++++RIGHT(Account,9),
++++0+BBalance,
++++SUM(debit%2Bcredit)Activity
++++FROM+GLT_CURRENT__TRANSACTION+
++++WHERE+LEFT(Account,4)+=+'9452'+
++++AND+Accounting_Date+>=+@Date+
++++AND+Accounting_Date+<=+Eomonth(@Date)
++++AND+RIGHT(Account,9)+IN+('1110.0130','1110.0131','2110.0061','2110.0062')
++++Group+By+LEFT(Account,4),+RIGHT(Account,9)
)A
GROUP+BY+A.Entity,A.Account|code-block|syntax|javascript|3951997|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this-

<pre><code>DECLARE @Date DATETIME = '04/01/2019'

SELECT 
A.Entity, 
A.Account,
SUM(A.BBalance) BBalance,
SUM(A.Activity) Activity
FROM
(
 SELECT LEFT(Account,4)Entity, 
 RIGHT(Account,9)Account,
 CAST(SUM(debit+Credit) OVER ( PARTITION BY Right(Account,9)) AS NUMERIC(8,2)) As BBalance,
 0 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 &lt;= EOMONTH(@Date,-1) As D
 GROUP BY Entity, D.Account, BBalance
 --ORDER BY Account

 UNION ALL

 SELECT LEFT(Account,4)Entity,
 RIGHT(Account,9),
 0 BBalance,
 SUM(debit+credit)Activity
 FROM GLT_CURRENT__TRANSACTION 
 WHERE LEFT(Account,4) = '9452' 
 AND Accounting_Date &gt;= @Date 
 AND Accounting_Date &lt;= Eomonth(@Date)
 AND RIGHT(Account,9) IN ('1110.0130','1110.0131','2110.0061','2110.0062')
 Group By LEFT(Account,4), RIGHT(Account,9)
)A
GROUP BY A.Entity,A.Account
</code></pre>

blocks|key|3952040|text|另一种简单的方法是使用CTE。该代码基于您上面的期初余额和移动查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3952041|DECLARE+@Date+DATETIME+=+'04/01/2019'+--Set+the+variable+for+the+first+day+of+the+period.

;with+BeginningBalance
as
(+
++++--Gather+the+beginning+or+opening+balance
++++Select+
++++++++Left(Account,4)+as+Entity,+
++++++++right(Account,9)+as+Account,+
++++++++sum(debit%2Bcredit)+as+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(@Date,-1)+--All+transaction+movement+up+until+31Mar
++++Group+By+
++++++++Left(Account,4),+
++++++++right(Account,9)
),
Activity+--Activity+/+movement
as
(
++++Select+
++++++++Left(Account,4)+as+Entity,
++++++++Right(Account,9)+as+Account,
++++++++Sum(debit%2Bcredit)Activity
++++From+
++++++++GLT_CURRENT__TRANSACTION++
++++Where+
++++++++Left(Account,4)+=+'9452'+and+
++++++++Accounting_Date+>=+@Date+and+
++++++++Accounting_Date+<=+Eomonth(@Date)+AND+
++++++++Right(Account,9)+IN+('1110.0130','1110.0131','2110.0061','2110.0062')
++++Group+By+
++++++++Left(Account,4),+
++++++++Right(Account,9)
)
SELECT+
++++ISNULL(a.Entity,+b.Entity)+as+[Entity],+--Use+entity+from+Beginning+or+activity+just+in+case+the+account+was+made+on+or+after+1APR+or+not+used+after+31Mar
++++ISNULL(a.Account,+b.Account)+as+[Account],+--Use+Account+from+Beginning+or+activity+just+in+case+the+account+was+made+on+or+after+1APR+or+not+used+after+31Mar
++++ISNULL(BBal,0)+as+[BBal],+--Zero+opening+if+there+is+no+opening+balance
++++ISNULL(Activity,0)+as+[Activity]+--Zero+activity+if+there+is+no+account+movement+within+the+period
FROM+
++++BeginningBalance+a
FULL+OUTER+JOIN+--Should+include+all+rows+from+beginning+balance+&+activity.+You+may+want+to+exclude+inactive+accounts+with+zero+balances+as+you're+summing+up+values+since+the+start+of+your+GL
++++Activity+b
ON+
++++a.Entity+=+b.Entity+AND
++++a.Account+=+b.Account|code-block|syntax|javascript|3952042|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another easy way to do this would be using a CTE. The code is based off your beginning balance and movement queries above. 

<pre><code>DECLARE @Date DATETIME = '04/01/2019' --Set the variable for the first day of the period.

;with BeginningBalance
as
( 
 --Gather the beginning or opening balance
 Select 
 Left(Account,4) as Entity, 
 right(Account,9) as Account, 
 sum(debit+credit) as 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 &lt;= EOMONTH(@Date,-1) --All transaction movement up until 31Mar
 Group By 
 Left(Account,4), 
 right(Account,9)
),
Activity --Activity / movement
as
(
 Select 
 Left(Account,4) as Entity,
 Right(Account,9) as Account,
 Sum(debit+credit)Activity
 From 
 GLT_CURRENT__TRANSACTION 
 Where 
 Left(Account,4) = '9452' and 
 Accounting_Date &gt;= @Date and 
 Accounting_Date &lt;= Eomonth(@Date) AND 
 Right(Account,9) IN ('1110.0130','1110.0131','2110.0061','2110.0062')
 Group By 
 Left(Account,4), 
 Right(Account,9)
)
SELECT 
 ISNULL(a.Entity, b.Entity) as [Entity], --Use entity from Beginning or activity just in case the account was made on or after 1APR or not used after 31Mar
 ISNULL(a.Account, b.Account) as [Account], --Use Account from Beginning or activity just in case the account was made on or after 1APR or not used after 31Mar
 ISNULL(BBal,0) as [BBal], --Zero opening if there is no opening balance
 ISNULL(Activity,0) as [Activity] --Zero activity if there is no account movement within the period
FROM 
 BeginningBalance a
FULL OUTER JOIN --Should include all rows from beginning balance &amp; activity. You may want to exclude inactive accounts with zero balances as you're summing up values since the start of your GL
 Activity b
ON 
 a.Entity = b.Entity AND
 a.Account = b.Account
</code></pre>

blocks|key|3952100|text|我觉得你把事情搞得太复杂了|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3952101|SELECT
++++LEFT(Account,4)+as+Entity,+
++++RIGHT(Account,9)+as+Account,+
++++SUM(CASE+WHEN+accounting_date+<=+EOMONTH(@date,+-1)+THEN+debit%2Bcredit+END)+as+BBal,
++++SUM(CASE+WHEN+accounting_date+>+EOMONTH(@date,+-1)+THEN+debit%2Bcredit+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)|code-block|syntax|javascript|3952102|当给定@date时，此查询将选择2018年3月31日之前的所有事务。对于每一行，在SUM中的情况将测试会计日期是过去(对于bbal)还是当前月份(对于活动)。如果特定交易的会计日期不符合规则情况(当test返回false时)，则来自情况的返回值为null，因此不对其求和|3952103|要了解有关此查询如何工作的更多信息，请运行该查询，但不包含分组/求和：|3952104|SELECT
++++LEFT(Account,4)+as+Entity,+
++++RIGHT(Account,9)+as+Account,+
++++Accounting_date,
++++(CASE+WHEN+accounting_date+<=+EOMONTH(@date,+-1)+THEN+debit%2Bcredit+END)+as+BBal,
++++(CASE+WHEN+accounting_date+>+EOMONTH(@date,+-1)+THEN+debit%2Bcredit+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)+++|3952105|对于三月份的@date，事务将如下所示：|3952106|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|3952107|查看案例中如何根据会计日期将交易金额拆分为bbal列或activity列？现在，当我们对它们求和并分组时(从查询结果中删除date列)：|3952108|1,+1,+$400,+$900++--the+$400+is+£100%2B$300,+the+$900+is+$400%2B$500|3952109|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

I think you're over-complicating things

<pre><code>SELECT
 LEFT(Account,4) as Entity, 
 RIGHT(Account,9) as Account, 
 SUM(CASE WHEN accounting_date &lt;= EOMONTH(@date, -1) THEN debit+credit END) as BBal,
 SUM(CASE WHEN accounting_date &gt; 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 &lt;= EOMONTH(@Date) 
GROUP BY 
 Left(Account,4), 
 right(Account,9)
</code></pre>

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:

<pre><code>SELECT
 LEFT(Account,4) as Entity, 
 RIGHT(Account,9) as Account, 
 Accounting_date,
 (CASE WHEN accounting_date &lt;= EOMONTH(@date, -1) THEN debit+credit END) as BBal,
 (CASE WHEN accounting_date &gt; 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 &lt;= EOMONTH(@Date) 
</code></pre>

Transactions would be like this for @date in march:

<pre><code>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
</code></pre>

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

<pre><code>1, 1, $400, $900 --the $400 is £100+$300, the $900 is $400+$500
</code></pre>

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. 

<pre><code>Declare @Date datetime = '04/01/2019'
</code></pre>

Code for beginning balance:

<pre><code> 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 &lt;= EOMONTH('04-01-2019',-1)
Group By Left(Account,4), right(Account,9)
</code></pre>

Code for sum of activity

<pre><code> 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 &gt;= '04-01-2019' and Accounting_Date &lt;= 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)
</code></pre>

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

<pre class="lang-none prettyprint-override"><code>Entity Account BBalance Activity
 9452 1110.0130 50.00 2,500
</code></pre>

Union ALL with partition

我有两个独立的查询，我想将它们合并为一个。他们自己工作得很好，我试着使用联合，但我似乎不能正确使用它。基本上，一个查询在某个日期获得平衡，另一个查询计算某一日期范围内的活动。我希望结果出现在一个接一个的列中。尝试在没有分区的情况下编写查询，并将其作为简单的group by。使用了union，但是我无法让第二个查询的列出...

问Union ALL with partition
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Union ALL with partitionEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Union ALL with partition
EN