Looping ???????

[aspag]

Looping ????
I am a new at sql and try to obtain faster results.

From the database, the ultimate result should be to show customers whose number of days is less than 46 from the first ship date to next order date (which is greater than the ship date).

Cust ivoice orderdt shipdt
12345 9899 11/19/1999 12/2/1999
12345 9877 12/6/1999 12/22/1999
12345 9866 12/29/1999 1/5/2000
12345 9850 1/13/2000 2/23/2000
12345 9456 7/25/2001 8/9/2001
12345 9755 11/3/2001 12/27/2001
12345 9785 1/14/2003 1/24/2003
12345 9000 8/21/2003 8/28/2003

Step1
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 #temp1 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

Custid Ivoice orderdt shipdt next order dt days
12345 9899 11/19/1999 12/2/1999 12/6/1999 5
12345 9877 12/6/1999 12/22/1999 12/29/1999 8
12345 9866 12/29/1999 1/5/2000 1/13/2000 9
12345 9850 1/13/2000 2/23/2000 7/25/2001 519
12345 9456 7/25/2001 8/9/2001 11/3/2001 87
12345 9755 11/3/2001 12/27/2001 1/14/2003 384
12345 9785 1/14/2003 1/24/2003 8/21/2003 210
12345 9000 8/21/2003 8/28/2003 8/28/2003 0

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

Row 4 – 8 is deleted.

Step1-4 is repeated at least 5 times to arrive at the ouput of

custid ivoice orderdt shipdt next order dt days
12345 9000 8/21/2003 8/28/2003 8/28/2003 0


Is there an easier way to accomplish this task?

I am trying to put create a stored procedure for quicker results.

Any help will be appreciated.

Thank you

 

[SemperFiDownUnda]

Can you explain this a bit more in detail because I don't understand just what you want by this description.

"From the database, the ultimate result should be to show customers whose number of days is less than 46 from the first ship date to next order date (which is greater than the ship date). "

"number of days is less then ..."
looks like your missing some info just before this
It appears you skipped tracks and tried to explain what you want in 2 different ways.

Do you want all records who's (orderdt - shipdt) < 46?


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[aspag]

Please add an extra row to the original table:
12345 9001 9/1/2003 9/20/2003 - Row # 9

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.

As you see that by doing step 1 – 3 days calculated were 519, 87,387, 210 for rows 4-7(shipdate – next order date). These rows were deleted from the temp table by step 4 as days were greater then 45 days.

Another temp table was created with row 1. 2 3, 8 & 9 as rows 4-7 were deleted.

Days calculated and arrived at 1325 from the ship date of 1/5/00 to the next order date 8/21/2003 . Row 3 is deleted.

Third temp table was created with row 1. 2 8 & 9.

Days calculated and arrived at 1339 from the ship date of 12/22/99 to the next order date 8/21/2003 . Row 2 is deleted.

Fourth temp table was created with row 1. 8 & 9

Days calculated and arrived at 1359 from the ship date of 12/2/99 to the next order date 8/21/2003 . Row 1 is deleted.

Final report was displayed with
Cust inv orderdt shipdt days
12345 9000 8/21/2003 8/28/2003 5
12345 9001 9/1/2003 9/20/2003 0

Hope I have simplified

Thanks for taking the time in going thru this notes in detail.