Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jyoti_borkar
Starting Member
1 Post |
 Posted - 2006-10-03 : 08:00:55
|
| I have tables like order_item which is detail table consisting of multiple lines for one order having order_id,order_date, order_amount,state,commodity_code & other fields. Another table is taxrate_commodity which has fields like state,commodity_code,tax_rate,effective_date.There are diff taxes defined in the taxrate_commodity table and according to the effective_date data tax_rate should be picked up from taxrate_commodity table. For ex. there is effective_date as '2004-08-01', tax_rate as 7.00, another effective_date is '2006-08-01', tax_rate as 6.00 Order_item table can be joined to taxrate_commodity table on state, commodity_code.Now If order_date is '2006-07-30' then it should pick up effective_date as '2004-08-01' and rate as 7.00And if order_date is >= '2006-08-01' then it should pick up '2006-08-01' as effective_date and 6.00 as rate. i.e 1 row for each order. but I'm getting 2 rows one for effective_date '2004-08-01' and another for '2006-08-01'. Hence I'm not able to calculate tax properly. Can anyone suggest me best query to find out the solution?Thanks and Regards,Jyoti |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-03 : 08:04:00
|
| Provide table layout and some sample data, please.Also, how do you JOIN the two tables?Peter LarssonHelsingborg, Sweden |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-10-03 : 09:47:43
|
| Follow the links in my sig for faster help![Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
|
|
|
|
Query Problem