Click here to Skip to main content
13,252,206 members (56,055 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi i have the following code which i would like to loop

This is the list i would like to select from

Select Distinct Serial from dbo.VwDatesReceivedVsDatesDispatched


serial should be = @Serial

Second select
Select top 1  Min(Workcompleted) dispatcheddate,min (dateReceived)Receiveddate,Serial,RID,did,dispatchorderid from  VwDatesReceivedVsDatesDispatched where Manufacturerserialnumber = '" & @serial & "' and dispatchorderid is null group by Seril,RID,did,dispatchorderid



RID should be = @rid
DID should be = @Did
to get the date Received vs DateDispatched by serial number

first check

Select * from Receiving where id =@rid


if this has rows then

Update Receiving set dispatchorderid=@did where id =@rid


Else
Select * from ReceivingArchive where id =@rid


if has rows then

Update ReceivingArchive set dispatchorderid=@did where id =@rid


loop

[EDIT]
What i need to do is to create a stored procedure which will update my Receiving table column Dispatchorderid, so that my Received date can be linked to a dispatched date,

the first query on the page shows all serials without any dispatched order id.

the second query returns the min date received and the min date dispatched as well as the Received id and dispatched id for those dates
E.G.
id | Serial |ReceievedDate | id | DispatchedDate
80 |98654 |2013-04-01    |5441| 2013-04-06

The Select * from Receiving table will look as follows before the update
id|Serial|DateReceived|Dispatchid
80|98654 |2013-04-01  |NULL

Then i need to update Receiving table to show the following
id|Serial|DateReceived|Dispatchid
80|98654 |2013-04-01  |5441
[/EDIT]
Posted 14-Apr-13 22:35pm
isi19520
Updated 15-Apr-13 10:20am
Maciej Los257.6K
v2
Comments
OriginalGriff 15-Apr-13 3:44am
   
And?
What have you tried?
Where are you stuck?
isi19 15-Apr-13 3:53am
   
I am not sure about how to get my variables(@serial,@Rid,@did)
Maciej Los 15-Apr-13 3:57am
   
Not clear. Please, describe what you want to achieve and show example data. It will be much understandable.
isi19 15-Apr-13 4:24am
   
What i need to to is create a stored procedure which will update my Receiving table column Dispatchorderid, so that my Received date can be linked to a dispatched date,

the first query on the page shows all serials without any dispatched order id.

the second query returns the min date received and the min date dispatched as well as the Received id and dispatched id for those dates
E.G.
id | Serial |ReceievedDate | id | DispatchedDate
80 |98654 |2013-04-01 |5441| 2013-04-06

The Select * from Receiving table will look as follows before the update
id|Serial|DateReceived|Dispatchid
80|98654 |2013-04-01 |NULL

Then i need to update Receiving table to show the following
id|Serial|DateReceived|Dispatchid
80|98654 |2013-04-01 |5441
ThePhantomUpvoter 15-Apr-13 16:31pm
   
"create a stored procedure" okay so create a stored procedure then. What exactly is the problem?
isi19 16-Apr-13 3:26am
   
i have created my stored proc and am able to select the serial through a cursor, how would i set the variable @did,@rid to be = did,rid from the following sql statement
Select top 1 Min(Workcompleted) dispatcheddate,min (dateReceived)Receiveddate,Serial,RID,did,dispatchorderid from VwDatesReceivedVsDatesDispatched where Manufacturerserialnumber = '" & @serial & "' and dispatchorderid is null group by Seril,RID,did,dispatchorderid

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.171114.1 | Last Updated 15 Apr 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100