Geek Answers Handbook

Using model suggestions to extend dates

I have several different types of data related to a date range that I want to combine together, but at the same time broken day. Thus, the three-day piece of data will result in three lines:

start primary_key start+1 primary_key start+2 primary_key

I played using the model suggestion in a select statement in 10g and was looking for the best way to achieve this. I am currently joining a date range that covers the entire range of possible dates (select min (start date), max (end date)). I would prefer to select data and add more rows to them to convert them to a dataset for the day.

edit:

I managed to come up with (now there are sample data):

  SELECT * FROM ( SELECT 123 req_code, 345 req_par_code, TO_DATE('01-03-2010', 'dd-mm-yyyy') req_start_date, TO_DATE('05-03-2010', 'dd-mm-yyyy') req_end_date FROM dual ) MODEL PARTITION BY (req_code) DIMENSION BY (0 d) MEASURES (SYSDATE dt, req_par_code, req_start_date, req_end_date) RULES ITERATE(365) UNTIL (dt[iteration_number] >= TRUNC(req_end_date[0])) ( dt[iteration_number] = NVL(dt[iteration_number-1] + 1, TRUNC(req_start_date[0])), --Copy data across req_par_code[ iteration_number ] = req_par_code[0], req_start_date[ iteration_number ] = req_start_date[0], req_end_date[ iteration_number ] = req_end_date[0] ) ORDER BY dt, req_code;
+4
source share
3 answers

you can use the MODEL clause to generate strings, here is a small example:

 SQL> SELECT * FROM t_data; PK START_DATE END_DATE ---------- ----------- ----------- 1 20/01/2010 20/01/2010 2 21/01/2010 23/01/2010 3 24/01/2010 27/01/2010 SQL> SELECT pk, start_date, end_date FROM t_data 2 MODEL 3 PARTITION BY (pk) 4 DIMENSION BY (0 AS i) 5 MEASURES(start_date, end_date) 6 RULES 7 ( start_date[FOR i 8 FROM 1 TO end_date[0]-start_date[0] 9 INCREMENT 1] = start_date[0] + cv(i), 10 end_date[ANY] = start_date[CV()] + 1 11 ) 12 ORDER BY 1,2; PK START_DATE END_DATE ---------- ----------- ----------- 1 20/01/2010 21/01/2010 2 21/01/2010 22/01/2010 2 22/01/2010 23/01/2010 2 23/01/2010 24/01/2010 3 24/01/2010 25/01/2010 3 25/01/2010 26/01/2010 3 26/01/2010 27/01/2010 3 27/01/2010 28/01/2010
+2
source 
 SELECT TO_DATE('01.01.2009', 'dd.mm.yyyy') + level - 1 FROM dual CONNECT BY TO_DATE('01.01.2009', 'dd.mm.yyyy') + level <= TRUNC(SYSDATE, 'DDD') + 1

will provide you with a list of all dates from Jan 1st, 2009 to today.

0
source

Despite the love of proposing an SQL model, in this case it is only the second or third. I compared six ways to make this type of request here on my blog: http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html

Hope this helps.

Regards, Rob.

0
source

More articles:


All Articles