CODEWITHSUNDEEP
sqloracle-database
SeeWe
SeeWe

Reputation: 45

fill missing data Oracle

Trust me I've tried my luck finding for this problem. I've had MYSQL & SQLServer solutions and not Oracle and not to specific roll-up I require. Since Cross Apply is not available in the version of Oracle I'm using, I hit a road block.

Problem is simple for many of you.

Start of the universe for me is rolling 13 months.

I've a table which has

CREATE TABLE TBLTESTAUM (
ORDER_NO NUMBER(10,0) NOT NULL ENABLE,
RECORD_DATE DATE,
Order_SEQUENCE NUMBER(5,0) NOT NULL ENABLE,
CLASS NUMBER(3,0));

INSERT INTO TBLTESTAUM VALUES (1234, '29-Aug-2015', 34, 459);
INSERT INTO TBLTESTAUM VALUES (1234, '20-Jun-2016', 35, 877);
INSERT INTO TBLTESTAUM VALUES (1234, '20-Jun-2016', 37, 877);
INSERT INTO TBLTESTAUM VALUES (1234, '02-Jul-2016', 39, 122);
INSERT INTO TBLTESTAUM VALUES (1234, '28-Jul-2016', 40, 122);
INSERT INTO TBLTESTAUM VALUES (1234, '31-Jul-2016', 41, 311);
INSERT INTO TBLTESTAUM VALUES (1234, '10-Aug-2016', 42, 311);
INSERT INTO TBLTESTAUM VALUES (1234, '18-Aug-2016', 44, 110);
INSERT INTO TBLTESTAUM VALUES (1234, '20-Aug-2016', 45, 110);

Please Note :

  1. the 20/Jul/2015 in first insert.
  2. the Seq field may or may not have each value. It may be missing some. If you want to use it.
  3. Not Nullable for Sequence is ignore able.

So rolling 13 months gives me 22/07/2015, as of today.

I want this broken down in weekly "CLASS" situations and if nothing exists for that week then the last applied CLASS. If nothing last, i.e. it is the first instance, then next applied CLASS. Till the CLASS changes.

Output to be -

Order Num   WeekDate    CLASS
123         27-Jul-15   459
123         3-Aug-15    459
123         10-Aug-15   459
123         17-Aug-15   459
123         24-Aug-15   459
123         31-Aug-15   459
123         7-Sep-15    459
Dates and Order Num to continue till next match in TBLTESTAUM is found
123         20-Jun-16   877
123         27-Jun-16   122
123         4-Jul-16    122
123         11-Jul-16   122
123         25-Jul-16   311
123         1-Aug-16    311
123         8-Aug-16    311
123         15-Aug-16   110
123         22-Aug-16   110

There are multiple order num. But I hope your code will fit one and all. Please note

it needs to roll up to the Weekly Status.
There is a requirement to do Monthly Status. But hopefully I can figure that out from your code.

Thank you in advance.

PS: I will not be available to look at your responses for next 10hrs. Man got to sleep. But really appreciate any time you spend on this.

Upvotes: 3

Views: 208

Answers (1)

user5683823
user5683823

Reputation:

The query below may do what you need. If it doesn't, please explain what is different compared to your requirement. (Please answer the questions I asked in my Comment to your question as well).

In this solution, I generate the needed days (first as Sundays because you want to capture what happened during the "current week", then I change them to Mondays in the final, outer query). I also generate the order_no's from the table you provided, but in a meaningful real-life situation that shouldn't be necessary; you should have an "orders" table with the same order_no as the primary key, and my CTE "o" should pull order numbers from that table, not from tbltestaum.

Good luck!

with
     w ( weekdate ) as (
       select trunc(sysdate, 'iw') - 7 * (level - 1) + 6  -- This will generate Sundays
       from   dual
       connect by level <= 1 + 
                   ( trunc(sysdate, 'iw') - trunc(add_months(sysdate, -13), 'iw') ) / 7
     ), 
     o ( order_no ) as (
       select distinct order_no from tbltestaum
     ),
     prep ( order_no, dt, order_sequence, class ) as (
       select order_no, record_date, order_sequence, class
         from tbltestaum
       union all
       select order_no, weekdate, null, null
         from w cross join o
     ),
     z ( order_no, dt, order_sequence, class ) as (
       select order_no, dt, order_sequence,
              nvl( last_value(class ignore nulls) over (partition by order_no 
                                                        order by dt, order_sequence),
                  first_value(class ignore nulls) over (partition by order_no
                                                        order by dt, order_sequence
                             rows between unbounded preceding and unbounded following ) )
       from   prep
     )
select order_no, to_char(dt - 6, 'dd-Mon-yy') as weekdate, class
from   z
where  order_sequence is null
;

Upvotes: 1

Related Questions