May 27, 2014 at 11:35 pm
Hello, I have 2 queries as below -
select
SUM(CASE T1.DOCTYPE
WHEN '1' THEN T1.CURTRXAM *1
WHEN '4' THEN T1.CURTRXAM *-1
WHEN '5' THEN T1.CURTRXAM *-1
WHEN '6' THEN T1.CURTRXAM *-1
END) as [Payables TB]
from PM20000 T1
select
sum(PERDBLNC) as [GL Balance]
from GL10110
where ACTINDX = '130'
which return results like this
Payables TB
1520512.30
GL Balance
-1520512.30
I would like to combine them into the one query and have a variance column with the results looking like below -
Payables TB GL Balance Variance
1520512.30 -1520512.30 0.00
Thankyou 🙂
May 28, 2014 at 12:07 am
Quick question, how do those two tables relate?
😎
May 28, 2014 at 12:17 am
Can you please post DDL and few sample records for two tables ?
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
May 28, 2014 at 1:02 am
I come from an accounting background and am self taught on SQL coding, am not sure what is meant by DDL but I will have a read and learn.
I am quite familiar with joins and have done many queries using these, so no the tables are not related.
This answer is exactly what I'm after thankyou 🙂
May 28, 2014 at 7:33 am
You could this without needing a join and windowing functions. Since you have two queries that are both just an aggregate a cross apply will make this a lot simpler.
SELECT Sum(CASE T1.DOCTYPE
WHEN '1' THEN T1.CURTRXAM * 1
WHEN '4' THEN T1.CURTRXAM * -1
WHEN '5' THEN T1.CURTRXAM * -1
WHEN '6' THEN T1.CURTRXAM * -1
END) AS [Payables TB]
FROM PM20000 T1
CROSS apply
(
SELECT Sum(PERDBLNC) AS [GL Balance]
FROM GL10110
WHERE ACTINDX = '130'
) x
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2014 at 7:33 pm
This doesn't work, it only returns the result of the first query
Payables TB
1551651.47
May 29, 2014 at 3:40 am
I know you said 'combine into one query' but don't you really want to combine the results from the 2 separate queries into one result set?
declare @payables decimal(10,2), @ledgerbal decimal(10,2)
select @payables =
SUM(CASE T1.DOCTYPE
WHEN '1' THEN T1.CURTRXAM *1
WHEN '4' THEN T1.CURTRXAM *-1
WHEN '5' THEN T1.CURTRXAM *-1
WHEN '6' THEN T1.CURTRXAM *-1
END)
from PM20000 T1
select @ledgerbal =
sum(PERDBLNC)
from GL10110
where ACTINDX = '130'
select @payables as 'payable TB', @ledgerbal as 'GL Balance', @payables - @ledgerbal as 'diff'
May 29, 2014 at 6:33 am
Sean Lange (5/28/2014)
You could this without needing a join and windowing functions. Since you have two queries that are both just an aggregate a cross apply will make this a lot simpler.
SELECT Sum(CASE T1.DOCTYPE
WHEN '1' THEN T1.CURTRXAM * 1
WHEN '4' THEN T1.CURTRXAM * -1
WHEN '5' THEN T1.CURTRXAM * -1
WHEN '6' THEN T1.CURTRXAM * -1
END) AS [Payables TB]
FROM PM20000 T1
CROSS apply
(
SELECT Sum(PERDBLNC) AS [GL Balance]
FROM GL10110
WHERE ACTINDX = '130'
) x
Tania, you need just a little step from above
SELECT z.[Payables TB], x.[GL Balance], z.[Payables TB] - x.[GL Balance]
FROM (
SELECT Sum(CASE T1.DOCTYPE
WHEN '1' THEN T1.CURTRXAM * 1
WHEN '4' THEN T1.CURTRXAM * -1
WHEN '5' THEN T1.CURTRXAM * -1
WHEN '6' THEN T1.CURTRXAM * -1
END) AS [Payables TB]
FROM PM20000 T1 ) AS z
, (
SELECT Sum(PERDBLNC) AS [GL Balance]
FROM GL10110
WHERE ACTINDX = '130'
) x
May 29, 2014 at 9:40 pm
Thankyou, obvious now that I see it 🙂
May 29, 2014 at 9:42 pm
Yes that's what I mean, it's just my amatuer terminology. Thanks for your help on this one.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply