asked on

create multiple rows by date

I am looking for an sql statement to achieve the below. A member is eligible for a discount if he has coverage for at least 20 days. I have to display the records by month.

Source Data:
MNO eff_Dt end_dt
123 2010/09/21 2011/01/15

Answer"

123 201009 10 days
123 201010 31 days
123 201011 30 days
123 201012 31 days
123 201101 15 days

Open in new window

I have to execute this sql in teradata which is almost similar to Oracle in sql.

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cutie_smily

ASKER

Thank you the query worked in Oracle but I am having hard time converting into teradata. There is no "connect by", last_day etc. I found a calendar table and I am trying to replicate the logic that you gave me.

SELECT calendar_date, day_of_week, day_of_month, day_of_year, day_of_calendar, weekday_of_month, week_of_month, week_of_year, week_of_calendar, month_of_quarter, month_of_year, month_of_calendar, quarter_of_year, quarter_of_calendar, year_of_calendar
FROM SYS_CALENDAR.CALENDAR

Open in new window

Excellent!!! solution.

slightwv (䄆 Netminder)

Can you create a view in Oracle with the select above and query that from Teradata?

cutie_smily

ASKER

I guess not as my source data is in Teradata.

slightwv (䄆 Netminder)

Not sure what all sdstubers code does but this one appears a little easier and produces the results posted above.

The bad news is it also uses CONNET BY.

drop table tab1 purge;
create table tab1(eff_dt date, end_dt date);

insert into tab1 values(to_date('09/21/2010','MM/DD/YYYY'),to_date('01/15/2011','MM/DD/YYYY'));
commit;

select to_char(eff_dt + level -1,'YYYYMM'), count(*)
from tab1
connect by level < end_dt - eff_dt + 2
group by to_char(eff_dt + level -1,'YYYYMM')
order by 1
/

Open in new window

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

another simple option, instead of using recursive/connect by just join to a big table and use row_number()
in oracle ROWNUM would be better, but I don't think teradata has such a thing

SELECT   TO_CHAR(d, 'YYYYMM'), COUNT(*)
    FROM (SELECT DISTINCT eff_dt + n d
            FROM tab1,
                 (SELECT ROW_NUMBER() OVER (ORDER BY ROWID) - 1 n
                    FROM somebigtable
                   )
           WHERE n <= end_dt - eff_dt)
GROUP BY TO_CHAR(d, 'YYYYMM')
ORDER BY 1

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cutie_smily

ASKER

sdstuber,
Your last solution worked like charm..thank you!!!!

slightwv: Thanks for all your help and explanation. I did learn many things from your post.