?
Solved

create multiple rows by date

Posted on 2011-05-10
10
Medium Priority
?
921 Views
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

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.
0
Comment
Question by:cutie_smily
  • 4
  • 3
  • 3
10 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1200 total points
ID: 35734981
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
 

Author Comment

by:cutie_smily
ID: 35737893
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35738409
Can you create a view in Oracle with the select above and query that from Teradata?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:cutie_smily
ID: 35738465
I guess not as my source data is in Teradata.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35738486
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 total points
ID: 35738565
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 total points
ID: 35738647
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35738804
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1200 total points
ID: 35738896
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
 

Author Comment

by:cutie_smily
ID: 35743554
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

850 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