• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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
0
ayha1999
Asked:
ayha1999
  • 4
  • 3
1 Solution
 
8080_DiverCommented:
I want to update the first row in the second table.

How are you defining the first row?  There is no guaranteed order for data returned from a query unless you specify an ORDER BY clause.

Your requirement if status=true for the selected row does not seem to match your data that appears to have 0 in that column.

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?

if completionDate is > the supplied date.
Again, what is the source of the supplied date?
0
 
ayha1999Author Commented:
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
0
 
8080_DiverCommented:
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.

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
ayha1999Author Commented:
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
0
 
8080_DiverCommented:
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. ;-)
0
 
ayha1999Author Commented:
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
0
 
ayha1999Author Commented:
Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now