Morning All,

We have a strange problem, we are looking at our inventory changes today vs. yesterday, we have a .fex that will get us the two dates to a hold file, then insert that in list as a where statement on our second query.

This limits our results from a few million record table, but the data is stored like this...

JOB DATE	WHSE	Item	NET QTY1
105361	10099990007	1012776	2354
105361	10099990009	1538937	2341
105362	10099990002	1005333	10
105362	10099990002	1274486	3

Hold file to get yesterday/today

  
TABLE FILE F568304
BY  HIGHEST 2 JOBDATE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SAVE AS 'HOLD1' FORMAT ALPHA
END

We want to subtract by the different dates in the rows, this example just gives us the two values for the two days (we tried a few things, but didn't get it to work):

  

TABLE FILE F568304
SUM
     NetChange
BY LOWEST  JOBDATE
WHERE ( Q2DXJ IN FILE HOLD1 );

Appreciate any help, once we have a working example we can do this anywhere

*Edits- Cleaned up the example.

Thank you,

JC

This message has been edited. Last edited by: <Kathryn Henning>,

In such cases we use MATCH FILE on the results for today and those for yesterday.

MATCH
FILE F568304
SUM NetChange AS NetChangeToday
WHERE JOBDATE EQ &Today;
RUN
FILE F568304
SUM NetChange AS NetChangeYesterday
WHERE JOBDATE EQ &Yesterday;
AFTER MATCH HOLD OLD-OR-NEW
END

TABLE FILE HOLD
SUM Diff/D12.2 = NetChangeToday - NetChangeYesterday;
END

We would do a define or compute
/*
DEFINE FILE F568304
NETTODAY/D20.2=IF JOBDATE EQ &TODAY THEN Netchange ELSE 0;
NETYESTR/D20.2=IF JOBDATE EQ &YESTERDAY THEN Netchange ELSE 0;
END
TABLE FILE F568304
SUM NETTODAY NETYESTR
COMPUTE NETCHANGE/D20.2= NETYESTR - NETTODAY;

WHERE JOBDATE EQ &TODAY OR &YESTERDAY
END

If the 2 dates in HOLD1 changes everyday. How do I get them to &values.

Would I do a -READ? I've only used that in tables with 1 record, then used that one record as a where statement.

Appreciate the feedback!

JC

Try something like this
/*
-SET &BASEDATE = &YYMD ;
-SET &TODAY = DATECVT (&BASEDATE,'I8YYMD','YYMD');
-SET &YESTERDAY = DATEADD(&CURDATE, 'D', -1);

I've done something like that, but not like a century julian.

CYDDD (P7)

Would it be the same type of -set values?

As long as the dates you are comparing are in the same format, I don't see why not. Just try it and see what happens.

How do I populate &CURDATE? It's prompting for it.

i miised a change. It should be &today.

Code:

  
-SET &BASEDATE = &YYMD ;
-SET &TODAY = DATECVT (&BASEDATE,'I8YYMD','YYMD');
-SET &YESTERDAY = DATEADD(&TODAY, 'D', -1);

DEFINE FILE F568304
NETTODAY/D20.2=IF JOBDATEEQ &TODAY THEN NETCHANGE ELSE 0;
NETYESTR/D20.2=IF JOBDATEEQ &YESTERDAY THEN NETCHANGE ELSE 0;
END
TABLE FILE F568304
SUM NETTODAY NETYESTR
COMPUTE NETCHANGE/D20.2= NETYESTR - NETTODAY;
WHERE JOBDATEEQ &TODAY OR &YESTERDAY
END

FOCUSEXE:

  

 SET TRACEOFF = ALL
 SET TRACEON = STMTRACE//CLIENT
 SET TRACEON = STMTRACE/2/CLIENT
 SET TRACEUSER = ON
 SET XRETRIEVAL = OFF
 -SET &BASEDATE = 20130419 ;
 -SET &TODAY = DATECVT (20130419,'I8YYMD','YYMD');
 -SET &YESTERDAY = DATEADD(41017, 'D', -1);
 DEFINE FILE F568304
 NETTODAY/D20.2=IF JOBDATEEQ 41017 THEN NETCHANGE ELSE 0;
 NETYESTR/D20.2=IF JOBDATEEQ 41016 THEN NETCHANGE ELSE 0;
 END
 TABLE FILE F568304
 SUM NETTODAY NETYESTR
 COMPUTE NETCHANGE/D20.2= NETYESTR - NETTODAY;
 WHERE JOBDATEEQ 41017 OR 41016
 END

SQL:

  
 14.57.57 AE    SELECT
 14.57.57 AE    SUM((CASE (T1."NETCHANGE")  WHEN 41017 THEN T1."NETCHANGE" ELSE 0
 14.57.57 AE   END)),
 14.57.57 AE    SUM((CASE (T1."NETCHANGE")  WHEN 41016 THEN T1."NETCHANGE" ELSE 0
 14.57.57 AE   END))
 14.57.57 AE    FROM
 14.57.57 AE   HSIQDTA71/F568304 T1
 14.57.57 AE    WHERE
 14.57.57 AE   (T1."JOBDATE" IN(41017, 41016))
 14.57.57 AE    FOR FETCH ONLY;

Edits- Made field names more freindly.

This message has been edited. Last edited by: John C.,

John,

This works?

The date being sent in SQL doesn't match the CYDDD, so returns 0 records.

Tried this:

Probably not the way to go.. but..

  
-SET &BASEDATE = JULDAT(&YYMD, 'I6') + 100000;
-SET &BASEDATEYES = JULDAT(&YYMD, 'I6') + 100000 - 1;
-TYPE &BASEDATE ... &BASEDATEYES
-EXIT

Run

  
 -SET &BASEDATE = JULDAT(20130419, 'I6') + 100000;
 -SET &BASEDATEYES = JULDAT(20130419, 'I6') + 100000 - 1;
 -TYPE 113109 ... 113108
 113109 ... 113108
 -EXIT

quote:

-SET &BASEDATE = JULDAT(&YYMD, 'I6') + 100000;
-SET &BASEDATEYES = JULDAT(&YYMD, 'I6') + 100000 - 1;
-TYPE &BASEDATE ... &BASEDATEYES
-EXIT

Anyone know a good way to look at the weekend? If monday I need to go to friday's date.

Do you mean Julian format? if you do, you can convert from Julian to Gregorian using a WebFOCUS Fucntion.