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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Sql help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ann
Posting Yak Master

218 Posts

Posted - 08/23/2013 :  16:46:46  Show Profile  Reply with Quote
I need to find the row of Itemx and Discount from table b based on the SoldDate from table A. TableB SalesStartDate the items are on discount until the next SalesStartDate. For Example, I need to know the item and discount that was in effect when the solddate was 09/10/2011 - the SalesStartDate the discount is good until the next SalesStartDate and then the new Discount is applied:

Table A:
ColumnA ColumnB SoldDate
blah blah 09/10/2011

Table B:
Itemx Discount SalesStartDate
t-shirt 10% 11/01/2011
leggings 10% 09/03/2011
fleece pants 15% 09/01/2011

Results wanted:
leggings 10%

Don't know how clear I'm being, but any help would be appreciated.
Thanks

ann
Posting Yak Master

218 Posts

Posted - 08/23/2013 :  17:04:34  Show Profile  Reply with Quote
Re-reading this, maybe I'm not being very clear - I need to find the closest or equal date in TableB based on the SoldDate of TableA, but the date in TableB cannot be greater than the SoldDate in table A
Go to Top of Page

ann
Posting Yak Master

218 Posts

Posted - 08/23/2013 :  17:44:37  Show Profile  Reply with Quote
Figured this out:

SELECT *
FROM TableB
WHERE SalesStartDate= (SELECT MAX(SalesStartDate)
FROM TableB
WHERE SalesStartDate <= '09/10/2011')
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.08 seconds. Powered By: Snitz Forums 2000