asked on

SQL SP

I have the following tables and data. How can I create SP to achieve the following result?

CustService
ReqId(PK)	desc	pickstatus(bit)
101	some text 	0
201	some text	0

Assignments
AId(PK)	ReqId(FK)	Status(bit)	                 workSequence (int)              CompletionDate
1	101	0		1	                     NULL
2	101	0		2		NULL	
3	101	0		3		NULL
4	201	0		1		NULL

Open in new window

I want to update the first row in the second table.

before updating some conditions need to be met.
no need to update for the follwoing:
1. if pickstatus = 1 for the selected reqId
2. if status=true for the selected row
3. if any worksequence less than the seleted record(reqId) exists and its status=0
4. if completionDate is > the supplied date.

values supplied.
ReqId,
AId
workSequence
status
completionDate

Please help

ayha

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ayha1999

ASKER

sorry for my unclear explanation.

How are you defining the first row?
Aid is PK column. I want to update the Aid 1 to make its status 1.

Your requirement if status=true for the selected row does not seem to match your data that appears to have 0 in that column.
Aid 1(worksequence 1) belongs ReqId 101, so I want to check any records existing for 101 with its status=0 and and its worksequence is less the Aid 1 record. status =0 means work not approved. eg. worksequence 2 cannot be approved without approving worksequence 1 belongs to the same ReqId.

if any worksequence less than the seleted record(reqId) exists and its status=0
When you refer to selected record(reqId), are yo indicating a parameter was passed in to indicate which one to select or what?
Yes. I am pasing ReqId, Aid, worksequence, completion date.

if completionDate is > the supplied date.
Again, what is the source of the supplied date?
let's say we want to upadte Aid 2 (worksequence 2, suppose worksequence 1 is already approved(status=1), then I want to check the supplying completion date for Aid 2 is higher than the completion date of Aid 1 record. eg. completion date for Aid 1 is 05/05/2011 and user gives 05/04/2011 which is not acceptable bcz the date is less than the previous record's date.

thanks

8080_Diver

ayha1999,

Some of us occasionally get caught up in doing the work we are getting paid to do and temporarily have to set aside the work we are doing pro bono. Such was the case for me on Thursday and Friday.

If you wish to continue with the closing of this question, that is your prerogative; however, I am now (at least temporarily) able to spend some time answering this question.

ayha1999

ASKER

sorry Diver. Why I requsted is, in my experience, may of my questions are ignoned by experts ( may be not intentionally) and have posted a new ones to get answers. I thought this may be one of them.

please continue if you sometime free.

thanks

ayha

8080_Diver

If you have posted a new question, then that should be the question that is active and this one should be closed. However, you shouldn't post a new question to get the same answer you are seeking in an already posted question until the first question is actually closed.

Hopefully, you provided at least as much information in the second question as you provided in your clarification. (As an aside, if you had included the parameters being passed when you initially posted this question, it would have better clarified things from the beginning. ;-)

ayha1999

ASKER

No new question is active now for the same. Let's do it for ReqId = 101,id = 1, worksequece =1 and date 05/09/2011 10:00:00

ayha1999

ASKER

Thanks