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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8458
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 #668790 is a reply to message #668786] Wed, 14 March 2018 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select DAT,TOTAL_PRODUCT,SALE_COUNT,
  2         total_product - sum(sale_count) over (order by dat) remain
  3  from DUMMY_TAB
  4  order by dat
  5  /
DAT         TOTAL_PRODUCT SALE_COUNT     REMAIN
----------- ------------- ---------- ----------
01-JAN-2018           147         10        137
02-JAN-2018           147         20        117
10-JAN-2018           147          3        114
15-JAN-2018           147          4        110
Now the question is: what if data are inconsistent and TOTAL_PRODUCT is not the same one in all rows?
Other way to say it: the model is not correct.

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
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.

Re: Help on running difference sql query (2 merged by CM) [message #668794 is a reply to message #668792] Wed, 14 March 2018 21:21 Go to previous message
bharathi89
Messages: 43
Registered: May 2012
Location: chennai
Member
Hi Michel/ Jhon,

Aplogies not for using tags and thanks a lot for spending time on my request !!!

i have modified the above query and achieved the expected result and posting here
 SELECT    act_man_date, dat, total_product, sale_count
,         total_product - SUM (sale_count) OVER ( PARTITION BY  act_man_date
                                                  ORDER BY      dat
                                                )
                            AS remaining_products
FROM      dummy_tab
ORDER BY  act_man_date, dat;

Thanks a lot all !!!!
*BlackSwan added missing {code} tags.
Please do so yourself in the future.
How to use {code} tags and make your code easier to read

[Updated on: Wed, 14 March 2018 22:20] by Moderator

Report message to a moderator

Previous Topic: How Can I Execute Table Name in Order without using ORDER BY Clayuse
Next Topic: How To Count Total number of rows deleted by DELETE and reset counter after some condition
Goto Forum:
  


Current Time: Sun Jan 24 01:33:53 CST 2021