Hi Tom,
First of all, thanks for your tremendous contribution to the
Oracle Community in helping people like us solve day to day Oracle replated
problems.
I have a small problem. Here are the details:
CREATE
TABLE T
(
HS_ID NUMBER(20) PRIMARY KEY,
HS_NM VARCHAR2(30
BYTE),
HS_STRT_DT DATE,
HS_END_DT DATE,
HS_CLT_IND CHAR(1 BYTE)
);
Insert into TMS.T
(HS_ID, HS_NM, HS_STRT_DT, HS_END_DT, HS_CLT_IND)
Values
(1, 'Alaska', TO_DATE('01/01/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N');
Insert into TMS.T
(HS_ID, HS_NM, HS_STRT_DT, HS_END_DT, HS_CLT_IND)
Values
(2, 'Alabama', TO_DATE('01/01/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y');
Insert into TMS.T
(HS_ID, HS_NM, HS_STRT_DT, HS_END_DT, HS_CLT_IND)
Values
(3, 'Virginia', TO_DATE('01/01/2001 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'N');
Insert into TMS.T
(HS_ID, HS_NM, HS_STRT_DT, HS_END_DT, HS_CLT_IND)
Values
(4, 'Virginia', TO_DATE('01/01/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Y');
COMMIT;
Here is the business rule:
For each row in table t, view should traslate
it into multiple rows based on start, end date and current year
(HS_STRT_DT , HS_END_DT).
Taking example of row with id=1:
strt_dt=01/01/2004
end_dt=12/31/2012
current_year=2007
If
current_year <= end_dt then the row should get translated into 4 rows, one
for each year starting 2004 and ending at 2007.
If current_year > end_dt
then that particular row should have (year(end_dt)-strt_dt) rows,
starting at year(strt_dt) and ending at year(strt_dt)
Thanks in
advance for your time and help.
and we said...
You might have to play around
with some boundary value conditions - just to validate that the math is right -
don't know your data like you know it, but here is the idea, I used 100 as the
max year spread, you can adjust based on your needs
ops$tkyte%ORA10GR2> select * from t;
HS_ID HS_NM HS_STRT_D HS_END_DT H
---------- ------------------------------ --------- --------- -
1 Alaska 01-JAN-04 31-DEC-12 N
2 Alabama 01-JAN-03 31-DEC-12 Y
3 Virginia 01-JAN-01 31-DEC-04 Y
4 Virginia 01-JAN-05 31-DEC-12 N
WITH data
AS ( SELECT LEVEL - 1 l
FROM DUAL
CONNECT BY LEVEL <= 100)
SELECT hs_id,
hs_nm,
TO_CHAR (ADD_MONTHS (hs_strt_dt, l * 12), 'yyyy'),
hs_clt_ind
FROM t, data
WHERE l <
CEIL (MONTHS_BETWEEN (LEAST (SYSDATE, hs_end_dt), hs_strt_dt) / 12)
AND TO_CHAR (ADD_MONTHS (hs_strt_dt, l * 12), 'yyyy') <=
TO_CHAR (SYSDATE, 'yyyy')
ORDER BY 1, 2, 3
HS_ID HS_NM TO_C H
---------- ------------------------------ ---- -
1 Alaska 2004 N
1 Alaska 2005 N
1 Alaska 2006 N
1 Alaska 2007 N
2 Alabama 2003 Y
2 Alabama 2004 Y
2 Alabama 2005 Y
2 Alabama 2006 Y
2 Alabama 2007 Y
3 Virginia 2001 Y
3 Virginia 2002 Y
3 Virginia 2003 Y
3 Virginia 2004 Y
4 Virginia 2005 N
4 Virginia 2006 N
4 Virginia 2007 N
16 rows selected.