Script Building Help for a Logic!

[dmohanty]

Hi All.

I am bit Struggling to build a Script for the Below Logic:

I have 2 Set of Tables as below:

Driving table (Table A):

Joining Table (Table B):

The Logic to build is :

• From Table A, for a single DOC_NUM and DOC_NUM_ID, find the SUM(RCV_QTY) -- It should be a single line for above example.
• If SUM(RCV_QTY) = ORDER_QTY (from Table B), then proceed to next step. If doesn't matches, then Step Ends. -- For above example, it should match as '19'.
• From Table A, find the MAX(POST_DATE)  --- It is 1/28/2015.
• If (MAX(POST_DATE)  -  MIN(POST_DATE) <=1 , 'YES' , 'NO') as FILL_RATE --- Here it would be more than 1 and NO.

Could someone please help in building the script for above logic?

[Anonymous]

Here is the logic:

Table:
LOAD
DOC_NUM,
DOC_NUM_ID,
sum(RCV_QTY) as SUM_RCV_QTY,
if(max(POST_DATE)-min(POST_DATE)<=1,'YES','NO') as FILL_RATE
FROM TableA
GROUP BY DOC_NUM,DOC_NUM_ID;

LEFT JOIN (Table) LOAD
DOC_NUM,
DOC_NUM_ID,
ORDER_QTY,
RCV_DATE
FROM TableB;

LEFT JOIN (Table) LOAD
DOC_NUM,
DOC_NUM_ID,
if(SUM_RCV_QTY=ORDER_QTY, 'Next Step', 'End') as "What to do next"       // up to you what to do about that...
RESIDENT Table;

[maxgro]

if you can post some data, we can try

the psudo code could be

1) tmp1: load doc_num, doc_num_id, sum(rcv_qty), min(post_date), max(post_date) resident TableA

group by  doc_num, doc_num_id;

2) join (tmp1) load * resident TableB; join is by doc_num, doc_num_id

3) now we have all the fields for the check on the same record; do a resident load of tmp1 for the 2 check

     If SUM(RCV_QTY) = ORDER_QTY

     If (MAX(POST_DATE)  -  MIN(POST_DATE) <=1 , 'YES' , 'NO')