Home » Developer & Programmer » Reports & Discoverer » select weeks number per months and year as 1 .....52 week on matrix report 6i (merged)
icon7.gif  select weeks number per months and year as 1 .....52 week on matrix report 6i (merged) [message #473322] Fri, 27 August 2010 06:50 Go to next message
waleedtot
Messages: 4
Registered: August 2010
Junior Member
wanna to make matrix report retrieve months year and the number of weeks from dual table it supposes to the weeks number is 52 or 53 week
the months and weeks on the rows please find attach pic it show what i need to do
for exmple Razz

month name: January February March April May June July August September Nov October December
no of week : 1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-20-21-22....... TO 52 week
  • Attachment: qqq.JPG
    (Size: 48.35KB, Downloaded 973 times)
Re: select weeks number per months and year as 1 .....52 week on matrix report 6i (merged) [message #473391 is a reply to message #473322] Fri, 27 August 2010 14:25 Go to previous messageGo to next message
Alien
Messages: 291
Registered: June 1999
Senior Member
Hi,

what are the black spots in the picture? Do you need those too?

So do you need a query to provide you with date and week-number. Or do you have the query and do you want to know how to put it in a report?

Regards,

Arian
Re: select weeks number per months and year as 1 .....52 week on matrix report 6i (merged) [message #473398 is a reply to message #473391] Fri, 27 August 2010 16:18 Go to previous messageGo to next message
waleedtot
Messages: 4
Registered: August 2010
Junior Member
the black spots as you know the matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data
the columns is months name and no of week
and the row is employee names and the spots is dates
but i need in matrix report make select in formula to get the names of months( January February March .....
AND under this columns make select on formula to get the numbers of weeks it should start from 1.....52 weeks in year

So do you need a query to provide you with date and week-number.
yes i need a query select months names and weeks no from dual table or any solutions to put in formula
for your info dear i get query and put it in formula and get me error (exact fetch returns more than requested number of rows)
this select is get months names in the year

SELECT TO_CHAR( ADD_MONTHS( TRUNC(SYSDATE,'year'), LEVEL-1 ), 'Mon' ) the_month
FROM dual
CONNECT BY level <= 12;

i hope to get solution and get a query for weeks number to put in formula to run on matrix report
Re: select weeks number per months and year as 1 .....52 week on matrix report 6i (merged) [message #473467 is a reply to message #473398] Sat, 28 August 2010 13:23 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A query like this one will return months and weeks for all days within a year:
select distinct
       d_month,
       d_week
from (-- months and weeks for every day in a year
      select
        datum d_date,
        to_char(datum, 'mm') d_month,
        to_char(datum, 'ww') d_week
      from (-- all days within the current year
            select
            trunc(sysdate, 'yyyy') + level - 1 datum
            from dual
            connect by level <= add_months(trunc(sysdate, 'yyyy'), 12)
                              - trunc(sysdate, 'yyyy')
           )
     )
order by d_month, d_week;

As I never did what you are looking for, I don't know what to do with weeks that span two adjacent months. For example:
M  W
-- --
01 01
01 02
01 03
01 04
01 05  --> the 5th week belongs to both Jan and Feb.
02 05  --> Where would you like to put it?
02 06
02 07
02 08

Anyway: perhaps the above query throws some light to what you are looking for and you'll be able to create a solution.
Re: select weeks number per months and year as 1 .....52 week on matrix report 6i (merged) [message #473474 is a reply to message #473467] Sat, 28 August 2010 17:49 Go to previous messageGo to next message
waleedtot
Messages: 4
Registered: August 2010
Junior Member
really appreciate your work but i need to understand you what i need from weeks to say to me you must make maintenance
in this week no from this month
by the way i get solution as you but the same problem I need the output the select to be as this type
to put in matrix report

M W
-- --
January 01
02
03
04
05 --> the 5th week belongs to both Jan and Feb.
February 05 --> Where would you like to put it?
06
07
08
as this January February March April May June July August September Nov October December
1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-20-21-22....... TO 52 week


and this my query
SQL> SELECT DISTINCT TO_CHAR(TO_DATE('2010-01-01','YYYY-MM-DD')+LEVEL*7,' MON ') month
2 ,TO_CHAR(TO_DATE('2010-01-01','YYYY-MM-DD')+LEVEL*7,' WW ')WEEK
3 FROM DUAL
4 CONNECT BY TO_DATE('2010-01-01','YYYY-MM-DD')+LEVEL*7 < TO_DATE('2010-12-31','YYYY-MM-DD')
5 order by TO_CHAR(TO_DATE('2010-01-01','YYYY-MM-DD')+LEVEL*7,' WW ');

MONTH WEEK
----- ----
JAN 02 but you see its suppose start with week number 01 and Jan is repeat
JAN 03
JAN 04
JAN 05
FEB 06
FEB 07
FEB 08
FEB 09
MAR 10
MAR 11
MAR 12
MAR 13
APR 14
APR 15
APR 16
APR 17
APR 18
MAY 19
MAY 20
MAY 21

MONTH WEEK
----- ----
MAY 22
JUN 23
JUN 24
JUN 25
JUN 26
JUL 27
JUL 28
JUL 29
JUL 30
JUL 31
AUG 32
AUG 33
AUG 34
AUG 35
SEP 36
SEP 37
SEP 38
SEP 39
OCT 40
OCT 41
OCT 42

MONTH WEEK
----- ----
OCT 43
OCT 44
NOV 45
NOV 46
NOV 47
NOV 48
DEC 49
DEC 50
DEC 51
DEC 52

51 rows selected

SQL> i hope to see solution no need to repeat the month name on the select
Re: select weeks number per months and year as 1 .....52 week on matrix report 6i (merged) [message #473493 is a reply to message #473474] Sun, 29 August 2010 07:14 Go to previous message
waleedtot
Messages: 4
Registered: August 2010
Junior Member
dear about the black spots in the picture this is date i need this date return on same week no
in matrix report
SUPPOSE
:month this return month name
:week this return week no per year for exmple w1 w2 w3 w4 .... w52
:next_date return date i need this date return under week number
do u have any idea am work in matrix report
Previous Topic: lov selecting
Next Topic: long running report
Goto Forum:
  


Current Time: Fri Apr 19 14:31:26 CDT 2024