Minus One Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Minus One Query?

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-20 : 10:51:38

How would I write a query that subtracts 1 from a value?

Our parts carry a sequence number that keeps them arranged in our work orders.

Here's a sample table:


Serial_Number  WorkOrder_Number  WorkOrder_Qty  WorkOrder_Seq
C710125 1005   456201            250            158
C710125 1006   456201            250            159
C710125 1007   456201            250            160
C710125 1008   456201            250            161
C710125 1009   456201            250            162
C710125 1010   456201            250            163
C710125 1011   456201            250            164

If a part number (i.e. Serial_Number 'C710125 1007') gets started and is later removed (postpone the job or the part was damaged in production), we set it's sequence number to 0 (no longer part of the sequence). To keep the books straight, we need to take all sequence numbers greater than this and drop them down one.

The sample table should now look like this:

Serial_Number  WorkOrder_Number  WorkOrder_Qty  WorkOrder_Seq
C710125 1005   456201            250            158
C710125 1006   456201            250            159
C710125 1007   456201            250            0
C710125 1008   456201            250            160
C710125 1009   456201            250            161
C710125 1010   456201            250            162
C710125 1011   456201            250            163

How would I write a query to reduce certain sequence numbers by one?

Here's my lame attempt at a start:

UPDATE ACP_Parts
SET
  WorkOrder_Seq=(SELECT WorkOrder_Seq FROM ACP_Parts
                 WHERE
                    (WorkOrder_Number=@WorkOrder_Number) AND
                    (/* What could I write here? */)
                ) - 1
WHERE (WorkOrder_Number=@WorkOrder_Number) AND
      (WorkOrder_Seq > @WorkOrder_Seq)

Any help is certainly appreciated.

Avoid Sears Home Improvement

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-20 : 11:16:49

try this

UPDATE ACP_Parts
SET WorkOrder_Seq=WorkOrder_Seq - 1
WHERE (WorkOrder_Seq > @WorkOrder_Seq)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 11:30:15

[code]
UPDATE t
SET WorkOrder_Seq=WorkOrder_Seq - t1.PrevCount
FROM ACP_Parts t
CROSS APPLY(SELECT COUNT(*) AS PrevCount
FROM ACP_Parts
WHERE Serial_Number <t.Serial_Number
AND WorkOrder_Seq=0)t1
WHERE WorkOrder_Number=@WorkOrder_Number
[/code]

jp2code
Posting Yak Master

175 Posts

Posted - 2009-02-20 : 11:55:09

Thanks for both replies!

Visakh, your version exceeds my understanding. It is more efficient or more accurate than Raky's code?

I understand Raky's code, but if your version works better, I'll certainly take the time to understand it.

Thanks,
Joe

Avoid Sears Home Improvement

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 12:05:47

quote:

Originally posted by visakh16


UPDATE t
SET WorkOrder_Seq=WorkOrder_Seq - t1.PrevCount
FROM ACP_Parts t
CROSS APPLY(SELECT COUNT(*) AS PrevCount
            FROM ACP_Parts
            WHERE Serial_Number <t.Serial_Number  
            AND WorkOrder_Seq=0)t1
WHERE WorkOrder_Number=@WorkOrder_Number

oops this is SQL 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 12:12:00

quote:

Originally posted by sodeep

quote:

Originally posted by visakh16


UPDATE t
SET WorkOrder_Seq=WorkOrder_Seq - t1.PrevCount
FROM ACP_Parts t
CROSS APPLY(SELECT COUNT(*) AS PrevCount
            FROM ACP_Parts
            WHERE Serial_Number <t.Serial_Number  
            AND WorkOrder_Seq=0)t1
WHERE WorkOrder_Number=@WorkOrder_Number

oops this is SQL 2000.

ah...my bad

...i missed it. thanks for spotting


UPDATE t
SET WorkOrder_Seq=WorkOrder_Seq - (SELECT COUNT(*) AS PrevCount
            FROM ACP_Parts
            WHERE Serial_Number <t.Serial_Number  
            AND WorkOrder_Seq=0)
FROM  ACP_Parts t
WHERE t.WorkOrder_Number=@WorkOrder_Number

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 12:47:07

quote:

Originally posted by visakh16

quote:

Originally posted by sodeep

quote:

Originally posted by visakh16


UPDATE t
SET WorkOrder_Seq=WorkOrder_Seq - t1.PrevCount
FROM ACP_Parts t
CROSS APPLY(SELECT COUNT(*) AS PrevCount
            FROM ACP_Parts
            WHERE Serial_Number <t.Serial_Number  
            AND WorkOrder_Seq=0)t1
WHERE WorkOrder_Number=@WorkOrder_Number

oops this is SQL 2000.

ah...my bad

...i missed it. thanks for spotting


UPDATE t
SET WorkOrder_Seq=WorkOrder_Seq - (SELECT COUNT(*) AS PrevCount
            FROM ACP_Parts
            WHERE Serial_Number <t.Serial_Number  
            AND WorkOrder_Seq=0)
FROM  ACP_Parts t
WHERE t.WorkOrder_Number=@WorkOrder_Number

I guess Serial_Number must be Primary Key over here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources