help!! 
Author Message
 help!!
I have four column:
RowID, ContractID, Status, Date

(RowID is primary key which increment by one automatically)

I need a stored procedure to find out status = 'A' with 15 or more days old
date.
If find any record, then add new row with following:
Status = 'B'
Data = Today's date
ContractID = ContractID

But once I added a new row, and execute the stored procedure, it will find
at current status for the contract, if the contract already has status 'B'
then it wouldn't do any thing.



Sun, 29 Feb 2004 18:25:03 GMT
 help!!

I'm not sure I understand the problem, but I believe you just want to set
the status to B for any row with a status of A more than 15 old for which a
row with B does not already exist.  Your problem is that that there is
nothing to tie any created B row back to the A row given the structure
unless the ContractID is unique.  Adding a new B row isn't a problem
obviously, but telling whether a corresponding B row already exists is.  If
you can be sure the contractID will only be duplicated in the case of a B
row then you could use something like:

INSERT TableName (ContractID, Status, Date)
SELECT t1.ContractID, 'B', GETDATE())
FROM TableName t1
LEFT OUTER JOIN TableName t2 ON t2.ContractID = t1.ContractId AND t2.Status
= 'B'
WHERE t1.Status = 'A'
            AND DATEADD(dd, 5, Date) < GETDATE()
            AND t2.Status IS NULL

This was dome off the top of my head so you may need to tweak the syntax.
If you can't rely on the ContractID column you're going to have to change
your stucture.  One thing I would consider is adding a column to track the
RowID of the parent row when a B row is created.


Quote:
> I have four column:
> RowID, ContractID, Status, Date

> (RowID is primary key which increment by one automatically)

> I need a stored procedure to find out status = 'A' with 15 or more days
old
> date.
> If find any record, then add new row with following:
> Status = 'B'
> Data = Today's date
> ContractID = ContractID

> But once I added a new row, and execute the stored procedure, it will find
> at current status for the contract, if the contract already has status 'B'
> then it wouldn't do any thing.



Sun, 29 Feb 2004 21:09:19 GMT
 help!!
Please post DDL and not your personal narratives.  Sample data would also help.  Next, get a book or take a course on relational databases, so you will not do things like that row_id.  It is redundant, dangerous and non-relational.  Quit trying to imitate a 1960's sequential tape file in SQL -- you even said "record" and not "row" in your post!!

Finally, DATE is a reserved word in Standard SQL and should never be used as a name; date is also too vague to be a proper data element because it is too general --"the date of what??"

Is this what you meant to post?  Pardon the wild guesses about defaults, datatype and all the other stuff you did not post for people who are doing your work for you for free.  

CREATE TABLE Foobar
(contract_id INTEGER NOT NULL
             REFERENCES Contracts(contract_id),
 change_date DATE NOT NULL,
 contract_status CHAR(1) NOT NULL DEFAULT 'X'
        CHECK (status IN ('A', 'B', 'X')),
 PRIMARY KEY (contract_id, change_date));

Quote:
>> I need a stored procedure to find out status = 'A' with 15 or more days old change date. If you find any row [sic: record], then add new row with following:

contract_status = 'B'
change_date = Today's date
contract_id = contract_id <<

Here is an answer in SQL-92, which you can easily translate into your SQL dialect.  

INSERT INTO Foobar (contract_id, change_date, contract_status)
SELECT F1.contract_id, CURRENT_DATE, 'B'
  FROM Foobar AS F1
 WHERE F1.contract_id = Foobar.contract_id
   AND (CURRENT_DATE - Foobar.change_date)
       = INTERVAL 15 DAYS
   AND Foobar.contract_status = 'A';

Your specs did not say what to do if there is already a 'B' status row in the table, what do to about NULLs, etc.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 29 Feb 2004 23:15:35 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. help,help,help,help,help,help,help,help,help,help,help,help,help,

2. Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,Help,

3. help help help help help help help help help

4. SQL To ORACLE Help Help Help Help Help

5. query:HELP! HELP HELP HELP HELP

6. Help help help help

7. HELP HELP HELP HELP!

8. HELP HELP HELP HELP

9. HELP HELP HELP

10. Refresh Test Environment help help help

11. backup HELP HELP HELP

12. HELP HELP HELP!!!!


 
Powered by phpBB® Forum Software