Saturday, January 28, 2017

SQL101: Using Oracle Analytical function - range between unbounded preceding and 1 preceding

In SQL101 series, I will post some simple SQL related techniques. Most of them are posted in OTN time to time by me.
This SQL101 is related to Using Oracle Analytical function with range between unbounded preceding and 1 preceding, the problem was posted in OTN.
Test table and data setup:
First, I have used the following to create the test table and insert the sample data.

--Test table
create table daily_tr_tab
  (
    invid    number unique, --- though this is also not used in my query. if you need any unique column to give me a better solution.
    dates    date,
    purchase number,
    sales    number
  );
  

--Test data setup 
REM INSERTING into daily_tr_tab
SET DEFINE OFF;
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (1,to_date('22-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),50,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (2,to_date('23-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),20,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (3,to_date('24-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),10,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (4,to_date('25-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),60,45);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (5,to_date('26-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),40,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (6,to_date('27-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),15,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (7,to_date('28-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),25,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (8,to_date('29-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),15,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (9,to_date('30-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),30,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (10,to_date('31-DEC-15 00:00:00','DD-MON-RR hh24:mi:ss'),20,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (11,to_date('01-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (12,to_date('02-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (13,to_date('03-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),25,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (14,to_date('04-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,25);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (15,to_date('05-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),15,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (16,to_date('06-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,15);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (17,to_date('07-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),25,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (18,to_date('08-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (19,to_date('09-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,30);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (20,to_date('10-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),20,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (21,to_date('11-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),30,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (22,to_date('12-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),40,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (23,to_date('13-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),50,20);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (24,to_date('14-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),12,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (25,to_date('15-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),13,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (26,to_date('16-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),14,10);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (27,to_date('17-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),24,5);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (28,to_date('18-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),34,0);
Insert into daily_tr_tab (INVID,DATES,PURCHASE,SALES) values (29,to_date('19-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),35,25);
insert into daily_tr_tab (invid,dates,purchase,sales) values (30,to_date('20-JAN-16 00:00:00','DD-MON-RR hh24:mi:ss'),40,10);
The output needed:
  1. DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY  
  2. --------- ----------- ---------- ---------- -----------  
  3. 01-JAN-16         100         20         10         110  
  4. 02-JAN-16         110         30         25         115  
  5. 03-JAN-16         115         25          0         140  
  6. 04-JAN-16         140         35         25         150  
  7. 05-JAN-16         150         15         10         155  
  8. 06-JAN-16         155         20         15         160  
  9. 07-JAN-16         160         25         10         175  
  10. 08-JAN-16         175         30         20         185  
  11. 09-JAN-16         185         35         30         190  
  12. 10-JAN-16         190         20         20         190  
  13. 11-JAN-16         190         30          5         215  
 Opening quantity in a date is total of previous purchase minus total sales. The closing balance is opening quantity of that date minus the sale.

The solution is:
 
SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf

  1  select *
  2  from
  3    (select dates,
  4        sum(purchase) over(order by dates range between unbounded preceding and 1 preceding )
  5      - sum(sales) over(order by dates range between unbounded preceding and 1 preceding) opening_qty ,
  6      purchase,
  7      sales ,
  8        sum(purchase) over(order by dates range between unbounded preceding and 1 preceding)
  9      - sum(sales) over(order by dates range between unbounded preceding and 1 preceding) + purchase - sales closing_qty
 10    from daily_tr_tab
 11    )
 12  where dates >= to_date('01/01/16', 'dd/mm/rr')
 13* order by dates
SCOTT@orclSB 28-JAN-17> /

DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY
--------- ----------- ---------- ---------- -----------
01-JAN-16         100         20         10         110
02-JAN-16         110         30         25         115
03-JAN-16         115         25          0         140
04-JAN-16         140         35         25         150
05-JAN-16         150         15         10         155
06-JAN-16         155         20         15         160
07-JAN-16         160         25         10         175
08-JAN-16         175         30         20         185
09-JAN-16         185         35         30         190
10-JAN-16         190         20         20         190
11-JAN-16         190         30          5         215

DATES     OPENING_QTY   PURCHASE      SALES CLOSING_QTY
--------- ----------- ---------- ---------- -----------
12-JAN-16         215         40          0         255
13-JAN-16         255         50         20         285
14-JAN-16         285         12         10         287
15-JAN-16         287         13         10         290
16-JAN-16         290         14         10         294
17-JAN-16         294         24          5         313
18-JAN-16         313         34          0         347
19-JAN-16         347         35         25         357
20-JAN-16         357         40         10         387

20 rows selected.

SCOTT@orclSB 28-JAN-17>
Posted by at
Labels: , , ,

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )