Click here to Skip to main content
11,709,844 members (77,417 online)
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 21:35pm
isi19538
Edited 15-Apr-13 9:20am
(no name)194.1K
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 Sergey Alexandrovich Kryukov 514
1 OriginalGriff 250
2 Peter Leow 229
3 Maciej Los 180
4 Mika Wendelius 175
0 OriginalGriff 9,428
1 Sergey Alexandrovich Kryukov 9,037
2 CPallini 5,189
3 Maciej Los 4,991
4 Mika Wendelius 3,856


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