help writing a mssql 2000 script 1

[tbscmgi]

I have a number table with an account number and I want to find that account number in a balance table and when I find that account number I want the most recent one by date, if account not found I want to look in the previous month balance table.

Thank for any help I can get.

 

[ptheriault]

Ok,

JOIN (select b.accountno
        ,max(b.balancedate) as Date
         FROM #test_ANB a
         JOIN balance_2007jan b
         on a.number = b.accountno
        GROUP BY b.accountno) b

This is a derived table. It's name is b. the columns in your table are accountno and Date.
Do you see where you are trying to join c.balancedate to b.balancedate? That won't work because there is no field in b name balancedate. you need to change that to b.date. Make the same change in your select.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[tbscmgi]

Paul-
This work fine.
Now I also want the account that not found in the balance table.

SELECT b.AccountNo, b.Date, c.OpenBalance
FROM balance_2007jan c
JOIN (select b.accountno, max(b.balancedate) as Date
FROM #test_ANB a
JOIN balance_2007jan b
ON a.number = b.accountno
GROUP BY b.accountno) b
ON c.accountno = b.accountno and c.balancedate = b.date
order by b.accountno


Thanks
Tony

 

[ptheriault]

Change the join to the derived table to left join.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[tbscmgi]

Paul-
That work great.

Thank again for all of your help.

Thanks
Tony

 

[ptheriault]

no problem

- Paul
- If at first you don't succeed, find out if the loser gets anything.