I need some immediate help with an SQL problem which I suspect is easy if you know what you are doing. I have two tables. The first table (item, date, balance) has 2,500 records and I need to calculate what the balance was at the date of the record. The second table (item, date, quantity) has all of the transactions since the beginning of the year. 135,000 records. The same item can appear several times in each table but with different dates. I need some sql that is going to update each record in the first table that matches with records in the second table we need to compare where table1_item = table2_item and where table1_date >= table2_date and update the balance if it meets those criteria. The sql needs to loop around for each record in the first table to see it it matches the record it the second table. I ran the following sql: update table1 set table1_balance = table1_balance + table2_quantity from table2 where table2_item_item = table1_item and tavle2_date >= table1_date This updated the records but doesn't check every record in table2 and table1. Somehow it needs to use a loop to check every record in the 154,000 records of table2 against the 2,500 records in table1.
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):
a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.
b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).
Transact SQL - SQL2000