Home » SQL & PL/SQL » SQL & PL/SQL » Help on running difference sql query (2 merged by CM) (oracle 11g)
Help on running difference sql query (2 merged by CM) [message #668786] |
Wed, 14 March 2018 07:49 |
|
bharathi89
Messages: 43 Registered: May 2012 Location: chennai
|
Member |
|
|
Hi Gurus,
I have a requirement and stuck to achive this in sql . could you pls help me on this.
create table :
create table dummy_tab (dat date ,total_product number , sale_count number )
/
--insert scripts
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT) values (to_date('01-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,10);
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT) values (to_date('02-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,20);
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT) values (to_date('10-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,3);
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT) values (to_date('15-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,4);
/
current table structure :
DAT TOTAL_PRODUCT SALE_COUNT
01/01/2018 147 10
02/01/2018 147 20
10/01/2018 147 3
15/01/2018 147 4
Required output :
DAT TOTAL_PRODUCT SALE_COUNT Remaing products
01/01/2018 147 10 137 <=147-10
02/01/2018 147 20 117 <=137-20
10/01/2018 147 3 114 <=117-3
15/01/2018 147 4 110 <=114-4
Thanks & Regards,
Bharathi
|
|
|
Help on running difference sql query [message #668787 is a reply to message #668786] |
Wed, 14 March 2018 07:52 |
|
bharathi89
Messages: 43 Registered: May 2012 Location: chennai
|
Member |
|
|
Hi Gurus,
I have a requirement and stuck to achive this in sql . could you pls help me on this.
create table :
create table dummy_tab (dat date ,total_product number , sale_count number )
/
--insert scripts
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT) values (to_date('01-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,10);
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT) values (to_date('02-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,20);
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT) values (to_date('10-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,3);
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT) values (to_date('15-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,4);
/
current table structure :
DAT TOTAL_PRODUCT SALE_COUNT
01/01/2018 | 147 | 10
02/01/2018 | 147 | 20
10/01/2018 | 147 | 3
15/01/2018 | 147 | 4
Required output :
DAT TOTAL_PRODUCT SALE_COUNT Remaing products
01/01/2018 | 147 | 10 | 137 | <=147-10
02/01/2018 | 147 | 20 | 117 | <=137-20
10/01/2018 | 147 | 3 | 114 | <=117-3
15/01/2018 | 147 | 4 | 110 | <=114-4
So far i have tried a query :
select dat, TOTAL_PRODUCT,SALE_COUNT,dif, SUM(dif) OVER (ORDER BY dat ROWS UNBOUNDED PRECEDING)after_rem from
(
SELECT dat, TOTAL_PRODUCT,SALE_COUNT, TOTAL_PRODUCT-SALE_COUNT dif
FROM
dummy_tab
ORDER BY
dat )
order by dat;
Thanks & Regards,
Bharathi
[Updated on: Wed, 14 March 2018 07:56] Report message to a moderator
|
|
|
Re: Help on running difference sql query [message #668788 is a reply to message #668787] |
Wed, 14 March 2018 08:06 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Would something like this do:orclx> select o.dat,o.TOTAL_PRODUCT,o.SALE_COUNT,o.total_product - (select sum(i.sale_count) from dummy_tab i where i.dat <= o.dat) from dummy_tab o;
DAT TOTAL_PRODUCT SALE_COUNT O.TOTAL_PRODUCT-(SELECTSUM(I.SALE_COUNT)FROMDUMMY_TABIWHEREI.DAT<=O.DAT)
------------------- ------------- ---------- ------------------------------------------------------------------------
2018-01-01:00:00:00 147 10 137
2018-01-02:00:00:00 147 20 117
2018-01-10:00:00:00 147 3 114
2018-01-15:00:00:00 147 4 106
2018-01-15:00:00:00 147 4 106 Next time you post, please use [code] tags. You have been asked to do this before.
--update: not quite right, I managed to insert a duplicate row.
[Updated on: Wed, 14 March 2018 08:08] Report message to a moderator
|
|
|
|
Re: Help on running difference sql query (2 merged by CM) [message #668791 is a reply to message #668790] |
Wed, 14 March 2018 09:50 |
|
bharathi89
Messages: 43 Registered: May 2012 Location: chennai
|
Member |
|
|
Hi Micheal,
Thanks a lot for the response !!!, Yes the total_product will remain same always,
i have slightly modified the table structure, added product manufacture date and values, Please find the script below and modified the your sql,
Please help to achive the same with new table structure and values , Thanks in advance.
select act_man_date manufacture_date,DAT sale_date ,TOTAL_PRODUCT total_product_manufactured,SALE_COUNT,
total_product - sum(sale_count) over (order by act_man_date,dat) remain
from DUMMY_TAB
order by act_man_date,dat
/
create table dummy_tab (dat date ,total_product number , sale_count number,act_man_date date )
/
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT,ACT_MAN_DATE) values (to_date('01-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,10,to_date('01-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS'));
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT,ACT_MAN_DATE) values (to_date('02-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,20,to_date('01-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS'));
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT,ACT_MAN_DATE) values (to_date('10-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,3,to_date('01-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS'));
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT,ACT_MAN_DATE) values (to_date('15-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),147,4,to_date('01-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS'));
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT,ACT_MAN_DATE) values (to_date('02-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),501,2,to_date('02-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS'));
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT,ACT_MAN_DATE) values (to_date('03-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),501,2,to_date('02-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS'));
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT,ACT_MAN_DATE) values (to_date('10-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),501,4,to_date('02-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS'));
Insert into DUMMY_TAB (DAT,TOTAL_PRODUCT,SALE_COUNT,ACT_MAN_DATE) values (to_date('15-JAN-18 00:00:00','DD-MON-RR HH24:MI:SS'),501,4,to_date('02-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS'));
/
Thanks & Regards,
Bharathi
[Updated on: Wed, 14 March 2018 09:57] Report message to a moderator
|
|
|
Re: Help on running difference sql query (2 merged by CM) [message #668792 is a reply to message #668791] |
Wed, 14 March 2018 11:59 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Yes the total_product will remain same always,
Who guarantees that?
You should have at least 2 tables, one of products with initial total and one with sales, of course there should a product id... and a sale_id if several sales can be happened at the same time as John mentioned.
Quote:added product manufacture date and values,
They are not in the test case you posted.
How do they impact the result? Post it.
In addition, do NOT use month names, we have not the same ones, use month numbers:
SQL> select to_date('02-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS') from dual;
select to_date('02-JAN-18 14:31:23','DD-MON-RR HH24:MI:SS') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
In the end, as John asked, format your code and result: read How to use [code] tags and make your code easier to read.
Stay your line in 80 characters width.
No more answer if post is not formatted.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 18:35:33 CDT 2024
|