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.
cutie_smilyAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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


SELECT   TO_CHAR(day, 'YYYYMM'), COUNT(*)
    FROM tab1, mycalendar
   WHERE mycalendar.day BETWEEN tab1.eff_dt AND tab1.end_dt
GROUP BY TO_CHAR(day, 'YYYYMM')
ORDER BY 1;
0
 
sdstuberConnect With a Mentor Commented:
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'),
       CASE
           WHEN COLUMN_VALUE = 0
           THEN
               (LAST_DAY(eff_dt) - eff_dt + 1) || ' days'
           WHEN ADD_MONTHS(TRUNC(eff_dt, 'mm'), COLUMN_VALUE) = TRUNC(end_dt, 'mm')
           THEN
               TO_CHAR(end_dt, 'dd') || ' days'
           ELSE
               TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(eff_dt, 'mm'), COLUMN_VALUE)), 'dd') || ' days'
       END
  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))
0
 
cutie_smilyAuthor Commented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
slightwv (䄆 Netminder) Commented:
Can you create a view in Oracle with the select above and query that from Teradata?
0
 
cutie_smilyAuthor Commented:
I guess not as my source data is in Teradata.
0
 
slightwv (䄆 Netminder) Commented:
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

0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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

www.info.teradata.com/edownload.cfm?itemid=062280003



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:

http://www.bikinfo.com/HTML/TD/TD_vs_Oracle.html#_Toc_Recursive%20SQL

http://walkingoncoals.blogspot.com/2009/12/fun-with-recursive-sql-part-1.html
0
 
sdstuberCommented:
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

0
 
cutie_smilyAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.