Stored Procedure Problem - 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

SQL Server Development (2000)

Stored Procedure Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-09 : 08:04:49

James writes "Hi,

Assume I have the following data in a table to record warehouse information:

WrhsItem# Act. Date Activity
--------- --------- --------
123456789 5/18/2002 Shipped
123456789 5/18/2002 Received



654231000 4/12/2002 Received



753428691 6/30/2002 Shipped
753428691 6/30/2002 Received



987654321 3/10/2002 Shipped
987654321 3/10/2002 Transferred
987654321 3/10/2002 Received

I need to write a stored procedure in SQL Server version 6.50.201 that will delete all the records for warehouse items that have been shipped and whose activity date (Act. Date) is greater than 30 days. In the data set above, I would want to delete the all the records for WarehouseItem# 123456789 and 987654321. I would not want to delete 654321000 because it has not been shipped and would not delete 753428691 because it is not 30 days old. Do you know how to write a stored procedure to do this?

Thanks,
-James"

<edit>to adjust code display</edit>

Edited by - robvolk on 07/09/2002 09:02:31

dsdeming

479 Posts

Posted - 2002-07-09 : 08:12:27

Something like this should work:

DELETE FROM YourTable
WHERE WrhsItem# IN (
SELECT WrhsItem# FROM YourTable WHERE Activity = 'Shipped' )
AND DATEDIFF( day, Act. Date, getdate()) > 30

MakeYourDaddyProud

184 Posts

Posted - 2002-07-09 : 08:26:53

Why use a subquery?...

DELETE FROM YourTable
WHERE Activity = 'Shipped'
AND DATEDIFF( day, [Act. Date], getdate()) > 30

If your columns have spaces 'Act.Date' you need parenthesis.

Daniel Small MIAP
www.danielsmall.com IT Factoring

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-09 : 09:19:51

quote:

Why use a subquery?...

Because the requirement was to delete all the records for the item, not just the 'shipped' record.

to take advantage of indexes on [Act. Date] you should use


...
where [Act. Date] < dateadd(dd, -30, getdate())

<O>

Edited by - Page47 on 07/09/2002 09:21:20

MakeYourDaddyProud

184 Posts

Posted - 2002-07-09 : 10:48:55

Ok, I see what you mean now...

quote:

DELETE FROM YourTable
WHERE WrhsItem# IN (
SELECT WrhsItem# FROM YourTable WHERE Activity = 'Shipped' )
AND DATEDIFF( day, Act. Date, getdate()) > 30

Hmm... Correct me if i'm wrong but the above query still won't work. You will delete warehouseitems that are shipped but not in the 30 day range for the dataset. The 30 day range applies to rows out side that scope but for the marker delete i.e.

WrhsItem# Act. Date Activity
--------- --------- --------
123456789 5/18/2002 Shipped
123456789 5/18/2002 Received

654231000 4/12/2002 Received

753428691 6/30/2002 Shipped

753428691 6/30/2002 Received

987654321 3/10/2002 Shipped

987654321 3/10/2002 Transferred

987654321 3/10/2002 Received

... consider the row highlighted in red for a moment, the 'transferred' row satifies the requirement for 30 day rule, but not the 'shipped' row hence you will be deleting all warehouseitems that have any row > 30 days not solely the shipped on.

You need to subgroup the DATEDIFF phrase within the subquery also.

Daniel Small MIAP
www.danielsmall.com IT Factoring

MakeYourDaddyProud

184 Posts

Posted - 2002-07-09 : 10:52:29

Sorry, I didn't change the date so that the 'shipped' was within 30 days and the 'transferred' outside 30 days. Problem still applies though.

Daniel Small MIAP
www.danielsmall.com IT Factoring

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-09 : 10:54:35

Yeah, the requirements aren't crystal.

quote:

In the data set above, I would want to delete the all the records for WarehouseItem# 123456789 and 987654321.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources