|
Re: How to find first prior number from current date [message #631563 is a reply to message #631562] |
Thu, 15 January 2015 06:59 |
|
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:21aaditya321 wrote on Thu, 15 January 2015 17:46one 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 #631578 is a reply to message #631562] |
Thu, 15 January 2015 08:57 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
aaditya321 wrote on Thu, 15 January 2015 07:49Suppose 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 #631590 is a reply to message #631581] |
Thu, 15 January 2015 13:24 |
|
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 #631609 is a reply to message #631591] |
Fri, 16 January 2015 03:16 |
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 #631756 is a reply to message #631674] |
Mon, 19 January 2015 09:23 |
|
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 #631772 is a reply to message #631767] |
Mon, 19 January 2015 11:00 |
|
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 #631801 is a reply to message #631800] |
Tue, 20 January 2015 00:07 |
|
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 #631821 is a reply to message #631818] |
Tue, 20 January 2015 04:09 |
|
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
|
|
|
|
|
|
|
|