Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #23237] Sun, 24 November 2002 23:15 Go to next message
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 Go to previous messageGo to next message
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
ReNot working Balamurugan [message #23242 is a reply to message #23237] Mon, 25 November 2002 01:25 Go to previous messageGo to next message
sha
Messages: 84
Registered: July 2002
Member
Hi Balu,
The query u sent is not working.Kindly check it and send me.
regards,
shastri
Re: sql query [message #23243 is a reply to message #23237] Mon, 25 November 2002 01:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
not working below 2000year [message #23269 is a reply to message #23268] Tue, 26 November 2002 02:30 Go to previous messageGo to next message
sha
Messages: 84
Registered: July 2002
Member
Hi,
It is not working for years below 2000.
really sorry to trouble u
regards,
shastri
Re: sql query [message #23305 is a reply to message #23237] Thu, 28 November 2002 04:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
SELECT COUNT (*)
FROM   (SELECT ROWNUM r,
               UPPER ('&three_letter_month' || '&four_digit_year') AS monyear
        FROM   all_objects
        WHERE  ROWNUM <= 31)
WHERE  TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'DY') 
       = UPPER ('&three_letter_day_of_week')
AND    TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY') + r - 1, 'MONYYYY') 
       = TO_CHAR (TO_DATE ('01' || monyear, 'DDMONYYYY'), 'MONYYYY')
/
Re: sql query [message #23332 is a reply to message #23305] Mon, 02 December 2002 20:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 #430173 is a reply to message #23332] Mon, 09 November 2009 06:20 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
great thinking
Re: sql query [message #430179 is a reply to message #430173] Mon, 09 November 2009 06:42 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
ayush_anand wrote on Mon, 09 November 2009 12:20
great thinking

7 Year old post. Did you really have to dig it up?
Re: sql query [message #430181 is a reply to message #430179] Mon, 09 November 2009 06:45 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
People must get appreciation that they deserve..even after 7 years
Re: sql query [message #430182 is a reply to message #430181] Mon, 09 November 2009 06:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
[decided not to bother getting involved in such drivel]
Re: sql query [message #430183 is a reply to message #430182] Mon, 09 November 2009 06:52 Go to previous messageGo to next message
Frank
Messages: 7880
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..
Re: sql query [message #430185 is a reply to message #430183] Mon, 09 November 2009 06:54 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I'm going to assume coffee Smile
Previous Topic: solution to mutating trigger
Next Topic: Oracles Alternative For SQLServers Cross Apply (merged)
Goto Forum:
  


Current Time: Sun Dec 04 22:47:11 CST 2016

Total time taken to generate the page: 0.11858 seconds