October 28, 2013 at 12:21 pm
Type entered Dt Amt Max Entered Overall Max EnteredDt Overall
E 1/1/2011 500 500 1/1/2011
E 2/3/2012 600 600 2/3/2012
E 8/1/2012 400 600 2/3/2012
E 9/1/2013 1000 1000 9/1/2013
E 9/15/2013 650 1000 9/1/2013
E 10/1/3013 1000 1000 9/1/2013
Hello
i have first three columns in my fact table and i want to achieve last two columns, i will try to explain it here, first value for Max Entered Overall (min entered dt Amt)is as it is populated in Max Entered Overall column, then when Amt in next row is greatethan the present row then i should get next amount in Max Entered Overall , when the next row Amt is less than present row then amount in Max Entered Overall should be carry forwarded.same with the dates too.
i m trying to do this in DB2 sql, i cant use cursor for this here, it would be great if this is achieved by using self join and sub queries.
Thanks in advance and i really appreciative any help.
October 28, 2013 at 12:48 pm
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
October 28, 2013 at 1:34 pm
sorry for not providing this earlier
create table "xx"."table"(
"type_code" varchar(2) NOT NULL,
"PROCESS_TS" TIMESTAMP NOT NULL ,
"AMT" DECIMAL(11,2) NOT NULL ,
"MAX_ENTERED_AMT" DECIMAL(11,2) NULL,
"MAX_ENTERED_TS" TIMESTAMP NULL )
INSERT INTO "xx"."table"(
type_code,
PROCESS_TS,
AMT,
MAX_ENTERED_AMT,
MAX_ENTERED_TS)
values('E','1988-03-01-15.06.01.000000','5.00',NULL,NULL),
('E', '1990-12-11-13.22.01.000000','5000.00',NULL,NULL),
('E', '1991-10-21-16.04.01.000000', '1000.00',NULL,NULL),
('E', '1991-10-25-13.18.01.000000', '3000.00', NULL,NULL),
('E','1992-12-15-11.40.01.000000','2500000.00',NULL, NULL)
Result
type_code PROCESS_TS Amt MAX_ENTERED_AMT MAX_ENTERED_TS
E 1988-03-01-15.06.01.000000 5.00 5.00 1988-03-01-15.06.01.000000
E 1990-12-11-13.22.01.000000 5000.00 5000.00 1990-12-11-13.22.01.000000
E 1991-10-21-16.04.01.000000 1000.00 5000.00 1990-12-11-13.22.01.000000
E 1991-10-25-13.18.01.000000 3000.00 5000.00 1990-12-11-13.22.01.000000
E 1992-12-15-11.40.01.000000 2500000.00 2500000.00 1992-12-15-11.40.01.000000
please let me know any other information is needed.
Thank You
October 28, 2013 at 2:12 pm
So this is DB2? I have no idea how this would work. I worked with DB2 once about 15 years ago. I think that timestamp is the same as datetime. The other datatypes are easy enough. The problem is that I have idea what functions and such are available.
In sql server I would probably use ROW_NUMBER() to help out here. I don't know if you have that in DB2. I think you can use PROCESS_TS as your ordering column?
_______________________________________________________________
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/
October 28, 2013 at 2:31 pm
yes that is correct DB2 has lot of limitations and i don't know how can i achieve this ,
i m copying what i tried,i tried to load this into a process table, i thought by creating end_ts it would some how help me to update in date ranges. i m not sure how exactly i can compare this amounts and get the max amount for particular time stamp.
SELECT file_key,
claim_no_key,
PROCESS_ts,
amt,
MIN(end_ts)
FROM (
Select DISTINCT
a.file_key,
a.claim_no_key,
a.process_ts,
a.amt,
case when b.process_ts >a.process_ts THEN b.process_ts
else null
END as end_ts
from xx.MAX_RESERVE a
Inner join xx.MAX_RESERVE b
on a.file_key=b.file_key
and a.claim_no_key = b.claim_no_key
order by file_key, claim_no_key,process_ts
) t
group by
file_key,
claim_no_key,
process_ts,
amt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply