outer joining 2 Select Statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

outer joining 2 Select Statements

Author

Topic

newbie600
Starting Member

5 Posts

Posted - 2014-04-15 : 10:54:54

I have two SELECT statement that I need to join with a LEFT OUTER JOIN. The two statements are as follows:

SELECT TLR_CHRON_JRNL.acn, TLR_CHRON_JRNL.sfx, TLR_CHRON_JRNL.trn_cd, TLR_CHRON_JRNL.trn_amt, TLR_CHRON_JRNL.tlr_no, TLR_CHRON_JRNL.trn_date, Tellers.TellerName, TELLER_BRANCH_XREF.TellerBranch
FROM SQLUser.TELLER_BRANCH_XREF TELLER_BRANCH_XREF, useProfileMgr_Tellers.Tellers Tellers, SQLUser.TLR_CHRON_JRNL TLR_CHRON_JRNL
WHERE TLR_CHRON_JRNL.tlr_no = Tellers.TellerID AND Tellers.TellerID = TELLER_BRANCH_XREF.TellerNumber AND ((TLR_CHRON_JRNL.trn_cd In ('MMO','MCC')) AND (TLR_CHRON_JRNL.trn_amt>=3000) AND (CONVERT(VARCHAR(30),TLR_CHRON_JRNL.trn_date,101)=CONVERT(VARCHAR(30),DATEADD(day,-1,GETDATE()),101)))

SELECT acn, sum(csh_amt) AS TotalCashAmt
FROM TLR_CHRON_JRNL
WHERE (CONVERT(VARCHAR(30),TLR_CHRON_JRNL.trn_date,101)=CONVERT(VARCHAR(30),DATEADD(day,-1,GETDATE()),101))
GROUP BY acn HAVING (sum(csh_amt)>= 3000) AND (sum(csh_amt) <= 10000) ORDER BY acn

I need to return all records from the first statement along with any records from the second statement where the acn values are equal. Any assistance would be greatly apreciate

Ray Bond

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-04-15 : 11:27:50

[code]
WITH Sums
AS
(
SELECT acn, SUM(csh_amt) AS TotalCashAmt
FROM TLR_CHRON_JRNL
WHERE trn_date >= DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), -1)
AND trn_date < DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0)
GROUP BY acn
HAVING SUM(csh_amt) BETWEEN 3000 AND 10000
)
SELECT J.acn, J.sfx, J.trn_cd, J.trn_amt, J.tlr_no, J.trn_date, T.TellerName, X.TellerBranch
,S.TotalCashAmt
FROM SQLUser.TELLER_BRANCH_XREF X
JOIN useProfileMgr_Tellers.Tellers T
ON X.TellerNumber = T.TellerID
JOIN SQLUser.TLR_CHRON_JRNL J
ON T.TellerID = J.tlr_no
AND X.TellerNumber= J.tlr_no
LEFT JOIN Sums S
ON J.acn = S.acn
WHERE J.trn_cd IN ('MMO','MCC')
AND J.trn_amt>=3000
AND J.trn_date >= DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), -1)
AND J.trn_date < DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0)
ORDER BY acn;
[/code]

newbie600
Starting Member

5 Posts

Posted - 2014-04-15 : 12:13:53

Forgive my ignorance, but I've tried this code suggestion in SQL Server 2008 R2 Reporting Services and get an immediate error. SSRS is looking for a SQL statement and finds WITH....

Can you help me understand the solution you've suggested...

Ray Bond

newbie600
Starting Member

5 Posts

Posted - 2014-04-15 : 14:34:30

I've done some research and now understand the syntax of your suggested solution. However, SQL Server 2008 R2 Reporting Services doesn't seem to like the syntax. It is looking for a SQL statement and doesn't like the 'WITH' beginning.....

Any additional suggestions would be most helpful...

Ray Bond

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-04-16 : 08:52:32

Try re-writing a derived table(inline view):


SELECT J.acn, J.sfx, J.trn_cd, J.trn_amt, J.tlr_no, J.trn_date, T.TellerName, X.TellerBranch
	,S.TotalCashAmt
FROM SQLUser.TELLER_BRANCH_XREF X
	JOIN useProfileMgr_Tellers.Tellers T
		ON X.TellerNumber = T.TellerID
	JOIN SQLUser.TLR_CHRON_JRNL J
		ON T.TellerID = J.tlr_no
			AND X.TellerNumber= J.tlr_no
	LEFT JOIN
	(
		SELECT acn, SUM(csh_amt) AS TotalCashAmt
		FROM TLR_CHRON_JRNL
		WHERE trn_date >= DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), -1)
			AND trn_date < DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0)
		GROUP BY acn
		HAVING SUM(csh_amt) BETWEEN 3000 AND 10000
	) S
		ON J.acn = S.acn
WHERE J.trn_cd IN ('MMO','MCC')
	AND J.trn_amt>=3000
	AND J.trn_date >= DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), -1)
	AND J.trn_date < DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0)
ORDER BY acn;

newbie600
Starting Member

5 Posts

Posted - 2014-04-16 : 11:22:28

Thank you very much....with only a slight modification to incorporate a date conversion, this works perfectly and validates with a manual process in place currently. This will allow us to automate and schedule this report daily....

Again.....THANK YOU!

Ray Bond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources