April 26, 2004 at 4:10 am
I have a table of items, with multiple records for each item, representing different prices for different periods, and a table which defines supplier rebates for different periods (may be different than item periods)
For example:
ITEMS: itemno, supplier_id, price, valid_from, valid_to:
1, 1, 1000, '01-01-2001'. '12-31-2001'
1, 1, 1100, '01-01-2002'. '12-31-2002'
1, 1, 1200, '01-01-2003'. '12-31-2003'
SUPPLIER_REBATES:
supplier_id, rebate, rebate_valid_to:
1, 10, '10-31-2002' (10% until 10-31-2002)
1, 12, '12-31-2010' (12% until 12-31-2010)
I need a query to list all items, with the corresponding rebate, according to valid_from date, e.g.:
RESULT: itemno, price, valid_from, valid_to, rebate
1, 1000, '01-01-2001'. '12-31-2001', 10
1, 1100, '01-01-2002'. '12-31-2002', 10
1, 1200, '01-01-2003'. '12-31-2003', 12
How do I do that?
Thanks in advance for your help
April 26, 2004 at 4:53 am
haven't checked this but it should work.
select i.itemno, i.price, i.valid_from, i.valid_to, r.rebate
from items i
inner join supplier_rebate r on (i.supplier_id=r.supplier_id)
where (r.rebate_valid_to between i.valid_from and i.valid_to)
hope this helps
cheers
dbgeezer
April 26, 2004 at 9:39 am
You'd save yourself effort with this query and probably others if you had a 'rebate_valid_from' field in the rebate table.
Your sample data shows everything running conveniently from year-end to year-end, but what if a rebate runs from November to March? The Supplier would presumably be entitled to one level of rebate for Nov and Dec, then a different level for Jan to Mar.
April 27, 2004 at 7:01 pm
/* CODE BETTER, FUNCTION BETTER */
-- Your code should read like it runs
select getdate()
where getdate() between getdate() and getdate()
-- equates to this
select getdate()
where getdate() >= getdate()
and getdate() <= getdate()
-- IF you have times hidden in datetime
-- this happens differently by programs
-- and it's unstable in trusting the time
-- to be 00:00:00 AM always
-- (use the convert & eliminate the time)
-- Use this instead !!
select CONVERT(varchar(11),getdate())
where CONVERT(varchar(11),getdate())
between CONVERT(varchar(11),getdate())
and CONVERT(varchar(11),getdate())
-- try this and see that the time is not being used
-- replace the getdate() function with your dates
Coach James
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply