我有两个独立的查询,我想将它们合并为一个。他们自己工作得很好,我试着使用联合,但我似乎不能正确使用它。基本上,一个查询在某个日期获得平衡,另一个查询计算某一日期范围内的活动。我希望结果出现在一个接一个的列中。
尝试在没有分区的情况下编写查询,并将其作为简单的group by。使用了union,但是我无法让第二个查询的列出现在主select语句中。
Declare @Date datetime = '04/01/2019'
期初余额代码:
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)
活动总和代码
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)
如何将这两个组合成一行?尝试联合,但在第二个查询上不起作用。
Entity Account BBalance Activity
9452 1110.0130 50.00 2,500
发布于 2019-05-11 18:31:20
我觉得你把事情搞得太复杂了
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)
当给定@date时,此查询将选择2018年3月31日之前的所有事务。对于每一行,在SUM中的情况将测试会计日期是过去(对于bbal)还是当前月份(对于活动)。如果特定交易的会计日期不符合规则情况(当test返回false时),则来自情况的返回值为null,因此不对其求和
要了解有关此查询如何工作的更多信息,请运行该查询,但不包含分组/求和:
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)
对于三月份的@date,事务将如下所示:
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
查看案例中如何根据会计日期将交易金额拆分为bbal列或activity列?现在,当我们对它们求和并分组时(从查询结果中删除date列):
1, 1, $400, $900 --the $400 is £100+$300, the $900 is $400+$500
发布于 2019-05-11 16:02:36
另一种简单的方法是使用CTE。该代码基于您上面的期初余额和移动查询。
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 <= 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 >= @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
发布于 2019-05-11 14:56:15
试试这个-
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 <= 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 >= @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
https://stackoverflow.com/questions/56086751
复制相似问题