create multiple rows by date

Posted on 2011-05-10
Last Modified: 2013-11-11
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

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.
Question by:cutie_smily
    LVL 73

    Assisted Solution

    I don't have a teradata system to test on but this works on Oracle

    SELECT mno,
           TO_CHAR(ADD_MONTHS(TRUNC(eff_dt, 'mm'), COLUMN_VALUE), 'yyyymm'),
               WHEN COLUMN_VALUE = 0
                   (LAST_DAY(eff_dt) - eff_dt + 1) || ' days'
               WHEN ADD_MONTHS(TRUNC(eff_dt, 'mm'), COLUMN_VALUE) = TRUNC(end_dt, 'mm')
                   TO_CHAR(end_dt, 'dd') || ' days'
                   TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(eff_dt, 'mm'), COLUMN_VALUE)), 'dd') || ' days'
      FROM (SELECT *
              FROM (SELECT 123 mno,
                           TO_DATE('2010/09/21', 'yyyy/mm/dd') eff_dt,
                           TO_DATE('2011/01/15', 'yyyy/mm/dd') end_dt
                      FROM DUAL),
                   TABLE(SELECT COLLECT(LEVEL - 1)
                           FROM DUAL
                         CONNECT BY ADD_MONTHS(TRUNC(eff_dt, 'mm'), LEVEL - 1) <= end_dt))

    Author Comment

    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

    Open in new window

    Excellent!!! solution.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Can you create a view in Oracle with the select above and query that from Teradata?

    Author Comment

    I guess not as my source data is in Teradata.
    LVL 76

    Expert Comment

    by: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'));
    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

    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    Sorry.  I'm not a Teradata person but a quick scan of the docs show you can create stored code and temporary tables.

    Basically what the connect by does is create artificial rows to join with.  This creates a list of all the days between those dates.  Then it's a simple group by select statement.

    If you can create a loop in stored code that basically loops through the number of days and maybe inserts a 'day' into the temp table, then you could easily query that?

    I'll search around a little and see what other SQL constructs you might be able to use.
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    Looks like you do have a recursive SQL construct available:

    Two Ways to Specify a Recursive Query
    You can specify a recursive query by:
    ? Preceding a query with the WITH RECURSIVE clause
    ? Creating a permanent view using the RECURSIVE clause in a CREATE VIEW statement

    You just need to get it to return the number of days between those dates.

    Now that you know the construct you just need to find an example that puts it all together.

    Here are a couple quick links I found that talk about RECURSIVE and Oracle's CONNECT:
    LVL 73

    Expert Comment

    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
        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)
    ORDER BY 1

    Open in new window

    LVL 73

    Accepted Solution

    to make your query more efficient, you might want to pick a "bigtable"
    that is only as big as you need.

    or, if your calendar table is just a list of days

        FROM tab1, mycalendar
       WHERE BETWEEN tab1.eff_dt AND tab1.end_dt
    ORDER BY 1;

    Author Comment

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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article describes some very basic things about SQL Server filegroups.
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now