Joins all returning same data

[ThatRickGuy]

Hey guys, this one has me scratching my head. The query should return a lease number and the amounts past due in 4 different time frames.

the rls table holds the lease information and the rar table is the accounts receivable. and unpaid invoice sits in the rar table. Each of the 4 sub querys, if run individually, works fine and brings back the exact amount due for that time frame. But when I put them all together with left joins, which ever subquery is listed first brings back the correct data, and the three others just copy that.

Should return:

Lease | Current | 30-60 | 60-90 | 90+
  1       100      100     nul    nul
  2       100      100     200    nul

is returning:

Lease | Current | 30-60 | 60-90 | 90+
  1       100      100     100    100
  2       100      100     100    100

Any thoughts?

SELECT
	rls.lse_s AS LeaseNum,
	PastDue0To30Days.AmountDue1,
	PastDue30To60Days.AmountDue2,
	PastDue60To90Days.AmountDue3,
	PastDue90PlusDays.AmountDue4
FROM
	rls 
		LEFT JOIN
		(SELECT
			lse_s,
			SUM(tamt_b_d) AS AmountDue1
		 FROM 
			rar
		 WHERE
			d_pmtdu_s > DATEADD(dd, -30, getdate())
		 GROUP BY
			lse_s) PastDue0To30Days
		ON rls.lse_s = PastDue0To30Days.lse_s 

		LEFT JOIN
		(SELECT
			lse_s,
			SUM(tamt_b_d) AS AmountDue2
		 FROM 
			rar
		 WHERE
			d_pmtdu_s <= DATEADD(dd, -30, getdate()) AND
			d_pmtdu_s > DATEADD(dd, -60, getdate())  
		 GROUP BY
			lse_s) PastDue30To60Days
		ON rls.lse_s = PastDue30To60Days.lse_s

		LEFT JOIN
		(SELECT
			lse_s,
			SUM(tamt_b_d) AS AmountDue3
		 FROM 
			rar
		 WHERE
			d_pmtdu_s <= DATEADD(dd, -60, getdate()) AND
			d_pmtdu_s > DATEADD(dd, -90, getdate())  
		 GROUP BY
			lse_s) PastDue60To90Days
		ON rls.lse_s = PastDue60To90Days.lse_s

		LEFT JOIN
		(SELECT
			lse_s,
			SUM(tamt_b_d) AS AmountDue4
		 FROM 
			rar
		 WHERE
			d_pmtdu_s <= DATEADD(dd, -90, getdate()) 
		 GROUP BY
			lse_s) PastDue90PlusDays
		ON rls.lse_s = PastDue90PlusDays.lse_s

-Rick

----------------------

http://www.ringdev.com

 

[stephlal]

I took a stab at it, I just coded it differently. since I don't have the data, I wasn't able to test it. So the query might require some small corrections.

So I assumed you had these 2 tables with these columns.

select lse_s (LEASE #), tamt_b_d (AMMT), d_pmtdu_s (DT)
from rar
select lse_s (LEASE #)
from rls

SELECT rls.lse_s, sum(rar1.tamt_b_d) AS "Current",
sum(rar2.tamt_b_d) AS "30-60",
sum(rar3.tamt_b_d) AS "60-90",
sum(rar4.tamt_b_d) AS "90+",
FROM rls rls,
     rar rar1,
     rar rar2,
     rar rar3,
     rar rar4
WHERE rls.lse_s * = rar1.lse_s
and rls.lse_s * = rar2.lse_s
and rls.lse_s * = rar3.lse_s
and rls.lse_s * = rar4.lse_s
and rar1.d_pmtdu_s > DATEADD(dd, -30, getdate())
and rar2.d_pmtdu_s <= DATEADD(dd, -30, getdate())
and rar2.d_pmtdu_s > DATEADD(dd, -60, getdate()) 
and rar3.d_pmtdu_s <= DATEADD(dd, -60, getdate())
and rar3.d_pmtdu_s > DATEADD(dd, -90, getdate())
and rar4.d_pmtdu_s <= DATEADD(dd, -90, getdate())
GROUP BY rls.lse_s

 

[ThatRickGuy]

hrm, Sybase didn't like the * =

I'm assuming this is the equivilant to oracles (+) operator for joins?

-Rick

----------------------

http://www.ringdev.com

 

[stephlal]

yeah my bad. There shouldn't be a space after the *. So the code should be this way

WHERE rls.lse_s *= rar1.lse_s
and rls.lse_s *= rar2.lse_s
and rls.lse_s *= rar3.lse_s
and rls.lse_s *= rar4.lse_s

This will work. Sybase will accept the *, I run all my queries in sybase. Give it a hoot...