Home » SQL & PL/SQL » SQL & PL/SQL » How to find first prior number from current date
How to find first prior number from current date [message #631562] Thu, 15 January 2015 06:49 Go to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Hi Guys,

Please help me.
Suppose today(current date)publish English 100 Books then now we have to find out when published English books(in numbers) first prior date from current date.
Re: How to find first prior number from current date [message #631563 is a reply to message #631562] Thu, 15 January 2015 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Lalit Kumar B wrote on Thu, 15 January 2015 13:21
aaditya321 wrote on Thu, 15 January 2015 17:46
one more query here, please give me guide.
...
Michel Cadot wrote on Tue, 13 January 2015 23:07

I remind you the rules:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Use SQL*Plus and copy and paste what you already tried.

...

[Updated on: Thu, 15 January 2015 06:59]

Report message to a moderator

Re: How to find first prior number from current date [message #631565 is a reply to message #631563] Thu, 15 January 2015 07:38 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
I am sharing our table format here:
books_name     Book_quantity       Date
English          100             15-jan-2015
Hindi            142             14-jan-2015
Science          90              13-jan-2015
English          80              12-jan-2015
English          85              11-jan-2015


means we need to English book quantity of current date & first prior date book quantity when English book has published.
Re: How to find first prior number from current date [message #631566 is a reply to message #631565] Thu, 15 January 2015 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you sure this is a test case?
Re: How to find first prior number from current date [message #631567 is a reply to message #631566] Thu, 15 January 2015 07:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: How to find first prior number from current date [message #631569 is a reply to message #631566] Thu, 15 January 2015 07:57 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Yes, Its test case has done Michel.

[Updated on: Thu, 15 January 2015 07:59]

Report message to a moderator

Re: How to find first prior number from current date [message #631571 is a reply to message #631569] Thu, 15 January 2015 08:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We speak SQL?
do you?
if so, please post useful & valid SQL that we can run ourselves.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: How to find first prior number from current date [message #631578 is a reply to message #631562] Thu, 15 January 2015 08:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
aaditya321 wrote on Thu, 15 January 2015 07:49
Suppose today(current date)publish English 100 Books then now we have to find out when published English books(in numbers) first prior date from current date.


Open Database SQL Language Reference manual and read about analytic function LAG.

SY.
Re: How to find first prior number from current date [message #631581 is a reply to message #631569] Thu, 15 January 2015 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
aaditya321 wrote on Thu, 15 January 2015 14:57
Yes, Its test case has done Michel.


OK this is the SQL query: SQL query.

Re: How to find first prior number from current date [message #631590 is a reply to message #631581] Thu, 15 January 2015 13:24 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
My query is like below:
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jan 16 00:23:29 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> Create table Book_detail(book_name varchar2(15),
  2                       book_quantity number(5),
  3                       start_date Date);

Table created.


SQL> insert into book_detail values('English', 100, '15-jan-2015');

1 row created.

SQL> insert into book_detail values('Hindi', 142, '14-jan-2015');

1 row created.

SQL> insert into book_detail values('Science', 90, '13-jan-2015');

1 row created.

SQL> insert into book_detail values('English', 80, '12-jan-2015');

1 row created.

SQL> insert into book_detail values('English', 85, '11-jan-2015');

1 row created.

SQL> select * from book_detail;

BOOK_NAME       BOOK_QUANTITY START_DAT
--------------- ------------- ---------
English                   100 15-JAN-15
Hindi                     142 14-JAN-15
Science                    90 13-JAN-15
English                    80 12-JAN-15
English                    85 11-JAN-15
SQL> 


I need below output:

BOOK_NAME    BOOK_QUANTITY   BOOK_QUANTITY-1     DIFF     PERCENT
eNGLISH           100            80               20        25%
Re: How to find first prior number from current date [message #631591 is a reply to message #631590] Thu, 15 January 2015 13:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
As I already mentioned, use analytic function LAG. There are plenty example on this forum and on the web.

SY.
Re: How to find first prior number from current date [message #631609 is a reply to message #631591] Fri, 16 January 2015 03:16 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear aaditya321

This may solve your problem.

select book_name,book_quantity,book_quantity_1,(book_quantity-book_quantity_1) diff
,decode(book_quantity_1,0,100,(((book_quantity-book_quantity_1)*100)/book_quantity_1)) percent
from
(
	select book_name,book_quantity
	,nvl(lead(BOOK_QUANTITY) over(partition by book_name order by book_name,start_date desc),0) book_quantity_1
	,row_number() over (partition by book_name order by book_name,start_date desc) row_num
	from book_detail
)
where row_num = 1
order by book_name
/

BOOK_NAME       BOOK_QUANTITY BOOK_QUANTITY_1       DIFF    PERCENT
--------------- ------------- --------------- ---------- ----------
English                   100              80         20         25
Hindi                     142               0        142        100
Science                    90               0         90        100



Regards

Jimit
Re: How to find first prior number from current date [message #631674 is a reply to message #631609] Fri, 16 January 2015 23:21 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Thank you Jimit.
Re: How to find first prior number from current date [message #631756 is a reply to message #631674] Mon, 19 January 2015 09:23 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
If we use count function then how to make below query.

SQL> insert into book_detail values('English', 200, '15-jan-2015');

1 row created.

SQL> insert into book_detail values('English', 150, '15-jan-2015');

1 row created.

SQL> insert into book_detail values('English', 300, '15-jan-2015');

1 row created.

SQL> insert into book_detail values('English', 180, '12-jan-2015');

1 row created.

SQL> insert into book_detail values('English', 90, '12-jan-2015');

1 row created.

SQL> select * from book_detail;

BOOK_NAME       BOOK_QUANTITY START_DAT
--------------- ------------- ---------
English                   100 15-JAN-15
Hindi                     142 14-JAN-15
Science                    90 13-JAN-15
English                    80 12-JAN-15
English                    85 11-JAN-15
English                   200 15-JAN-15
English                   150 15-JAN-15
English                   300 15-JAN-15
English                   180 12-JAN-15
English                    90 12-JAN-15

10 rows selected.

SQL> commit
  2  /

Commit complete.


and my desire output:
Book_name   Book_quantity    Book_quantity_1    Diff     Percent
English         4                 3              1         25%

[Updated on: Mon, 19 January 2015 09:34]

Report message to a moderator

Re: How to find first prior number from current date [message #631760 is a reply to message #631756] Mon, 19 January 2015 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Explain.

Re: How to find first prior number from current date [message #631765 is a reply to message #631760] Mon, 19 January 2015 10:22 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
here if we use count(book_quantity) Book_quantity in '15-jan-2015' then output will come = 4
and if we use count(book_quantity) Book_quantity_1 in '12-jan-2015' then output will come = 3

Diff = Book_quantity - Book_quantity_1
Percent = 100*(Book_quantity - Book_quantity_1)/Book_quantity
Re: How to find first prior number from current date [message #631766 is a reply to message #631765] Mon, 19 January 2015 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just COUNT grouping by book_name and date, and use LAG to get the difference with the previous value, and pivot to get it on one line.

Quote:
Always post your Oracle version, with 4 decimals.

Re: How to find first prior number from current date [message #631767 is a reply to message #631766] Mon, 19 January 2015 10:32 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
My Oracle version is following:
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jan 19 20:06:03 2015

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Re: How to find first prior number from current date [message #631772 is a reply to message #631767] Mon, 19 January 2015 11:00 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
We try but got some error, please guide me
SQL> select book_name, count(book_quantity) book_quantity, count(book_quantity) book_quantity_1,(boo
k_quantity-book_quantity_1) diff
  2  ,decode(book_quantity_1,0,100,(((book_quantity-book_quantity_1)*100)/book_quantity)) percent
  3  from
  4  (
  5   select book_name,book_quantity
  6   ,nvl(lag(BOOK_QUANTITY) over(partition by book_name order by book_name,start_date desc),0) boo
k_quantity_1
  7   ,row_number() over (partition by book_name order by book_name,start_date desc) row_num
  8   from book_detail
  9  )
 10  where row_num = 1
 11  group by book_name
 12  order by book_name
 13  /
select book_name, count(book_quantity) book_quantity, count(book_quantity) book_quantity_1,(book_qua
                                                                                            *
ERROR at line 1:
ORA-00979: not a GROUP BY expression


SQL> 
Re: How to find first prior number from current date [message #631776 is a reply to message #631772] Mon, 19 January 2015 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do it in the plan I gave you.
Do it step by step.
Don't try to write the query in a single shot.

Re: How to find first prior number from current date [message #631800 is a reply to message #631776] Tue, 20 January 2015 00:04 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Please help guys for writing my query.
Re: How to find first prior number from current date [message #631801 is a reply to message #631800] Tue, 20 January 2015 00:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ COUNT grouping by book_name and date
2/ use LAG to get the difference with the previous value
3/ pivot to get it on one line.

Write the quey which does point 1.
Then using this query as inline view, write the query that does point 2.
Then using this later query as inline view, write your final query.

[Updated on: Tue, 20 January 2015 01:16]

Report message to a moderator

Re: How to find first prior number from current date [message #631807 is a reply to message #631801] Tue, 20 January 2015 00:34 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Also I tried it but got some error, please solve my error.
Re: How to find first prior number from current date [message #631810 is a reply to message #631807] Tue, 20 January 2015 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Also I tried


Post it.

Quote:
please solve my error


Which error? On the above query? The query is completely wrong, so the error is meaningless.


Re: How to find first prior number from current date [message #631811 is a reply to message #631810] Tue, 20 January 2015 01:20 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Please suggest me Michel, how to make correct query.
Re: How to find first prior number from current date [message #631812 is a reply to message #631811] Tue, 20 January 2015 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 20 January 2015 07:07

1/ COUNT grouping by book_name and date
2/ use LAG to get the difference with the previous value
3/ pivot to get it on one line.

Write the quey which does point 1.
Then using this query as inline view, write the query that does point 2.
Then using this later query as inline view, write your final query.

Re: How to find first prior number from current date [message #631813 is a reply to message #631812] Tue, 20 January 2015 01:38 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
hopeless answer,
Re: How to find first prior number from current date [message #631814 is a reply to message #631813] Tue, 20 January 2015 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Hopeless poster.
If you can't even write the query for point 1 then you have to urgently take a basic SQL course.

Re: How to find first prior number from current date [message #631815 is a reply to message #631814] Tue, 20 January 2015 01:57 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Already I have used count, group by and lag function, please tell me Michel how to write our query.

[Updated on: Tue, 20 January 2015 01:58]

Report message to a moderator

Re: How to find first prior number from current date [message #631817 is a reply to message #631815] Tue, 20 January 2015 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't care what you have done, this was wrong.
NOW just do what I ask: restart from the beginning.

Re: How to find first prior number from current date [message #631818 is a reply to message #631817] Tue, 20 January 2015 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Actually you don't need the point 3.
So just:
1/ count the books
2/ use LAG to get the count from previous date
3/ use - to get the diff and / to get the percent

In the end, when you'll have a query which works, simplify it.

[Updated on: Sat, 15 April 2017 11:41]

Report message to a moderator

Re: How to find first prior number from current date [message #631821 is a reply to message #631818] Tue, 20 January 2015 04:09 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
When we try another query then got some more error.
SQL> select book_name, book_quantity, book_quantity_1,(book_quantity-book_quantity_1) diff
  2  ,decode(book_quantity_1,0,100,(((book_quantity-book_quantity_1)*100)/book_quantity)) percent
  3  from
  4  (
  5   select book_name,count(book_quantity)
  6   ,nvl(lag(BOOK_QUANTITY) over(partition by book_name order by book_name,start_date desc),0) boo
k_quantity_1
  7   ,row_number() over (partition by book_name order by book_name,start_date desc) row_num
  8   from book_detail
  9    group by book_name
 10  )
 11  where row_num = 1
 12  order by book_name
 13  /

Again got ORA-00979: not a GROUP BY expression
Re: How to find first prior number from current date [message #631825 is a reply to message #631821] Tue, 20 January 2015 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It seems to me you are mixing the questions you posted in this topic.
I was talking about your latest question where BOOK_QUANTITY column is irrelevant.

Quote:
When we try another query then got some more error


I don't care what you tried. Your approach of the question is wrong. Stop digging you are close to the center of the Earth.
Do what I told you.

[Updated on: Tue, 20 January 2015 05:00]

Report message to a moderator

Re: How to find first prior number from current date [message #631921 is a reply to message #631825] Wed, 21 January 2015 23:23 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Thanks for giving good tips Michel.
Re: How to find first prior number from current date [message #631924 is a reply to message #631921] Thu, 22 January 2015 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have you a solution?
If so, post it.


Re: How to find first prior number from current date [message #632030 is a reply to message #631924] Fri, 23 January 2015 09:21 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Actually I understood here my fault.
Re: How to find first prior number from current date [message #632033 is a reply to message #632030] Fri, 23 January 2015 09:27 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Good. So it means you got your solution. Hence, please post it as Michel already said.
Previous Topic: Error While Fetching Data Through Database Link In Oracle
Next Topic: Subtracting Years from As_Of_Date
Goto Forum:
  


Current Time: Tue Mar 19 05:58:11 CDT 2024