Multiple subselect problem

[rogerzebra]

Hi,
I have a problem with a multiple subquery and I was hoping that someone could help me out on this one. I’m trying to filter out duplicated policies from my list so they just beeing counted once. The first subselect filters out policies with several transactions and using max (date) shows policies with from the most reasant date.

The problem I have is when a policy has more than one transaction on the same day. Each policy has a transaction number and its increases each time the policy has changed. So my next subselect sorts out all policies except for the one with the highest transaction number. The query works, but my problem is that my transaction date column gives me a lot of nulls in the end result??

Here is my code

 Select Policies
,a.transactiondt 
,a.transactionNumber
FROM table1 b
left join table2 a
	on b.Id = a.Id 
	and a.SystemId = b.SystemId
	and TransactionDates = 	
(
				select max(c.TransactionDates)
				from table2 c
				where c.SystemID = a.SystemId
				)
	and TransactionNumber =	(
				select max(d.TransactionNumber)
				from table2 d
				where d.SystemID = a.SystemID
				)

WHERE ….

I would appreciate if someone can point out what I'm doing wrong here.
Thanks
/r

 

[skuhlman]

Your code looks like it will work as long as the latest transaction number for an ID is on the latest date. The problem is when an earlier date has a higher transaction number than a later date's transaction. Your join conditions are saying that 1) the date matches the highest date and 2) the transaction number matches the highest transaction number of all dates. If you take the date AND statement and place that sub-select within the subselect for the max number, it will make sure that it's getting the max number of transactions only on the max date and then getting the exact data for only the transaction which has that exact number.

Not knowing the layout of your database, I haven't been able to test the code, but I think this will work:

Select Policies
,a.transactiondt 
,a.transactionNumber
FROM table1 b
left join table2 a
    on b.Id = a.Id 
    and a.SystemId = b.SystemId
    and TransactionNumber =    (
                select max(d.TransactionNumber)
                from table2 d
                where d.SystemID = a.SystemID
and TransactionDates =     
(
select max(c.TransactionDates)
from table2 c
where c.SystemID = a.SystemId
)
                )