April 1, 2008 at 2:25 pm
I've included a link to a CSV of test data if anyone wants to try it out themselves:
http://www.sendspace.com/file/mbv2z8
--------------------------------------------------------------------------------------------
here is the build script for the test table:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_S_T_C_TEST](
[sched_version] [tinyint] NOT NULL,
[trip_id] [int] NOT NULL,
[bs_seq] [smallint] NOT NULL,
[bs_id] [int] NOT NULL,
[bs_sname] [varchar](8) NOT NULL,
[bs_lname] [varchar](35) NOT NULL,
[longitude] [float] NOT NULL,
[latitude] [float] NOT NULL,
[new_stop_sequence] [int] NULL,
[seq_num] [int] NULL,
[eta] [datetime] NULL,
[new_eta] [datetime] NULL,
[DISTANCE] [float] NULL,
[min_eta] [datetime] NULL,
[max_eta] [datetime] NULL,
[time_ratio_s] [float] NULL,
[SUM_DISTANCE] [float] NULL,
[RATIO_DISTANCE] [float] NULL,
[Stop_Time] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [NC_SchedV_Trip_BSSeq] ON [dbo].[T_S_T_C_TEST]
(
[sched_version] ASC,
[trip_id] ASC,
[bs_seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--------------------------------------------------------------------------------------------
here is the PROBLEMATIC sql:
go
UPDATE [T_S_T_C_TEST]
SET eta =
(SELECT dateadd(ms,
datediff(ms, a.eta, c.eta) * 1.0 *
(b.New_Stop_Sequence - a.New_Stop_Sequence) /
(c.New_Stop_Sequence - a.New_Stop_Sequence),
a.eta)
FROM [T_S_T_C_TEST] a
CROSS JOIN [T_S_T_C_TEST] c
WHERE a.Trip_ID = b.Trip_ID
AND a.New_Stop_Sequence IN (SELECT MAX(a1.New_Stop_Sequence)
FROM [T_S_T_C_TEST] a1
WHERE a1.Trip_ID = b.Trip_ID
AND a1.New_Stop_Sequence < b.New_Stop_Sequence
AND a1.eta IS NOT NULL)
AND c.Trip_ID = b.Trip_ID
AND c.New_Stop_Sequence IN (SELECT MIN(c1.New_Stop_Sequence)
FROM [T_S_T_C_TEST] c1
WHERE c1.Trip_ID = b.Trip_ID
AND c1.New_Stop_Sequence > b.New_Stop_Sequence
AND c1.eta IS NOT NULL)
)
FROM [T_S_T_C_TEST] b
WHERE b.eta IS NULL
It keeps returning the following error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. etc
----------------------------------------------------------------------------------------------
I have a proof of concept script that completes successfully, but in my testing environment I've had no success. I even tried a cursor but the performance was extremely poor.
proof of concept script: (below)
CREATE TABLE testtrips(Trip_ID char(6),
Arrival_Time datetime,
Stop_ID int,
Stop_Sequence tinyint,
PRIMARY KEY (Trip_ID, Stop_Sequence))
INSERT testtrips
SELECT 'FR0002', '00:01:27:00', 2134, 1 UNION ALL
SELECT 'FR0002', NULL , 2135, 2 UNION ALL
SELECT 'FR0002', NULL , 2136, 3 UNION ALL
SELECT 'FR0002', NULL , 2137, 4 UNION ALL
SELECT 'FR0002', '00:01:33:00', 2138, 5 UNION ALL
SELECT 'FR0002', NULL , 2139, 6 UNION ALL
SELECT 'FR0002', NULL , 2140, 7 UNION ALL
SELECT 'FR0002', '00:01:35:00', 2141, 8 UNION ALL
SELECT 'FR0003', '00:03:16:00', 2142, 1 UNION ALL
SELECT 'FR0003', '00:03:17:00', 2144, 2 UNION ALL
SELECT 'FR0003', NULL , 2154, 3 UNION ALL
SELECT 'FR0003', NULL , 2164, 4 UNION ALL
SELECT 'FR0003', '00:03:20:00', 2174, 5
select * from testtrips
go
UPDATE testtrips
SET Arrival_Time =
(SELECT dateadd(ms,
datediff(ms, a.Arrival_Time, c.Arrival_Time) * 1.0 *
(b.Stop_Sequence - a.Stop_Sequence) /
(c.Stop_Sequence - a.Stop_Sequence),
a.Arrival_Time)
FROM testtrips a
CROSS JOIN testtrips c
WHERE a.Trip_ID = b.Trip_ID
AND a.Stop_Sequence = (SELECT MAX(a1.Stop_Sequence)
FROM testtrips a1
WHERE a1.Trip_ID = b.Trip_ID
AND a1.Stop_Sequence < b.Stop_Sequence
AND a1.Arrival_Time IS NOT NULL)
AND c.Trip_ID = b.Trip_ID
AND c.Stop_Sequence = (SELECT MIN(c1.Stop_Sequence)
FROM testtrips c1
WHERE c1.Trip_ID = b.Trip_ID
AND c1.Stop_Sequence > b.Stop_Sequence
AND c1.Arrival_Time IS NOT NULL)
)
FROM testtrips b
WHERE b.Arrival_Time IS NULL
go
SELECT * FROM testtrips ORDER BY Trip_ID, Stop_Sequence
go
drop table testtrips
--------------------------------------------------------------------------------------------
Any help would be appreciated
April 1, 2008 at 7:18 pm
My apologies,
I need to update the Create Table script so that the csv can be loaded into the table
CREATE TABLE [dbo].[T_S_T_C_TEST](
[sched_version] [tinyint] NOT NULL,
[trip_id] [int] NOT NULL,
[bs_seq] [smallint] NOT NULL,
[bs_id] [int] NOT NULL,
[new_stop_sequence] [int] NULL,
[seq_num] [int] NULL,
[eta] [datetime] NULL,
[new_eta] [datetime] NULL,
[DISTANCE] [float] NULL,
[time_ratio_s] [float] NULL,
[SUM_DISTANCE] [float] NULL,
[RATIO_DISTANCE] [float] NULL,
[Stop_Time] [datetime] NULL
) ON [PRIMARY]
April 1, 2008 at 11:02 pm
First execute the select subquery and confirm whether you are getting desired resultset. Error describes itself that your subquery is returning more that one records.
Let us know,
Mahesh
MH-09-AM-8694
April 3, 2008 at 6:48 am
I don't immediately see the problem - the MAX and MIN functions return a single value, right? but I would highlight and run each in turn and see if a single value results
then highlight and run the inner SELECT and see if a single time results
now if there are 2 or more records with the time that matches, maybe that's your problem...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply