SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INNER JOIN		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divan
Posting Yak Master

122 Posts
Posted - 08/28/2012 :  12:58:24  Show Profile  Reply with Quote
I HAVE TABLE "A" AND TABLE "B" WHERE THE NUMBER AND THE DATE ARE THE KEY. IN TABLE "B" THERE IS ANOTHER FIELD AMOUNT.

I NEED TO DO AN INNER JOIN OF TABLE "A" AND TABLE "B" IN SUCH A WAY THAT I GET THE NOT THE MOST RECENT RECORD OF TABLE "B" BUT RATHER ONE RECORD BEFORE THE MOST RECENT RECORD IN TABLE "B" THIS IS BECAUSE WHEN RECORDS ARE BEING DELETED IN THE APPLICATION THE SYSTEM CREATES AN ADDITIONAL RECORD IN TABLE 'B' WHERE THE AMOUNT IS ZERO AND I NEED TO PRINT AN AMOUNT WHICH IS IN THE RECORD PRIOR TO THE LAST RECORD.

TABLE A

1234 1/1/12
1234 5/2/12


TABLE b

1234 1/1/12 500.00
1234 5/2/12 0.00

I NEED TO GET THE INFO 500.00 FROM TABLE "B"

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 08/28/2012 :  13:06:47  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
;with cte as
(
select *, seq = row_number() over (partition by id order by dte desc) from tblb
)
select *
from tbla a
join cte b
on a.id = b.id
and a.dte = b.dte
and b.seq = 2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

divan
Posting Yak Master

122 Posts
Posted - 08/28/2012 :  14:24:48  Show Profile  Reply with Quote
THIS SEEMS TO BE NOT WORKING... IS THERE A WAY TO SELECT THE ONE TO THE LAST RECORD IN A SINGLE TABLE.. WHERE THE KEY CAN BE EITHER THE NUMBER OR DATE OR BOTH???
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 08/28/2012 :  22:35:08  Show Profile  Reply with Quote 

select *
from
(
select *,row_number() over (partition by id order by dte desc) as seq
from tbla a
join cte b 
on a.id = b.id
and a.dte = b.dte
and b.amount >0
)t
where seq=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000