Home » SQL & PL/SQL » SQL & PL/SQL » sql help
sql help [message #221270] Mon, 26 February 2007 01:27 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
want one query: The query has to display all the days in a month in number format.

Ex: Month: January ---> it has to diplay like 1, 2, 3, 4, 5, .............31.


Could you please send me this query any one
thanks
srinivas
Re: sql help [message #221288 is a reply to message #221270] Mon, 26 February 2007 03:30 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
any body help me
please
Thanks,
Srinivas
Re: sql help [message #221290 is a reply to message #221270] Mon, 26 February 2007 03:31 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Like this ?
SQL> with src as (select to_date(rownum,'MM') date# from dict where rownum <=2)
  2  select to_char(date#,'Month'),max(substr(sys_connect_by_path(day,','),2)) days
  3  from (
  4  select t.date#, days.day
  5  from
  6  (
  7  select date#, last_day(date#)-trunc(date#,'MM')+1 days
  8  from src
  9  ) t,
 10  (select rownum day from dict where rownum <=31) days
 11  where days.day <= t.days
 12  )
 13  start with day = 1
 14  connect by prior date#=date# and day = prior day + 1
 15  group by date#
 16  /

TO_CHAR(DATE#,'MONTH')
------------------------------------
DAYS
------------------------------------------------------------
January
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,
24,25,26,27,28,29,30,31

February
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,
24,25,26,27,28

Rgds.
Re: sql help [message #221304 is a reply to message #221290] Mon, 26 February 2007 04:03 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
gr8

The query is working fine.

But, my requirement is like this:

If a user wants to see the report today (it means we need to consider today's sysdate), the report has to give the details of first day of this month to today's date. (It means from February 1st to 26th).

So, i want a query which displays the first day of the month to till sysdate.

Could you please modify this query and sent it to me.
Re: sql help [message #221307 is a reply to message #221304] Mon, 26 February 2007 04:12 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
plz help me anyone
thanks,
srinivas
Re: sql help [message #221309 is a reply to message #221304] Mon, 26 February 2007 04:12 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
SQL> select to_char(date#,'Month'),max(substr(sys_connect_by_path(day,','),2)) days
  2  from (
  3  select t.date#, days.day
  4  from
  5  (
  6  select trunc(sysdate) date#, trunc(sysdate)-trunc(trunc(sysdate),'MM')+1 days
  7  from dual
  8  ) t,
  9  (select rownum day from dict where rownum <=31) days
 10  where days.day <= t.days
 11  )
 12  start with day = 1
 13  connect by prior date#=date# and day = prior day + 1
 14  group by date#
 15  /

TO_CHAR(DATE#,'MONTH')
------------------------------------
DAYS
------------------------------------------------------------
February
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,
24,25,26


Rgds.
Re: sql help [message #221320 is a reply to message #221309] Mon, 26 February 2007 04:47 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
gr8
thanks
srinivas
Re: sql help [message #221352 is a reply to message #221307] Mon, 26 February 2007 07:48 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
pavuluri wrote on Mon, 26 February 2007 05:12
plz help me anyone
thanks,
srinivas


Dude, would you a have a little frickin' patience? Every message you post, if you don't get a response in two hours, you ask people again for help. You know, people might be sleeping when you are in the middle of your day. I have decided to stop answering any of your questions.
Re: sql help [message #221357 is a reply to message #221352] Mon, 26 February 2007 07:54 Go to previous message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
ok
i agree u's Suggestion
thanks
srinivas
Previous Topic: i want basic query
Next Topic: How to extract parameter value whose name is in variable?
Goto Forum:
  


Current Time: Thu Dec 08 06:15:41 CST 2016

Total time taken to generate the page: 0.10718 seconds