How to do this in SQL? - 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)

How to do this in SQL?

Author

Topic

NgKH
Starting Member

15 Posts

Posted - 2003-06-02 : 18:00:26

if given a table A
(Customer_ID, Cost, Trip_EndDate)
sorted by Customer_ID, Trip_EndDate

Customer_ID COST TRIP_ENDDATE
------------------------------------
001 200.00 01/14/1997
001 300.00 03/06/1997
001 300.00 09/06/1997
001 300.00 09/22/1997
001 300.00 09/26/1997

determine if they are the same trip by assigning a trip ID in new table called it B such that

Customer ID COST TRIP_ENDDATE TRIP_ID
------------------------------------------------
001 200.00 01/14/1997 1
001 300.00 03/06/1997 2
001 300.00 09/16/1997 3
001 300.00 09/22/1997 3
001 300.00 09/26/1997 3

determine whether each record is the same trip by

if the difference between first the records' trip_enddate with the same customer_ID is within 7 days
(e.g. 09/16/1997 and 09/22/1997 is within 7 days and then 09/22/1997 and 09/26/1997 is within 7 days) then assign same trip ID

how do you do this in SQL? does a CURSOR has to be implemented?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-02 : 19:07:03

OOhhhh ... very cool puzzle. I hope I can beat Arnold to the solution ...

(to be continued)

- Jeff

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-02 : 23:07:33

NgKH,

What are the rules for TripID?

Is TripID unique per Customer? Does the TripID have to be in a sequence per Customer?

If the answer are Yes and NO then....


--DDL (You should have done this for us!!!)
Create table trips (Customer_ID char(3), Cost money, Trip_EndDate datetime)
Insert trips (Customer_ID, COST, TRIP_ENDDATE)
SELECT '001', 200.00, '01/14/1997'
UNION ALL
SELECT '001', 300.00, '03/06/1997'
UNION ALL
SELECT '001', 300.00, '09/16/1997'
UNION ALL
SELECT '001', 300.00, '09/22/1997'
UNION ALL
SELECT '001', 300.00, '09/26/1997'
UNION ALL
SELECT '001', 300.00, '09/30/1997'
GO
--DML
--Create NewTrips table
Select *, CASE (Select COUNT(*) from Trips where T.Customer_ID = Customer_ID 
		AND DATEDIFF(d, Trip_EndDate, T.Trip_EndDate) BETWEEN 1 and 7 )
		WHEN 0 THEN (Select COUNT(*) from Trips where T.Customer_ID = Customer_ID 
AND DATEDIFF(d, Trip_EndDate, T.Trip_EndDate) >=7 ) +1
		ELSE (Select COUNT(*) from Trips where T.Customer_ID = Customer_ID 
AND DATEDIFF(d, Trip_EndDate, T.Trip_EndDate) BETWEEN 1 and 7 ) END  AS TripID INTO NewTrips
from Trips T

--Update the TripID...
Update T
	SET T.TripID = COALESCE((SELECT TOP 1 TRIPID from NewTrips WHERE T.Customer_ID = Customer_ID  
AND T.Trip_EndDate> Trip_EndDate order by TRIPID DESC), 1)
From NewTrips T
where TRIPID = 1

HTH

EDIT: Poor Format..

Edited by - byrmol on 06/02/2003 23:11:26

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources