Validating Seq#'s - 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 Seq#'s

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2009-08-22 : 13:47:57

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:

Thank You,

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-23 : 02:19:35

I don't quite understand what you are doing but another approach to seeing if you have the right sequence might be to select your data with row_number() ordered and partitioned by the rules that determine what the sequence might be (date & file sequence?) and another row_number() ordered/partitioned by the actual sequence you read from the file.
Where the two don't match you have a problem.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-23 : 03:04:05

Don't quite understand, what record you want to remove.

Without any table structure, sample data, can't help you much.

Since you are using SQL 2005, you can make use of row_number() to simplify the query a bit. Here is the 1st section of your query using row_number()


SELECT	@MinVendName	= v.Vendor_File_Name,
	@MinSeq		= v.File_Sequence_No
FROM	(
		SELECT	Vendor_File_Name, File_Sequence_No,
			row_no	= row_number() over (order by File_Sequence_No)
		FROM	Hdr
		WHERE	Vendor_Name	= 'CFS'
		AND	rundate	= dateadd(day, datediff(day, 0, getdate()), 0)
	) v
WHERE	v.row_no	= 1

SELECT	@LasT_Seq	= MAX(Hdr_Seq)
FROM	Run_Log 
WHERE 	Vendor_Name 	= 'CFS'

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources