How can I avoid manually steps every week? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How can I avoid manually steps every week?

Author

Topic

aspag
Starting Member

6 Posts

Posted - 2004-01-28 : 17:55:11

New freebee at sql

Create table #tb1 (cust varchar(5),invoice varchar(7),orderdt datetime, shipdt datetime)

Insert into #tb1 values('12345','9899','19991119','19991202')
Insert into #tb1 values('12345','9877','19991206','19991222')
Insert into #tb1 values('12345','9866','1991229','20000105')
Insert into #tb1 values('12345','9850','20000113','20000223')
Insert into #tb1 values('12345','9456','20010725','20010809')
Insert into #tb1 values('12345','9755','20011103','20011227')
Insert into #tb1 values('12345','9785','20030114','20030124')
Insert into #tb1 values('12345','9000','20030823','20030828')
Insert into #tb1 values('12345','9001','20030901','20030920')

Goal is to track all customers that exist in the database whose shipment occurred within 45 days from the next order date for each customer.

Step 1
Drop table #Temp2
Select a.cust,a.invoice,a.orderdt,a.shipdt,days = 0
,(select min(b.orderdt) from #temp1 b where((b.cust = a.cust) and (b.orderdt > a.orderdt))) as nextyorderdt
into #Temp2 from #tb1 a order by cust,orderdt

Step2
update #Temp2 set nextorderdt = shipdt where nextorderdt is null

Step3
update #Temp2 set days = case when shipdt = nextorderdt then 0 else (datediff(day,shipdt,nextorderdt)+1) end

Step4
Delete from #Temp2 where days in (Select days from #Temp2where days > 45)

Deletion process deletes rows that are > 45 days.

Have to go thru steps 1 to 4 five tmes manually for the final output of

Cust inv orderdt shipdt days
12345 9000 8/21/2003 8/28/2003 5
12345 9001 9/1/2003 9/20/2003 0

Is there an easier way to accomplish this task? Do I have to use a cursor, or can direct me to an article which can be helpful in solving my problem

Any help will be appreciated.

aspag

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-01-28 : 18:36:02

The steps outlined produce a different result than your final output.

But those steps are redundant anyway...


Select *
from #tb1 A
where DATEDIFF(dd, A.orderdt, (SELECT ISNULL(MIN(OrderDT),0) from #tb1  WHERE Cust = a.Cust and orderdt > a.orderdt)) < 45

DavidM

"SQL-3 is an abomination.."

aspag
Starting Member

6 Posts

Posted - 2004-01-29 : 08:59:54

This works great but I had to run this code 5 times to arrive to the final output as shown earlier.
Have to go thru the steps several times, to track for the same customer, if days are >45 in the database.

With the first run, five rows are still present and days between ship date to the next order date is greater then 45 days (third row ship date to the next order dt - fourth row)
Second run, 4 rows left, where days is still greater then 45
Third run 3 left where days > 45 days
Fourth run, displays no days more than 45 days and output as desired.
Is there an easier way? Please let me know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources