August 21, 2013 at 12:58 pm
I copied the query result set to TEST.xls and required result set into result.xls.
I have a table now where we have addressline1, city,state, zip, county ,file_no and envelope_date. But for some of the records we have more than one address for particular file_no for different timestamp, you can see in the test.xls for example.
what I have to do is, we have a huge table where they have all the information, so I m pulling all the above mentioned fields from this huge table and loading into a process table, then planning to load them in to new dimension table and then get the dimension key and load into FACT Table, but I got into this issue where I m seeing more than one address for one file_no, so what I m planning to do is
add a end_ts column to my process table, and then select address between start and enddates. Start date is same as envelope_date and end_ts is nothing but next row in the envelope_date column.
Now I m not quite sure how can I pull the next row from same column and update end_ts.
Sorry if this explanation is confusing
Thanks in advance
August 21, 2013 at 1:21 pm
Instead of posting excel documents that require us to import data and guess on datatypes can you instead post ddl (create table statements) and sample data (insert statements)? That way we know exactly what the tables look like.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2013 at 1:39 pm
CREATE TABLE "AAA"."ADD_PROCESS" (
"FILE_NO" CHAR(11) NOT NULL,
"ENVELOPE_DATE" TIMESTAMP NOT NULL,
"REF_ID" CHAR(20) NOT NULL,
"ADDR_LINE_1" VARCHAR(60) NULL ,
"ADDR_CITY" VARCHAR(60) NULL ,
"ADDR_STATE" VARCHAR(10) NULL ,
"ADDR_ZIP" VARCHAR(60) NULL ,
"ADDR_COUNTY" VARCHAR(60) NULL,
"START_TS" TIMESTAMP NULL,
"END_TS" TIMESTAMP NULL )
FILE_NO ENVELOPE_DATE REF_ID ADDR_LINE_1 ADDR_CITY ADDR_STATE ADDR_ZIP ADDR_COUNTY START_TS END_TS
60001578296 2013-07-22-10.13.30.697000 DW2013072289572359 21 DELOREY AVE NORTH WEYMOUTH MA 02191 NORFOLK 2013-07-22-10.13.30.697000 <NULL>
60001578296 2013-07-22-10.24.13.679000 DW2013072289573236 32 Rosemont Ave 2013-07-22-10.24.13.679000 <NULL>
60001578296 2013-07-22-10.24.31.265000 DW2013072289573244 32 Rosemont Rd 2013-07-22-10.24.31.265000 <NULL>
60001578296 2013-07-22-10.24.51.168000 DW2013072289573247 North Weymouth 2013-07-22-10.24.51.168000 <NULL>
August 21, 2013 at 1:44 pm
ry.sqldev (8/21/2013)
CREATE TABLE "AAA"."ADD_PROCESS" ("FILE_NO" CHAR(11) NOT NULL,
"ENVELOPE_DATE" TIMESTAMP NOT NULL,
"REF_ID" CHAR(20) NOT NULL,
"ADDR_LINE_1" VARCHAR(60) NULL ,
"ADDR_CITY" VARCHAR(60) NULL ,
"ADDR_STATE" VARCHAR(10) NULL ,
"ADDR_ZIP" VARCHAR(60) NULL ,
"ADDR_COUNTY" VARCHAR(60) NULL,
"START_TS" TIMESTAMP NULL,
"END_TS" TIMESTAMP NULL )
FILE_NO ENVELOPE_DATE REF_ID ADDR_LINE_1 ADDR_CITY ADDR_STATE ADDR_ZIP ADDR_COUNTY START_TS END_TS
60001578296 2013-07-22-10.13.30.697000 DW2013072289572359 21 DELOREY AVE NORTH WEYMOUTH MA 02191 NORFOLK 2013-07-22-10.13.30.697000 <NULL>
60001578296 2013-07-22-10.24.13.679000 DW2013072289573236 32 Rosemont Ave 2013-07-22-10.24.13.679000 <NULL>
60001578296 2013-07-22-10.24.31.265000 DW2013072289573244 32 Rosemont Rd 2013-07-22-10.24.31.265000 <NULL>
60001578296 2013-07-22-10.24.51.168000 DW2013072289573247 North Weymouth 2013-07-22-10.24.51.168000 <NULL>
Thanks for the ddl. However the ddl you put together seems a bit off. You used the timestamp datatype. I assume that you mean to use datetime?
http://technet.microsoft.com/en-us/library/ms182776.aspx
Can't really do anything with the sample data. Please post this as inserts.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2013 at 1:57 pm
Thanks for the response, database here is DB2, so tables were also created in DB2 which is little different from SQL.
August 21, 2013 at 2:22 pm
please find the inserts
INSERT INTO "RAGHA"."INS1_ADD_PROCESS" (
'60000578296',
'2013-07-22-10.13.30.697000',
'DW2013072289572359',
'DELOREY AVE',
'NORTH WEYMOUTH',
'MA',
'02191',
'NORFOLK',
'2013-07-22-10.13.30.697000',
NULL)
UNION ALL
INSERT INTO "RAGHA"."INS1_ADD_PROCESS" (
'60000578296',
'2013-07-22-10.24.13.679000',
'DW2013072289573236',
'Rosemont Ave',
' ',
' ',
' ',
' ',
'2013-07-22-10.24.13.679000',
NULL)
UNION ALL
INSERT INTO "RAGHA"."INS1_ADD_PROCESS" (
'60000578296',
'2013-07-22-10.24.31.265000',
'DW2013072289573244',
'Rosemont Rd',
' ',
' ',
' ',
' ',
'2013-07-22-10.24.31.265000',
NULL)
UNION ALL
INSERT INTO "RAGHA"."INS1_ADD_PROCESS" (
'60000578296',
'2013-07-22-10.24.51.168000',
'DW2013072289573247',
' ',
'North Weymouth',
' ',
' ',
' ',
'2013-07-22-10.24.51.168000',
NULL)
August 21, 2013 at 2:27 pm
ry.sqldev (8/21/2013)
Thanks for the response, database here is DB2, so tables were also created in DB2 which is little different from SQL.
Being that this a sql server site I don't even want to try anything here. Any code I would put together would be for sql server. I doubt that it would work in DB2. I am guessing that the way to do this in sql server is most likely going to utilize ROW_NUMBER. I have no clue if DB2 has anything like that or not.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2013 at 2:37 pm
Here is how I would do this in sql server. I have no idea if you can anything like this in DB2 or not.
;with myCTE as
(
select * , ROW_NUMBER() over(order by STArt_ts) as RowNum
from ADD_PROCESS
)
select c1.*, c2.start_ts
from myCTE c1
left join myCTE c2 on c2.RowNum - 1 = c1.RowNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2013 at 2:43 pm
Thank You, will try to see if i can do anything similar in DB2
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply