February 14, 2012 at 8:28 am
I have a file the contains 3 types of records
I need to store a variable from record type 1, store a different variable from record type 2
the update record type 3 with the above variables.
The file conains
Seq, RT, type, code, descript (with code and Description as null to start with
001,1,1234 Need to store 1234 as code
002,2,Misc Info, Need to store Type as Descript
003,3,write code (1234) and descript(Misc Info)
004,3,write code (1234) and descript(Misc Info)
005,2,NMore Info
006,3,write code (1234) and descript(More Info)
007,1,8979 Need to store 8979 as code
006,3,write code (8979) and descript(More Info)
I cannot figure this one out..need help
February 14, 2012 at 8:49 am
Your post does not give us enough information to understand your issue completely. Can you please provide DDL for the table which you are affecting and sample data. Then an explanation of the file and what you re doing with it.
Jared
CE - Microsoft
February 14, 2012 at 7:01 pm
This is an explanation of what Jared is talking about: How best to post your question[/url]
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
February 15, 2012 at 11:04 am
********************* DDL
Tifvarcharno255 yesnono.
Typevarcharno255 yesnono.
Codevarcharno10 yesnono.
RCintno410 0 yes(n/a)(n/a)NULL
SEQintno410 0 no(n/a)(n/a)NULL
Descriptvarcharno30 yesnono
********************* Sample File
Tif Type Code RC SEQ DESCRIPT
00002667.tif ,102205 , NULL ,1 ,1NULL
00002668.tif ,CREDITAPPS , NULL ,2 ,2NULL
00002669.tif ,NULL , NULL ,3 ,3NULL
00002670.tif ,NULL , NULL ,34NULL
00002671.tif ,NULL , NULL ,35NULL
00002672.tif ,NULL , NULL ,36NULL
00002673.tif ,NULL , NULL ,37NULL
00002674.tif ,TRADEREFS , NULL , 28NULL
00002675.tif ,NULL , NULL ,39NULL
00002676.tif ,NULL , NULL ,310NULL
00002677.tif ,NULL , NULL ,311NULL
00002678.tif ,TAXEXEMPT , NULL ,212NULL
00002679.tif ,NULL , NULL ,313NULL
00002680.tif ,WELCOME , NULL ,214NULL
00002681.tif ,NULL , NULL ,315NULL
00002682.tif ,101278 , NULL ,116NULL
00002683.tif ,CREDITAPPS , NULL ,217NULL
00002684.tif ,NULL , NULL ,318NULL
00002685.tif ,NULL , NULL ,319NULL
IF RC = 3 update the record
Code = Type from the last RC = 1 record
DESCRIPT = Type from the last RC = 2 record
Thanks for your help
February 15, 2012 at 11:12 am
Your DDL should look like this:
CREATE TABLE tableName (Tif varchar (255)
Type varchar(255),
Code varchar(10),
RC int,
SEQ int,
Descript varchar(30))
Is this correct to make the table you are talking about? I do not know what all of the "no" and "yes" meant.
To format your data for us it should be like this:
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
SELECT '00002667.tif', '102205', NULL, 1, 1, NULL for each line of sample data. Does this make sense to you? It allows us to replicate your table and sample data.
Jared
CE - Microsoft
February 15, 2012 at 12:18 pm
CREATE TABLE tableName
(Tif varchar (255),
Type varchar(255),
Code varchar(10),
RC int,
SEQ int,
Descript varchar(30))
go
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002667.TIF',102205,NULL,1,1,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002668.TIF','CREDITAPPS',NULL,2,2,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002669.TIF',NULL,NULL,3,3,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002670.TIF',NULL,NULL,3,4,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002671.TIF',NULL,NULL,3,5,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002672.TIF',NULL,NULL,3,6,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002673.TIF',NULL,NULL,3,7,NULL)
INSERT INTO tableName (Tif, Type, Code,RC, SEQ, Descript)
Values ('00002674.TIF','TRADEREFS',NULL,2,8,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002675.TIF',NULL,NULL,3,9,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002676.TIF',NULL,NULL,3,10,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002677.TIF',NULL,NULL,3,11,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002678.TIF','TAXEXEMPT',NULL,2,12,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002679.TIF',NULL,NULL,3,13,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002680.TIF','WELCOME',NULL,2,14,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002681.TIF',NULL,NULL,3,15,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002682.TIF',101278,NULL,1,16,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002683.TIF','CREDITAPPS',NULL,2,17,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002684.TIF',NULL,NULL,3,18,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002685.TIF',NULL,NULL,3,19,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002686.TIF','TRADEREFS',NULL,3,20,NULL)
February 15, 2012 at 12:46 pm
Ok, great. So from this sample data, what is supposed to happen?
something like this (Not real SQL, just a kind of pseudocode)?
UPDATE tableName SET a.Code = b.Type WHERE b.lastRC = 1 and a.RC = 3
UPDATE tableName SET a.descript = c.type WHERE c.lastRC = 2 and a.RC = 3
So, if that is correct how are these grouped? i.e. What does "from the last RC=1 record" mean in your previous explanation. Is it based on sequence?
Jared
CE - Microsoft
February 15, 2012 at 1:13 pm
The records are not one for one.
I have added the sequence number and it must be read in seq.
Record code 1 Seq 001 need to set the code = type Where type = 123456
Record code 2 Seq 002 need to set the descript = type Where type = CreditApp
Record code 3 Seq 003 need to update the code as 123456 and the descript as CreditApp
Record code 3 Seq 004 need to update the code as 123456 and the descript as CreditApp
Record code 3 Seq 005 need to update the code as 123456 and the descript as CreditApp
Record code 2 Seq 006 need to set the descript = type Where type = TradeRefs
Record code 3 Seq 007 need to set the code as 123456 and the descript as TradeRef
Record code 1 Seq 008 need to set the code = type Where type = 654321
Record code 3 Seq 009 need to set the code as 654312 and the descript as TradeRef
Record 3 gets updated where the code is from the last record 1 and the descript gets updated with the last RC 2
February 15, 2012 at 1:23 pm
Ok, I get it now. Can I ask why you are doing this in SQL? Is this a 1 time update? Are you getting data from a file, importing it, and then adding this for analysis? If I can understand the business case, I can maybe help provide a better solution.
Jared
CE - Microsoft
February 15, 2012 at 1:28 pm
This is being exported into a file, it is kinda a one time thing
but with many records, I have tried in Crystal, SQL if you have any other
ideas, I am open
February 15, 2012 at 1:30 pm
Ok, I guess I was thinking whatever was writing this data should be inserting it with the correct values to begin with. Since it is a one time thing... et me work on something. How many rows do you have and have you build any indexes on the data?
Jared
CE - Microsoft
February 15, 2012 at 1:41 pm
There are a lot of rows, I am building this for a friend he supplied me with the sample data.
No I have not built any indexes.
February 15, 2012 at 2:04 pm
Ok, here's what I got so far:
USE tempdb
CREATE TABLE tableName
(Tif varchar (255),
Type varchar(255),
Code varchar(10),
RC int,
SEQ int,
Descript varchar(30))
go
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002667.TIF',102205,NULL,1,1,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002668.TIF','CREDITAPPS',NULL,2,2,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002669.TIF',NULL,NULL,3,3,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002670.TIF',NULL,NULL,3,4,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002671.TIF',NULL,NULL,3,5,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002672.TIF',NULL,NULL,3,6,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002673.TIF',NULL,NULL,3,7,NULL)
INSERT INTO tableName (Tif, Type, Code,RC, SEQ, Descript)
Values ('00002674.TIF','TRADEREFS',NULL,2,8,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002675.TIF',NULL,NULL,3,9,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002676.TIF',NULL,NULL,3,10,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002677.TIF',NULL,NULL,3,11,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002678.TIF','TAXEXEMPT',NULL,2,12,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002679.TIF',NULL,NULL,3,13,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002680.TIF','WELCOME',NULL,2,14,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002681.TIF',NULL,NULL,3,15,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002682.TIF',101278,NULL,1,16,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002683.TIF','CREDITAPPS',NULL,2,17,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002684.TIF',NULL,NULL,3,18,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002685.TIF',NULL,NULL,3,19,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002686.TIF','TRADEREFS',NULL,3,20,NULL)
GO
SELECT *
INTO #tempRC1
FROM tableName
WHERE RC = 1
SELECT *
INTO #tempRC2
FROm tableName
WHERE RC = 2
--UPDATE a
--SET code = b.type, descript = c.type
SELECT *
FROM tableName a
INNER JOIN #tempRC1 b
ON a.SEQ > b.SEQ
AND a.RC = 3
INNER JOIN #tempRC2 c
ON a.SEQ > c.SEQ
AND a.RC = 3
WHERE a.RC <= 6
--SELECT * FROM tableName
DROP TABLE tableName
DROP TABLE #tempRC1
DROP TABLE #tempRC2
I know this is not the best way to do this, but it is helping me visualize it in my head. Hopefully someone else will jump in here and help.
Jared
CE - Microsoft
February 15, 2012 at 3:02 pm
Ok, try this! 🙂
USE tempdb
CREATE TABLE tableName
(Tif varchar (255),
Type varchar(255),
Code varchar(10),
RC int,
SEQ int,
Descript varchar(30))
go
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002667.TIF',102205,NULL,1,1,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002668.TIF','CREDITAPPS',NULL,2,2,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002669.TIF',NULL,NULL,3,3,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002670.TIF',NULL,NULL,3,4,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002671.TIF',NULL,NULL,3,5,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002672.TIF',NULL,NULL,3,6,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002673.TIF',NULL,NULL,3,7,NULL)
INSERT INTO tableName (Tif, Type, Code,RC, SEQ, Descript)
Values ('00002674.TIF','TRADEREFS',NULL,2,8,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002675.TIF',NULL,NULL,3,9,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002676.TIF',NULL,NULL,3,10,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002677.TIF',NULL,NULL,3,11,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002678.TIF','TAXEXEMPT',NULL,2,12,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002679.TIF',NULL,NULL,3,13,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002680.TIF','WELCOME',NULL,2,14,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002681.TIF',NULL,NULL,3,15,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002682.TIF',101278,NULL,1,16,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002683.TIF','CREDITAPPS',NULL,2,17,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002684.TIF',NULL,NULL,3,18,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002685.TIF',NULL,NULL,3,19,NULL)
INSERT INTO tableName (Tif, Type, Code, RC, SEQ, Descript)
Values ('00002686.TIF','TRADEREFS',NULL,2,20,NULL)
GO
SELECT *
INTO #tempRC1
FROM tableName
WHERE RC = 1
SELECT *
INTO #tempRC2
FROm tableName
WHERE RC = 2
UPDATE a
SET code = d.type, descript = e.type
--SELECT a.Tif, a.Type, a.Code, a.RC, a.SEQ, b.max1, c.max2, d.Type, e.Type
FROM tableName a
CROSS APPLY (SELECT MAX(seq) max1
FROM #tempRC1 b
WHERE b.SEQ < a.SEQ) b
CROSS APPLY (SELECT MAX(seq) max2
FROM #tempRC2 b
WHERE b.SEQ < a.SEQ) c
INNER JOIN tableName d
ON b.max1 = d.SEQ
INNER JOIN tableName e
ON c.max2 = e.SEQ
WHERE a.RC = 3
SELECT * FROM tableName
--DROP TABLE tableName
--DROP TABLE #tempRC1
--DROP TABLE #tempRC2
Not sure if it is the "best" solution. However, it will get the job done!
Jared
CE - Microsoft
February 16, 2012 at 8:04 am
Did this work for you? Also, does anyone else have a better way to do this?
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply