Derived table with Date Range

[Former Member]

Hello,

I like to create a derived table in the Universe with Date Range from year 2001 to year 2010 and select only week end (saturdays) dates.

For example:

2001/1/6

2001/1/13

2001/1/20

2001/1/27

2001/2/3

...

2010/12/25

-

---

However I don't have any table/column to get the values. I need this to support the user prompt and will be used for display purposes only. Any ideas about the above query?

Thanks in advance

AK

[Former Member · Accepted solution]

Hi ,

Try This:

next_day(trunc(sysdate), 'saturday') - 7

All the Best,

Madhu....

[Former Member]

Hi akbarid ,

use this formula:

next_day(trunc(sysdate), 'saturday') - 7

it gives you only week end days....

All the Best.

Edited by: Praveen Vodela on Jan 28, 2011 11:07 AM

[Former Member]

Thanks to everyone, sometime it helps to have new ideas. I played with the queries and found out an easy way to get the week-end dates for five years and than can be easily expanded.

ANSI SQL supported by Universe Designer

SELECT ((dt3.num * 10 + dt2.num)*7)+date('2001/01/06') Week_End_Date

FROM (SELECT 0 AS num UNION ALL

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9) dt2

,(SELECT 0 AS num UNION ALL

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10 UNION ALL

SELECT 11 UNION ALL

SELECT 12 UNION ALL

SELECT 13 UNION ALL

SELECT 14 UNION ALL

SELECT 15 UNION ALL

SELECT 16 UNION ALL

SELECT 17 UNION ALL

SELECT 18 UNION ALL

SELECT 19 UNION ALL

SELECT 20 UNION ALL

SELECT 21 UNION ALL

SELECT 22 UNION ALL

SELECT 23 UNION ALL

SELECT 24 UNION ALL

SELECT 25) dt3

ORDER BY 1

Edited by: akbarid on Jan 27, 2011 5:23 PM

Edited by: akbarid on Jan 27, 2011 5:26 PM

[Former Member]

If you just want Saturdays, then use something like:

SELECT TRUNC (each_day) saturday
  FROM (SELECT     (SYSDATE - 3650) + LEVEL each_day
              FROM DUAL
        CONNECT BY LEVEL <= 3650)
 WHERE TO_CHAR (each_day, 'D') = '6'

[amrsalem1983]

whats your source database?

oracle? sql server ?,, or what?

[Former Member]

source db is oracle, and data further extracted through ETL process to Neteeza Server which supports ANSI SQL.

Thanks

AK

[amrsalem1983]

first of all you have to have a calendar table having the all the dates

second thing you do to build a derived table with this SQL

SELECT myDate

from myCalendarTable

where to_char(myDate,'DAY') = 'SATURDAY'

there is no way to select too many rows without having a calendar table

good luck

[Former Member]

You may need to add a trailing space or wrap the to_Char formula in an rtrim function - the to_Char (day) functionality converts to a char(9) to fit WEDNESDAY.

As you suggest, the best route is the calendar table though.

Regards,

Mark