Home » SQL & PL/SQL » SQL & PL/SQL » Increment sequnce no based on calender date.
Increment sequnce no based on calender date. [message #596552] Tue, 24 September 2013 13:07 Go to next message
nisaros
Messages: 1
Registered: September 2013
Location: Chennai
Junior Member
Hi There,
I have a scenario wherin i need to be increasing the sequence no by 1 for every calender date.
For example lets say if i receive 5 dumps of data for 24/09/2013 it should be as below. For next day the 25th the sequence no should again begin with 1.

24/09/2013 1
24/09/2013 2
24/09/2013 3
24/09/2013 4
24/09/2013 5

25/09/2013 1

Please help

[Updated on: Tue, 24 September 2013 13:07]

Report message to a moderator

Re: Increment sequnce no based on calender date. [message #596553 is a reply to message #596552] Tue, 24 September 2013 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 21966
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
icon5.gif  Re: Increment sequnce no based on calender date. [message #596557 is a reply to message #596552] Tue, 24 September 2013 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 57642
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How is this a SQL question?

Re: Increment sequnce no based on calender date. [message #596566 is a reply to message #596552] Tue, 24 September 2013 14:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1094
Registered: May 2013
Location: Hi-tech city
Senior Member
nisaros wrote on Tue, 24 September 2013 23:37

I have a scenario wherin i need to be increasing the sequence no by 1 for every calender date.
For example lets say if i receive 5 dumps of data for 24/09/2013 it should be as below. For next day the 25th the sequence no should again begin with 1.

24/09/2013 1
24/09/2013 2
24/09/2013 3
24/09/2013 4
24/09/2013 5

25/09/2013 1


You can do this using Analytic function. Use DENSE RANK, then PARTITION BY the date column and ORDER BY a proper column. For each date group you will have a rank as a pseudo column. Use the pseudo column as sequence.

Dense Rank

Regards,
Lalit
icon8.gif  Re: Increment sequnce no based on calender date. [message #596567 is a reply to message #596566] Tue, 24 September 2013 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57642
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How do you use dense_rank from date of dumps????
Who say these dates are in a table?
Do you know anything about the environment?
One more answer you could omit until you know something about what there is.

icon8.gif  Re: Increment sequnce no based on calender date. [message #596569 is a reply to message #596567] Tue, 24 September 2013 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57642
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, dense_rank could NOT give the wanted result as all rows with same date will have the same number which is the opposite of what OP wants.

Re: Increment sequnce no based on calender date. [message #596575 is a reply to message #596569] Tue, 24 September 2013 17:05 Go to previous message
rleishman
Messages: 3655
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hi nisaros, welcome to OraFAQ.

I think Lalit was right to direct you to Analytic Functions. He was probably thinking of the RANK() function rather than DENSE_RANK(), although I would have suggested ROW_NUMBER() as it is more descriptive of what you are trying to achieve.

On the odd chance that you came to an Oracle forum to ask a non-Oracle related question, I apologise for cluttering the forum with yet another response that doesn't answer your question.

Ross Leishman
Previous Topic: how to get Max(value) with its corresponding datetime ...
Next Topic: pl/sql date format
Goto Forum:
  


Current Time: Wed Apr 23 16:29:56 CDT 2014

Total time taken to generate the page: 0.09615 seconds