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 |
|
geyes
Starting Member
1 Post |
 Posted - 2012-04-19 : 13:38:37
|
| I have two files:1)Invoice file2)Cost fileI would like to create an output file/report called margin report.I want to link the sales to find the cost for the order. It is based on date and time.See example below for the output. What is the best SQl script for this?Invoice fileOrder1 Item1 Sales1 Date1 Time11001 A1001 10.00 04/15/12 1510251002 A1001 12.00 04/15/12 1511121003 A1001 11.00 04/15/12 1712351004 A1001 14.00 04/15/12 1715431005 A1001 13.50 04/15/12 171855Cost fileItem2 Cost2 Date2 Time2 A1001 3.50 04/14/12 171255A1001 4.20 04/15/12 151233A1001 2.50 04/15/12 171523A1001 4.00 04/15/12 171623Output file - Margin reportOrder Item Sales Cost Margin1001 A1001 10.00 3.50 6.50 1002 A1001 12.00 3.50 8.50 1003 A1001 11.00 2.50 8.50 1004 A1001 14.00 2.50 11.50 1005 A1001 13.50 4.00 9.50 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-19 : 14:08:46
|
| What is the rule you are using to associate a row in the invoice file with a row in the cost file?a) Row that has the closes date&time in cost file?b) Row that has the closest date&time in the cost file that is earlier than the date&time in the Invoice file?c) Something else? Seemed to me like it is something else. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-19 : 14:15:15
|
| Cant make out any relationship by which you're getting the result. how do you determine which cost need to applied for a sales? some cases you're taking cost having effective datetime after the sales, why is it so?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Link files by date and time