Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #23237] |
Sun, 24 November 2002 23:15  |
sha
Messages: 84 Registered: July 2002
|
Member |
|
|
Hi,
How to retrieve number of days (I mean friday or saturday or sunday etc.) in a given month,year and day, in a single sql statment.
e.g.
In NOvember month 2002.How many fridays?
output is
5
thanx in advance
regards,
shastri
|
|
|
Re: sql query [message #23239 is a reply to message #23237] |
Mon, 25 November 2002 00:28   |
Balamurugan.R
Messages: 91 Registered: March 2001
|
Member |
|
|
Hai,
Try this query
select decode(to_char(last_day('&&dd'),'dd'),28,3,4)+decode(to_char(last_Day('&dd'),'d'),6,1,0)+
decode(to_Char(last_day(add_months('&&dd',-1))+1,'d'),6,1,0) Friday from dual
This query is specifically for friday only, if u want
for any other day, replace 6 into 7 from the above
query and so on...
Hope this would help u
With luv,
Balamurugan.R
|
|
|
|
Re: sql query [message #23243 is a reply to message #23237] |
Mon, 25 November 2002 01:48   |
Balamurugan.R
Messages: 91 Registered: March 2001
|
Member |
|
|
Hai,
select decode(to_char(last_day(sysdate),'dd'),28,3,4)+decode(to_char(last_Day(sysdate),'d'),6,1,0)+
decode(to_Char(last_day(add_months(sysdate,-1))+1,'d'),6,1,0) Friday from dual
Just run this query and let me know the result. If it
works well, then just replace any date with sysdate.
With luv,
Balu
|
|
|
Re: small doubt [message #23265 is a reply to message #23243] |
Mon, 25 November 2002 21:58   |
sha
Messages: 84 Registered: July 2002
|
Member |
|
|
Hi Balamurugan,
Thanx, it is working only with friday if other days and other months then how?One more thing what is 28,3,4 in the decode syntax?
sorry to trouble
regards,
Shastri
|
|
|
Re: stilll doubt [message #23266 is a reply to message #23243] |
Tue, 26 November 2002 00:13   |
sha
Messages: 84 Registered: July 2002
|
Member |
|
|
Hi,
I replaced sysdate with some other date like next month's.I am not getting the correct one.
my system's date format is (oracle format is mm/dd/yy).can this could be the problem ?
regards,
shastri
|
|
|
Re: cleared but small doubt [message #23268 is a reply to message #23243] |
Tue, 26 November 2002 00:57   |
sha
Messages: 84 Registered: July 2002
|
Member |
|
|
hi,
I got it.Thanx alot, but I want to know the first decode statement decode(to_char(last_day('11/10/01'),'dd'),28,3,4)
What is 28,3,4?Sorry to trouble u.
thanx alot.
regards,
shastri
|
|
|
|
|
Re: sql query [message #23332 is a reply to message #23305] |
Mon, 02 December 2002 20:08   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The explanation below is in response to your e-mail which stated:
"Hi Boehmer,
I dont understand how it could know in the month of feb when there are 28 days and when it prints the monyear, it is showing mar2002 and so after 28 days in the month of feb, as u gave rownum<=31.I am confused here.What this all_objects table will do?U r not fetching any field from it.Can we use dual table in place of all_objects?
regards,
shastri"
The purpose of selecting from the all_objects table is to obtain the numbers 1 through 31 for usage in creating dates for comparison to the criteria specified. 31 rows are required, because that is the maximum number of days in a month. You could use any table that has at least 31 rows instead of the all_objects table. Since everyone has an all_objects table and everyone's all_objects table has more than 31 rows, it is an easy standard to choose. You could use the dual table, but you would have to union 31 selects, something like:
SELECT 1 FROM DUAL
UNION
SELECT 2 FROM DUAL
UNION
SELECT 3 FROM DUAL
-- and so on until
UNION
SELECT 29 FROM DUAL
UNION
SELECT 30 FROM DUAL
UNION
SELECT 31 FROM DUAL
/
In the example below, I have broken down the query into little pieces, so that you can hopefully understand how it works. In particular, notice the following lines in the result of one of the queries:
29 FEB2002 01MAR2002
30 FEB2002 02MAR2002
31 FEB2002 03MAR2002
In the above, the months of the month and year selected don't match the month of the day of the month obtained by adding the numbers 29 through 31 to the month and year selected. This is how the excess days beyond the end of the month are eliminated from the result set.
In the examples below, I have used February of 2002 and ultimately selected a count of the Fridays within that month:
SQL> -- selects 31 numbered rows from all_objects table
SQL> -- in order to obtain numbers 1 through 31
SQL> -- and month and year from input (monyear):
SQL> SELECT ROWNUM r,
2 UPPER ('&three_letter_month' || '&four_digit_year') AS monyear
3 FROM all_objects
4 WHERE ROWNUM <= 31
5 /
Enter value for three_letter_month: feb
Enter value for four_digit_year: 2002
R MONYEAR
---------- -------
1 FEB2002
2 FEB2002
3 FEB2002
4 FEB2002
5 FEB2002
6 FEB2002
7 FEB2002
8 FEB2002
9 FEB2002
10 FEB2002
11 FEB2002
12 FEB2002
13 FEB2002
14 FEB2002
15 FEB2002
16 FEB2002
17 FEB2002
18 FEB2002
19 FEB2002
20 FEB2002
21 FEB2002
22 FEB2002
23 FEB2002
24 FEB2002
25 FEB2002
26 FEB2002
27 FEB2002
28 FEB2002
29 FEB2002
30 FEB2002
31 FEB2002
SQL>
SQL>
SQL> -- selects same as above, using inline view:
SQL> SELECT r, monyear
2 FROM (SELECT ROWNUM r,
3 UPPER ('&three_letter_month' || '&four_digit_year') AS monyear
4 FROM all_objects
5 WHERE ROWNUM <= 31)
6 /
Enter value for three_letter_month: feb
Enter value for four_digit_year: 2002
R MONYEAR
---------- -------
1 FEB2002
2 FEB2002
3 FEB2002
4 FEB2002
5 FEB2002
6 FEB2002
7 FEB2002
8 FEB2002
9 FEB2002
10 FEB2002
11 FEB2002
12 FEB2002
13 FEB2002
14 FEB2002
15 FEB2002
16 FEB2002
17 FEB2002
18 FEB2002
19 FEB2002
20 FEB2002
21 FEB2002
22 FEB2002
23 FEB2002
24 FEB2002
25 FEB2002
26 FEB2002
27 FEB2002
28 FEB2002
29 FEB2002
30 FEB2002
31 FEB2002
SQL>
SQL>
SQL> -- adds each number (1 through 31) to month and year input (monyear)
SQL> -- to obtain 31 dates (day_of_mo):
SQL> SELECT r, monyear,
2 TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'DDMONYYYY')
3 AS day_of_mo
4 FROM (SELECT ROWNUM r,
5 UPPER ('&three_letter_month' || '&four_digit_year') AS monyear
6 FROM all_objects
7 WHERE ROWNUM <= 31)
8 /
Enter value for three_letter_month: feb
Enter value for four_digit_year: 2002
R MONYEAR DAY_OF_MO
---------- ------- ---------
1 FEB2002 01FEB2002
2 FEB2002 02FEB2002
3 FEB2002 03FEB2002
4 FEB2002 04FEB2002
5 FEB2002 05FEB2002
6 FEB2002 06FEB2002
7 FEB2002 07FEB2002
8 FEB2002 08FEB2002
9 FEB2002 09FEB2002
10 FEB2002 10FEB2002
11 FEB2002 11FEB2002
12 FEB2002 12FEB2002
13 FEB2002 13FEB2002
14 FEB2002 14FEB2002
15 FEB2002 15FEB2002
16 FEB2002 16FEB2002
17 FEB2002 17FEB2002
18 FEB2002 18FEB2002
19 FEB2002 19FEB2002
20 FEB2002 20FEB2002
21 FEB2002 21FEB2002
22 FEB2002 22FEB2002
23 FEB2002 23FEB2002
24 FEB2002 24FEB2002
25 FEB2002 25FEB2002
26 FEB2002 26FEB2002
27 FEB2002 27FEB2002
28 FEB2002 28FEB2002
29 FEB2002 01MAR2002
30 FEB2002 02MAR2002
31 FEB2002 03MAR2002
SQL>
SQL>
SQL> -- adds where clause to above to limit selection to only those
SQL> -- where month and year of day_of_mo matches month and year input (monyear):
SQL> -- (this eliminates days beyond the end of the month selected, because
SQL> -- those days are in another month; month of day_of_mo does not
SQL> -- match month of month and year input (monyear)):
SQL> SELECT r, monyear,
2 TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'DDMONYYYY')
3 AS day_of_mo
4 FROM (SELECT ROWNUM r,
5 UPPER ('&three_letter_month' || '&four_digit_year') AS monyear
6 FROM all_objects
7 WHERE ROWNUM <= 31)
8 WHERE TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'MONYYYY')
9 = TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY'), 'MONYYYY')
10 /
Enter value for three_letter_month: feb
Enter value for four_digit_year: 2002
R MONYEAR DAY_OF_MO
---------- ------- ---------
1 FEB2002 01FEB2002
2 FEB2002 02FEB2002
3 FEB2002 03FEB2002
4 FEB2002 04FEB2002
5 FEB2002 05FEB2002
6 FEB2002 06FEB2002
7 FEB2002 07FEB2002
8 FEB2002 08FEB2002
9 FEB2002 09FEB2002
10 FEB2002 10FEB2002
11 FEB2002 11FEB2002
12 FEB2002 12FEB2002
13 FEB2002 13FEB2002
14 FEB2002 14FEB2002
15 FEB2002 15FEB2002
16 FEB2002 16FEB2002
17 FEB2002 17FEB2002
18 FEB2002 18FEB2002
19 FEB2002 19FEB2002
20 FEB2002 20FEB2002
21 FEB2002 21FEB2002
22 FEB2002 22FEB2002
23 FEB2002 23FEB2002
24 FEB2002 24FEB2002
25 FEB2002 25FEB2002
26 FEB2002 26FEB2002
27 FEB2002 27FEB2002
28 FEB2002 28FEB2002
SQL>
SQL>
SQL> -- adds to where clause to limit selection to select day of week (Fridays) only:
SQL> SELECT r, monyear,
2 TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'DDMONYYYY')
3 AS day_of_mo
4 FROM (SELECT ROWNUM r,
5 UPPER ('&three_letter_month' || '&four_digit_year') AS monyear
6 FROM all_objects
7 WHERE ROWNUM <= 31)
8 WHERE TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'DY')
9 = UPPER ('&three_letter_day_of_week')
10 AND TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'MONYYYY')
11 = TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY'), 'MONYYYY')
12 /
Enter value for three_letter_month: feb
Enter value for four_digit_year: 2002
Enter value for three_letter_day_of_week: fri
R MONYEAR DAY_OF_MO
---------- ------- ---------
1 FEB2002 01FEB2002
8 FEB2002 08FEB2002
15 FEB2002 15FEB2002
22 FEB2002 22FEB2002
SQL>
SQL>
SQL> -- selects count of above records:
SQL> SELECT COUNT (*)
2 FROM (SELECT ROWNUM r,
3 UPPER ('&three_letter_month' || '&four_digit_year') AS monyear
4 FROM all_objects
5 WHERE ROWNUM <= 31)
6 WHERE TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'DY')
7 = UPPER ('&three_letter_day_of_week')
8 AND TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'MONYYYY')
9 = TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY'), 'MONYYYY')
10 /
Enter value for three_letter_month: feb
Enter value for four_digit_year: 2002
Enter value for three_letter_day_of_week: fri
COUNT(*)
----------
4
|
|
|
|
Re: sql query [message #430179 is a reply to message #430173] |
Mon, 09 November 2009 06:42   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
ayush_anand wrote on Mon, 09 November 2009 12:20great thinking
7 Year old post. Did you really have to dig it up?
|
|
|
|
|
Re: sql query [message #430183 is a reply to message #430182] |
Mon, 09 November 2009 06:52   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
pablolee wrote on Mon, 09 November 2009 13:51[decided not to bother getting involved in such drivel]
Thanks for making me splatter all over my laptop..
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 09:29:53 CST 2025
|