August 20, 2009 at 7:23 pm
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
August 20, 2009 at 7:32 pm
Hey Trudye 🙂
Could you please supply us with a little sample data to work with? It really isn't clear from the code you've posted, and would be a waste of your time and mine for me to make a guess, offer a solution, and have you reply that I misunderstood. There is an excellent article about how to properly set up questions here[/url].
Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 7:58 pm
Good Point about the waste of time, here are the necessary fields
Run_Log
[Rec_Type] [nvarchar](2)
[Hdr_Seq] [numeric](9, 0)
[Vendor_Name] [varchar](20)
[Run_Date] [nvarchar](8)
Hdr
[RunDate] [varchar](8)
[Vendor_Name] [varchar](20)
[Record_Type] [varchar](2)
[File_Sequence_No] [varchar](6)
August 20, 2009 at 8:04 pm
Schema are good to have as a start. Now how about some values in those fields, like this?
declare @a table (id int, name varchar(5))
insert into @a
select 1, 'Peter' union all
select 2, 'Paul' union all
select 3, 'Mary'
Believe me, taking the time to set it up this way gets people to tackle your problem much quicker and will probably get you code examples that have been tested.
Thanks again. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 8:41 pm
It would also be of great benefit if you'd actually provide the DDL for the tables, the actual create table statements. That way we only need to cut/paste/run in SSMS instead of having to write the create table statements ourselves. The more work you do up front, the more likely people are to help out and return working, tested code in return.
August 20, 2009 at 10:08 pm
Listen to the little green master. Wise he is in the ways of the Forum...
I love your new avatar too, Lynn. It radiates awesomeness.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 11:22 pm
Bob Hovious (8/20/2009)
Listen to the little green master. Wise he is in the ways of the Forum...I love your new avatar too, Lynn. It radiates awesomeness.
Thank you. I pulled a Barry for a day and everyone (even a couple PM's) complained about Yoda going away. Better picture I found, yes.
August 21, 2009 at 5:24 am
Thanx Guys, if you need anything else pls let me know.
CREATE TABLE Hdr](
[RunDate] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_E_Hdr_RunDate] DEFAULT (CONVERT([varchar](8),getdate(),(112))),
[Vendor_Name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Record_Type] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Enroll_Hdr_Record_Type] DEFAULT ((0)),
[Vendor_ID] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Enroll_Hdr_Vendor_ID] DEFAULT ('AP'),
[File_Originator] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Enroll_Hdr_File_Originator] DEFAULT ('N'),
[Division_Code] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Enroll_Hdr_Division_Code] DEFAULT ('S'),
[Filler1] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[File_Sequence_No] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Create_Date] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Create_Time] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Filler2] [varchar](575) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Vendor_File_Name] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
CREATE TABLE Run_Log](
[Rec_Type] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Hdr_Seq] [numeric](9, 0) NULL,
[Hdr_Date] [numeric](6, 0) NULL,
[Hdr_Time] [numeric](6, 0) NULL,
[Trlr_Count] [numeric](6, 0) NULL,
[FIle_Orgin] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Div_Code] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Vendor_Name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Run_Date] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
August 21, 2009 at 6:24 am
Hi again Trudye. What we have here is failure to communicate. The abbreviated schema you sent earlier were fine. What we need to see is some sample DATA for both tables.
We need to understand the number pattern of gaps which you tried to describe in your original post. We also need to understand what you expect both tables to look like after a correct solution is run. That way everyone interested can see that the code submitted as a solution is proven to work. A verbal description, by itself, is nowhere near as helpful to us in completely understanding the problem.
Think about this: What if all our answers were verbal. ( "I'd start with a cte with the ROW_NUMBER() function and then group by the significant columns and maybe do a cross apply of a query against a third table to do the price extensions.") Such an answer might be a totally correct statement, but will never be as helpful to you as code that you can run against sample data.
If you look back to the example I sent you, please notice that there is an INSERT INTO followed by a number of SELECT statements. That is code that anyone can cut and paste and immediately have the data they need to test against. This is data which you create, so hopefully it contains all the quirks that are giving you problems.
Illustrating (not describing) your expected output lets us know what the solutions are to various scenarios. It's just use cases, which only you can come up with. (Single row gaps, multiple row gaps, gaps at the beginning, gaps at the end, etc.) It will only take you 5 minutes to do this, so please take one more look at this example and send us some data we can sink our teeth into.
Thanks for your patience. I promise, we really do want to help you solve your problem. 😉
-- I have the following table
declare @sample table (ID int, department int, rating char(1))-- sample schema, all significant columns
insert into @sample
select 1,1,'B' union all-- sample data
select 2,2,'C' union all
select 3,1,'A' union all
select 4,3,'B' union all
select 5,1,'A'
-- how do I query it to look like this?(sample output)
-- dIDrating
-- 1 5 A
-- 1 3 A
-- 1 1 B
-- 2 2 C
-- 3 4 B
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 21, 2009 at 6:52 am
I do want to thank you for posting the DDL for your tables as well, but as Bob suggested, an abbreviated table schema (enough to work on your problem) is all that is needed. Providing it as a CREATE TABLE makes it easier for us.
With the exception of Steve Jones - Editor, we are all just volunteers providing assistance to the SQL Server Community here on SSC on our own time (and sometimes during breaks at work). The more work you do up fornt for us makes it easier for us to help you. There are very talented and knowledgeable people on this site that will skip over posts that ask for help with problems but to assist them would take a large effort to put together tables and test data in the hopes that they are doing it correctly.
As the OP, Original Poster, you know your system, the data, and the problem. You need to communicate as much of this as possible to us for us to help. If you take the time to read and follow the instructions detailed in the first article I have referenced in my signature block (and you will find many others on this site have the same article linked in theirs as well) when posting questions on SSC (or any other SQL Server site, such as SQLTeam or the MSDN forums), you will get much better and quicker assistance. Also, in return, the code you will be provided will be tested.
So, with that, the only things we are really missing now is sample data and your expected results.
August 21, 2009 at 7:53 am
insert into TRLR (rundate, vendor_name, record_type, file_seq_no, vendor_file_name)
select '20090822','XXX','00','062', 'Todays_First_File.txt' union all
select '20090822','XXX','00','063', 'Todays_second_File.txt' union all
select '20090822','XXX','00','064', 'Todays_Third_File.txt' union all
select '20090822','XXX','00','065', 'Todays_Fourth_File.txt' union all
--If the input is expected to look like the above input.
--The file_seq_no's s/b contiguous as they are above.
--I have managed to write the coe to ck the first file but cannot figure out
--how to verigy the others.
--If they are not contiguous I have t0 create
--a record in the Run_log and the entry should look like this
--(assuming Todays_second_File.txt was not recieved).
insert into Run_Log (rec_type, hdr_seq, vendor_name, Run_Date)
select '00','063','XXX','20090822' union all
--I've never done this before I hope this is what you need. Thanx for all your help
August 21, 2009 at 2:32 pm
Trudye, we're getting closer, but walk with me just a little further, please. Your original code referenced HDR, DTL and Run_Log tables. You showed data for TRLR and Run_Log. I am assuming that what you called TRLR is actually HDR, but I have no source data for what the data in DTL should look like. Neither do I see any gaps in file sequence numbers that would tell me that a sequence number is invalid. There is only one header row, and I am assuming it is valid.
I am assuming (again), that if the value in hdr_seq was 62 or 64 instead of 63 we would have an invalid situation and would want to list all detail rows for that date and vendor name. But this is an assumption you need to either confirm or correct. Without you giving us data for the DTL table, and a picture of what you expect to see
Below you will find a sample setup that I have manufactured from your posts, had to change a couple of field names and comparison values to get it to run. But now it is executable code and data that can be cut and pasted into a volunteers' SQL session to work with. By doing this, we can now visualize your problem better
Please copy and paste it into your SSMS to test it. Don't worry, it uses temporary tables.
Now, here is the last piece we need. Give us a few more days' worth of data. Three or four rows a day is more than adequate, but make sure there are some instances where the Hdr_Seq (in the run_log) is valid and some where the Hdr_Seq is invalid. (I haven't done this because I'm still not sure exactly what you would call valid or invalid.) Please include accompanying data for the DTL table...and post it back here. Be sure to include one or more sequence numbers that are invalid and marked for death.
DECLARE @MinVendName nvarchar (255)
if object_id(N'tempdb..#HDR') is not null drop table #HDR
if object_id(N'tempdb..#RUN_LOG') is not null drop table #RUNLOG
;with cte1 (rundate, vendor_name, record_type, file_seq_no, vendor_file_name) as
(select '20090822','XXX','00','062', 'Todays_First_File.txt' union all
select '20090822','XXX','00','063', 'Todays_second_File.txt' union all
select '20090822','XXX','00','064', 'Todays_Third_File.txt' union all
select '20090822','XXX','00','065', 'Todays_Fourth_File.txt' )
select *
into #HDR
from cte1
;with cte2 (rec_type, hdr_seq, vendor_name, Run_Date) as
(select '00','063','XXX','20090822')
select *
into #RUN_LOG
from cte2
select * from #hdr
select * from #run_log
SELECT @MinVendName = (select Vendor_File_Name from #HDR
where rundate = '20090822'
and Vendor_Name = 'XXX'
and File_Seq_No IN (
SELECT min(File_Seq_No) from #HDR
where rundate = '20090822'
and Vendor_Name = 'XXX')
)
select @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 = 'XXX')
SET @MinSeq = (
SELECT min(File_Seq_No) FROM #Hdr
WHERE rundate = '20090822'
and Vendor_Name = 'XXX'
)
select @Last_Seq as [@Last_Seq], @MinSeq as [@MinSeq]
-- If (@LasT_Seq + 1) @MinSeq
--BEGIN
--select * from Dtl D
--WHERE rundate = '20090822'
-- and Vendor_Name = 'XXX'
-- AND D.Vendor_File_Name = @MinVendName
--END
drop table #HDR
drop table #RUN_LOG
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 21, 2009 at 4:18 pm
Bob did a good job describing what we need above. Please follow his lead and build us the better mouse trip to show us your problem and what you are trying to accomplish.
Some of us are visual types, we need to see what you are trying to accomplish rather than just telling or describing what you want. Doing that is okay also, as it might help fill in gaps in understanding that a visual might miss as well. I happen to be a visually oriented problem solver. Descriptions can help but actually seeing the source and the target makes things much easier for me.
August 22, 2009 at 11:59 am
Thanx to all of you for all your help, patience and guidance. I have managed to get the query completed.
Thanx again,
Trudye
August 22, 2009 at 9:17 pm
trudye10 (8/22/2009)
Thanx to all of you for all your help, patience and guidance. I have managed to get the query completed.Thanx again,
Trudye
Trudye,
Glad to hear that you managed to complete the query. Now, what you should do, is post your solution. Should someone else have a similar problem, they will have something to look at while trying to solve their problem. Just good forum etiquette.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply