Home » SQL & PL/SQL » SQL & PL/SQL » time dimension loading week startign from saturday
time dimension loading week startign from saturday [message #218010] Tue, 06 February 2007 06:38 Go to next message
muhammadaqeel
Messages: 13
Registered: November 2006
Location: Jeddah Saudi Arabia
Junior Member

asslam o alaikum all,
please help in loading the time dimension i dont have any script to load it. i have this small script but this is not fullfilling my needs.
first one is the week should start from saturday in dimension
and i need also the week number of month and week number of year

second thing same fields for fiscal year also required
i have this script

select
trunc(sysdate) the_date,
sysdate date_time,
to_char(sysdate,'YYYY') Year,
to_char(sysdate,'Year') Year_spelled,
to_char(sysdate,'YY') short_year,
to_char(sysdate,'YYYY-Q') Year_quarter,
to_char(sysdate,'YYYY-Q') quarter,
to_char(sysdate,'MM') Month,
to_char(sysdate,'Month') Month_spelled,
to_char(sysdate,'YYYY-Q-MM') Year_quarter_Month,
to_char(sysdate,'YYYY-MM') Year_Month

from dual


pleaseeeeeeeeeeeee help me out

thanks

Re: time dimension loading week startign from saturday [message #218040 is a reply to message #218010] Tue, 06 February 2007 09:19 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Try this out. This gets the next 1000 days starting from the earliest coming Saturday..

select 
trunc(v.mydate+b.lv) the_date,
v.mydate+b.lv date_time,
to_char(v.mydate+b.lv,'YYYY') Year,
to_char(v.mydate+b.lv,'Year') Year_spelled,
to_char(v.mydate+b.lv,'YY') short_year,
to_char(v.mydate+b.lv,'YYYY-Q') Year_quarter,
to_char(v.mydate+b.lv,'YYYY-Q') quarter,
to_char(v.mydate+b.lv,'MM') Month,
to_char(v.mydate+b.lv,'Month') Month_spelled,
to_char(v.mydate+b.lv,'YYYY-Q-MM') Year_quarter_Month,
to_char(v.mydate+b.lv,'YYYY-MM') Year_Month
from (select decode(to_char(sysdate,'DY'),'SAT',sysdate,next_day(sysdate,'SAT'))-1 mydate from dual) v, 
       (select level lv from dual connect by level < 1001) b


You may like to rewrite it to an optimal one.

[Updated on: Tue, 06 February 2007 09:27]

Report message to a moderator

Previous Topic: Playing With IP (Merged)
Next Topic: Checking for null in Nested table column
Goto Forum:
  


Current Time: Sat Dec 03 22:12:19 CST 2016

Total time taken to generate the page: 0.07367 seconds