|
|
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 |
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 |
|
Littlefoot
Messages: 21823 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 |
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
|
|
|
|