Validating Contiguous Recs - 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 2005 Forums

Transact-SQL (2005)

Validating Contiguous Recs

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2009-08-20 : 21:24:34

Hi Guys, I'm reading several hdrs into a table, they all have seq#'s. The min seq# must be one gt the prev days seq# and
the other seq#'s must be contiguous. I have managed (in my own crude way) to establish if the next seq# of the incoming file is correct.
How do I validate the seq# of the other hdrs? Here is the code I have so far:

DECLARE @MinVendName nvarchar (255)
SET @MinVendName = (select Vendor_File_Name from Hdr
where rundate = Convert(Varchar(10),Getdate(),112)
and Vendor_Name = 'CFS' and
File_Sequence_No IN
(SELECT min(File_Sequence_No) from Hdr
where rundate = Convert(Varchar(10),Getdate(),112)
and Vendor_Name = 'CFS'))
Print @MinVendName

--Remove recs if hdr not good
DECLARE @LasT_Seq as int
DECLARE @MinSeq as int
SET @LasT_Seq = (SELECT max(Hdr_Seq) from Run_Log WHERE Vendor_Name = 'CFS')
SET @MinSeq = (SELECT min(File_Sequence_No) FROM Hdr
WHERE rundate = Convert(Varchar(10),Getdate(),112)and Vendor_Name = 'CFS')
If (@LasT_Seq + 1) <> @MinSeq
BEGIN
select * from Dtl D
WHERE rundate = Convert(Varchar(10),Getdate(),112)and Vendor_Name = 'CFS'
AND D.Vendor_File_Name = @MinVendName
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources