Date vs Same day last year

Database

Date vs Same day last year

Hi,

I want Same Week Day in Last Year, I have the sql for the same in Oracle, Can you please let us know how to achieve this in Tearadata.

"SQL> with t as (
2 select rownum n, to_date('31-DEC-2007') + rownum d from dual connect by rownum <= 366)
3 select
4 to_char(d, 'fmDy DD Month YYYY') this_year
5 , to_char(next_day(trunc(add_months(d, -12), 'iw'), to_char(d, 'DY')), 'fmDy DD Month YYYY') then_iw
6 , to_char(next_day(trunc(add_months(d, -12), 'ww'), to_char(d, 'DY')), 'fmDy DD Month YYYY') then_ww
7 from
8 t
9 where
10 n >= 360;

THIS_YEAR THEN_IW THEN_WW
--------------------- - -------------------- ---------------------
Thu 25 December 2008 Thu 27 December 2007 Thu 27 December 2007
Fri 26 December 2008 Fri 28 December 2007 Fri 28 December 2007
Sat 27 December 2008 Sat 29 December 2007 Sat 29 December 2007
Sun 28 December 2008 Sun 30 December 2007 Sun 30 December 2007
Mon 29 December 2008 Mon 31 December 2007 Mon 31 December 2007
Tue 30 December 2008 Tue 25 December 2007 Tue 25 December 2007"

I need the same fuctionality as above.

Can anybody help me in this.. I will appreciate your help.

Thanks in advance.

Guru
1 REPLY
Enthusiast

Re: Date vs Same day last year

Take a long look at the syscalender.calendar view. You can use the day of year column and number of year column to achieve what you want.

select
....

from
sys_calendar.calender a
inner join
sys_calendar.calendar b
on (a.day_of_year = b.day_of_year)
where a.year_number - 1 = b.year_number

or some permutation thereof...