| fetching data for a fiscal week of prevoius year [message #372106] |
Wed, 10 January 2001 07:57  |
IT Lover
Messages: 5
Registered: January 2001
|
Junior Member |
|
|
Hi There !
I have a table - students. Males, females and cre_date are three columns in it. Cre_date is enrollment date for the students. I need to find the number of males and females that were registered in 3-4 weeks earlier to current week. I have framed the following query as :
SELECT 'Fiscal Week ' ||to_number(to_char(a.cre_date,'WW')) week_no,COUNT(males), COUNT(females)
FROM students
WHERE to_number(to_char(students.cre_date,'WW')) in (SELECT to_number(TO_CHAR(sysdate ,'WW'))-3 FROM DUAL)
group by 'Fiscal Week ' ||to_number(to_char(a.cre_date,'WW'))
UNION
SELECT 'Fiscal Week ' ||to_number(to_char(a.cre_date,'WW')) week_no,COUNT(males), COUNT(females)
FROM students
WHERE to_number(to_char(students.cre_date,'WW')) in (SELECT to_number(TO_CHAR(sysdate ,'WW'))-4 FROM DUAL)
The query worked fine till last week of december , but it fails now as :
SELECT to_number(TO_CHAR(sysdate ,'WW'))-4 FROM DUAL
fetches -2.
and
SELECT to_number(TO_CHAR(sysdate ,'WW'))-3 FROM DUAL
fetches -1
Any idea what could be done so that in such cases it fetches data from previous year, ie in this case data for fiscal week 51 and 52 of year 2000.
Thanks,
IT Lover
|
|
|
|
| Re: fetching data for a fiscal week of prevoius year [message #372112 is a reply to message #372106] |
Wed, 10 January 2001 11:46  |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
SELECT decode(to_number(TO_CHAR(sysdate ,'WW')), 1,54, 2,55, 3,56, 4,57, to_number(TO_CHAR(sysdate ,'WW'))) -4 FROM DUAL
SELECT decode( to_number(TO_CHAR(sysdate ,'WW')), 1,54, 2,55, 3,56, to_number(TO_CHAR(sysdate ,'WW')) ) -3 FROM DUAL
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
