CREATE TABLE [tmpDataAnomalies] (
	[AC] [varchar] (3) NOT NULL ,
	[AC_NUMBER] [varchar] (4) NOT NULL ,
	[DEST] [varchar] (4) NOT NULL ,
	[ORIG] [varchar] (4) NOT NULL ,
	[IGTD] [smalldatetime] NOT NULL ,
	[Update_Time] [datetime] NOT NULL ,
	[AutoID] [tinyint] NOT NULL ,
	[DataAnomaly] [varchar] (50) NULL 
)

CREATE TABLE [tmpArrivals] (
	[AC] [varchar] (3) NOT NULL ,
	[AC_NUMBER] [varchar] (4) NOT NULL ,
	[DEST] [varchar] (4) NULL ,
	[ORIG] [varchar] (4) NULL ,
	[IGTD] [smalldatetime] NOT NULL ,
	[ETD] [smalldatetime] NULL ,
	[ETA] [smalldatetime] NULL ,
	[UPDATE_TIME] [datetime] NULL ,
)

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Declare @curAC AS varchar(5)
Declare @curACNum AS varchar(5)
Declare @curDest AS varchar(5)
Declare @curOrig AS varchar(5)
Declare @curIGTD AS smalldatetime
Declare @curUpdate_Time as datetime
Declare @curETA as datetime
Declare @prevAC AS varchar(5)
Declare @prevACNum AS varchar(5)
Declare @prevDest AS varchar(5)
Declare @prevOrig AS varchar(5)
Declare @prevIGTD AS smalldatetime
Declare @prevUpdate_Time as datetime
Declare @prevETA as datetime
Declare @sql as varchar(255)

Declare Anomalies  Cursor
 Static
 Read_Only
 For
	SELECT A.AC, A.AC_NUMBER, A.DEST, A.ORIG, A.IGTD, A.UPDATE_TIME, A.ETA
	FROM TMPARRIVALS A
	ORDER BY A.AC, A.AC_NUMBER, A.ORIG, A.DEST, A.IGTD
 --Go

Open Anomalies
FETCH FIRST FROM Anomalies into @curAC, @curACNum, @curDest, @curOrig, @curIGTD, @curUpdate_Time, @curETA
FETCH FIRST FROM Anomalies into @prevAC, @prevACNum, @prevDest, @prevOrig, @prevIGTD, @prevUpdate_Time, @prevETA
While @@Fetch_Status = 0
Begin

	IF (@curAC = @prevAC and @curACNum = @prevACNum and @curDest = @prevDest and @curOrig = @prevOrig and @curIGTD = @prevIGTD)
	BEGIN  
		IF DATEDIFF(n,@curETA,@prevETA) > 1200
		BEGIN
			SELECT @SQL = 'INSERT INTO tmpDataAnomalies(AC,AC_Number,DEST,ORIG,IGTD, Update_Time, AutoId, DataAnomaly) VALUES(@curAC, @curACNum, @curDest, @curOrig, @curIGTD, @curUpdate_Time, 1, 2)'
			EXEC (@SQL)
		END

	END

set @prevAC = @curAC
set @prevACNum = @curACNum
set @prevDest = @curDest
set @prevOrig = @curOrig
set @prevIGTD = @curIGTD
set @prevUpdate_Time = @curUpdate_Time
set @prevETA = @curETA

Fetch Next FROM Anomalies into @curAC, @curACNum, @curDest, @curOrig, @curIGTD, @curUpdate_Time, @curETA

End

Close Anomalies
Deallocate Anomalies

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

EXEC ("INSERT INTO tmpDataAnomalies(AC,AC_Number,DEST,ORIG,IGTD, Update_Time, AutoId, DataAnomaly) VALUES('" + @curAC + "', '" + @curACNum + "', '" + @curDest + "', '" + @curOrig + "', '" + @curIGTD + "', '" + @curUpdate_Time + "', 1, '2') ")

select
    IDENTITY(int,1,1) as RowNum,
    A.AC,
    A.AC_NUMBER,
    A.DEST,
    A.ORIG,
    A.IGTD,
    A.UPDATE_TIME,
    A.ETA
into
    #temp
from
    TMPARRIVALS A
order by
    A.AC,
    A.AC_NUMBER,
    A.ORIG,
    A.DEST,
    A.IGTD
    
insert into tmpDataAnomalies (
    AC,
    AC_Number,
    DEST,
    ORIG,
    IGTD,
    Update_Time,
    AutoId,
    DataAnomaly )
select
    A.AC,
    A.AC_NUMBER,
    A.DEST,
    A.ORIG,
    A.IGTD,
    A.UPDATE_TIME,
    A.ETA
from
    #temp A
where
    exists (
        select 1
        from
            #temp
        where
            a.RowNum = RowNum - 1 and
            A.AC = AC and
            A.AC_NUMBER = AC_NUMBER and
            A.DEST = DEST and
            A.ORIG = ORIG and
            A.IGTD = IGTD and
            DATEDIFF(n,A.ETA,ETA) > 1200 )

quote:

---

the DML to insert some sample data

---

I thought DML was the insert statement from above. Or do you want
sample data... cause I definitely post that.

Here is an example of the temp table not sorting on the insert



Also, the query that you sent looks like it is right on the money, yet it won't get the correct record. It returns the prior record and not the current.

Here is an example of this problem



At a glance it looks correct, it is finding the difference between the current and prior, but the record that the join is returning is the prior record. Any thoughts on how to clear up this issue?

thanks!
newty25

quote:

---

newty25, the select into should honor the order by when making the RowNum column. I've done it many times. I could be wrong, but maybe you should check your rowset once more...

---