February 2, 2009 at 5:41 am
There is a Trandet table and TranMast table.
Trandet Table has TrandetID (PK), TranMastID (FK), OnHold as columns
TranMast Table has TranMastID (PK), OnHold as columns.
OnHold column is a bit, so values are 0 or 1.
* TranMastID is always unique and no multiple records with same TranMastID in TranMast Table.
But in TranDet table, there can be multiple records with same TranMastID. And OnHold is 0 or 1
for same TranMastID in Trandet Table. Depends on the boolean condition.
What I need to write is a stored procedure which checks the following information and
updates accordingly:
The parameters I would be passing are @TranMastID and @OnHold (0 or 1)
- In TranMast Table for a TranMastID if OnHold = 0, then check for Trandet OnHold column and
make sure that OnHold column is zero for all the records with same TranMastID in Trandet table.
In TranMast Table for a TranMastID if OnHold = 1, then check for Trandet OnHold column and
maker sure that atleast one OnHold column for the same TranMastID is 1.
If OnHold column for every TranmastID in Trandet table is 0, then update TranMast Table, OnHold
column to zero otherwise, let OnHold = 1 in TranMast Table if anyof the records in Trandet table
OnHold = 1.
A small example:
TranMast Table:
TranMastID OnHold
107734 1
TranDet Table:
TranDetID TranMastID OnHold
1055 107734 0
1056 107734 0
1080 107734 1
As seen in the above example, because Trandet table has one record OnHold = 1 for 107734
In TranMast Table, OnHold = 1 which is true.
But, if all OnHold = 0 in Trandet table for 107734 and if still TranMast table OnHold = 1
Then, update TranMast table to 0.
If OnHold=0 in Trandet for all records with 107734, then OnHold should be updated to 0 in TranMast table.
February 2, 2009 at 7:17 am
Before posting the solution, I strongly suggest you reading this article on how to post questions to get quick & better responses[/url], it not only saves time of the posters but also yields to better/quick responses.
Here is the solution that you're looking for....
UPDATEM
SETM.OnHold = D.OnHold
FROMdbo.TranMast M
INNER JOIN
(
SELECTTranMastID, MAX( CONVERT( TINYINT, OnHold ) ) AS OnHold
FROMdbo.Trandet
WHERETranMastID = @TranMastID
GROUP BY TranMastID
) D ON M.TranMastID = D.TranMastID
WHEREM.OnHold != D.OnHold--this is to avoid same value update
--Ramesh
February 3, 2009 at 3:04 am
Hi Ramesh,
it is very usefull. thank you very much.:)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply