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 |
|
djchrome
Starting Member
19 Posts |
 Posted - 2002-05-14 : 21:08:51
|
| My setup (extra data fields have been left out in this example):Table 1: Estimate (EstimateID, Status)Table 2: EstimateItems (EstimateItemID, EstimateID, EstimateValue)Table 3: Assignments (AssignmentID, EstimateItemID, AssignmentValue, PersonID)Table 4: TimeCards (TimeCardID, AssignmentID, TimeCardHours)My question:I have a SPROC that changes the status in the Estimate table. I need to add some sort of mechinism (sproc/trigger) that when I change that status to a value of 'Cancelled' it will check to see if there have been any TimeCard entries (down stream) associated with that Estimate. That part is taken care of with a few simple joins and an If Exists statment.The tricky part that I'm struggling with is once the Estimate is 'Cancelled' I need to (working backwards) figure out how many TimeCardHours were worked against the Assignments that were created from EstimateItems, all tied to one Estimate. And with these TimeCardHours I need it to generate a new Estimate SELECT that @@IDENTITY value and insert that into the EstimateItems table along with the TimeCardHours as the EstimateValue.Then for each item just inserted into the EstimateItems table (could be many) I need to get each of those @@IDENTITY values and insert them into the Assignment table along with the TimeCardHours as the AssignmentValue and the same PersonID as in the original Assignment.And the finish the process I need to take the @@IDENTITY values of the rows just inserted into the Assignments table in the previous step and UPDATE the TimeCards table so that they match up with the newly created Assignments. FEEWW!! I hope that makes sense. I am really stuck on this one... please let me know if you need clarification... I wrote this at 6pm... Zzzzzz |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-14 : 21:18:58
|
OK, here is a really obvious question: why cancel the original Estimate, if all you're doing is recreating it? It's like buying a brand new car, then getting rid of it right away, just to buy another car EXACTLY like it, but paying for both!And I REALLY HOPE that the explanation is not a part of the fields you left out, because it really drives me nuts when people leave out crucial, explanatory information, just for the sake of brevity.  I'm not complaining really, it's just a pet peeve of mine. |
 |
|
|
djchrome
Starting Member
19 Posts |
Posted - 2002-05-15 : 12:42:41
|
| Well I'm not recreating the exact same estimate, for example... The estimate could be for 200 hours. From those hours 20 of which have been turned into 6 Assignments for 4 different people. Of those assignments 2 of those people have each worked on 2 of the hours assigned (TimeCard), a total of 4 hours. The estimate I am tryin to generate is only for the 4 hours that have been worked. The remaining 196 hours need to be Cancelled and still tie back to the original EstimateID. |
|
|
|
|
|
|
|
|
Insert serveral records and use the @@Identitys