Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
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
isi19464
Edited 15-Apr-13 10:20am
(no name)151.9K
v2
Comments
OriginalGriff at 15-Apr-13 3:44am
   
And?
What have you tried?
Where are you stuck?
isi19 at 15-Apr-13 3:53am
   
I am not sure about how to get my variables(@serial,@Rid,@did)
Maciej Los at 15-Apr-13 3:57am
   
Not clear. Please, describe what you want to achieve and show example data. It will be much understandable.
isi19 at 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 at 15-Apr-13 16:31pm
   
"create a stored procedure" okay so create a stored procedure then. What exactly is the problem?
isi19 at 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
0 OriginalGriff 386
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 215
3 Maciej Los 169
4 /\jmot 169
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 15 Apr 2013
Copyright © CodeProject, 1999-2014
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