Greetings Brainiacs, me again! I have yet another newbie question for you…

Let’s pretend that I read in 3 rows with the following information (FYI, the dates are MM/DD/YYYY format):

.........Open Date ....Service Date....Close Date
Row 1....NULL..........02/01/2008......NULL
Row 2....12/20/2007....01/08/2008......01/09/2008
Row 3....01/01/2008....12/31/2007......03/03/2008


And I want a report showing the following (FYI, the user supplies the From and To Dates as input amper variables):

Totals Report From 1/1/2008 To 3/31/2008.........................
Month.............Total Opened.....Total Serviced....Total Closed
January, 2008..... 1................1.................1...........
February, 2008....0................1.................0...........
March, 2008....... 0................0.................1...........


My question is, how do I create/generate/calculate the Month Column values to be used for totals calculations and to be displayed?

Thanks in advance for your help!
Sam

Sam,

Is this the type of idea you are looking for?:

DEFINE FILE EDUCFILE
XMONTH/M = DATE_ATTEND;
MTH_NAME/A15 = DECODE XMONTH (
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
ELSE Other
);
END

TABLE FILE EDUCFILE
COUNT
XMONTH AS 'Count'
BY XMONTH NOPRINT
BY MTH_NAME AS 'Month'
-*BY COURSE_CODE

ON TABLE SUBTOTAL
END

Sean

Sean, thanks so much for the quick reply!

This might just lead me to what I'm looking for...

I could Define a file with Month, Total1, Total2 and Total3 columns. I could then insert an initial row with a Month Column value of the &FromDate variable. I could add 1 month to that Month column value, compare the Result against the &ToDate variable, and if it's less than or equal &ToDate, I could insert another entry with Result as the Month Column value. I could repeat this logic until my Result is greater than &ToDate. That would give me a table with all necessary values of Month for my report.

Then I could have another Define that goes against my SQLServer Table rows, and, based on the value of the dates in each row, I could modify the counts in each Month's Total1, Total2 and Total3 columns in my previously defined table.

I should say that I found a Function to extract the Month Name and Year from Date for display purposes.

Am I close?

Thanks!
Sam

This message has been edited. Last edited by: SamF,

Sam,

To use the dates entered by the user, just add a WHERE clause. I have shown a month range instead of a date range, but you can use a date range for your example.

If you need all months in the range showing, regardless of whether there is any data for a given month, then yes, you will need to create a join driven by the month file.

Here's my revised sample:

-SET &FROM_MTH = '1';
-SET &TO_MTH   = '7';

DEFINE FILE EDUCFILE
XMONTH/M = DATE_ATTEND;
MTH_NAME/A15 = DECODE XMONTH (
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
ELSE Other
);
END

TABLE FILE EDUCFILE
COUNT
XMONTH AS 'Count'
BY XMONTH NOPRINT
BY MTH_NAME AS 'Month'
WHERE XMONTH FROM &FROM_MTH TO &TO_MTH 
-*BY COURSE_CODE

ON TABLE SUBTOTAL
END
  

Sean

Hey Sean, I understand! Thanks! Now one more quick question... do you know how I would sum by Month and Year instead of just Month since they may request a date span greater than 1 year?

Thank you very much!!
Sam

Simplest way would be to define YMONTH as a YYM and use that as your sort field.

DEFINE FILE EMPLOYEE
HIREYYM/trMYY =HIRE_DATE;
END
TABLE FILE EMPLOYEE
PRINT
EMP_ID
LAST_NAME
FIRST_NAME
HIRE_DATE
BY HIREYYM
END

Sam,

Something like this??:

-* Get the year/month from the user entered dates
-SET &FROM_YRMTH = '8201';
-SET &TO_YRMTH = '8311';

DEFINE FILE EDUCFILE
XMONTH/M = DATE_ATTEND;
XYEAR/YYYY = DATE_ATTEND;
YEARMONTH/A4 = EDIT(DATE_ATTEND, '9999');
MTH_NAME/A15 = DECODE XMONTH (
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
ELSE Other
);
END

TABLE FILE EDUCFILE
COUNT
XMONTH AS 'Count'
BY XYEAR AS 'Year'
BY XMONTH NOPRINT
BY MTH_NAME AS 'Month'
-*WHERE XMONTH FROM &FROM_MTH TO &TO_MTH 
WHERE YEARMONTH FROM '&FROM_YRMTH' TO '&TO_YRMTH' 
-*BY COURSE_CODE

ON TABLE SUBTOTAL
END

Sean

Hi

Another way to do this, an extension to what Darin posted would be

-SET &FROM_YRMTH = '8201';
-SET &TO_YRMTH = '8311';

DEFINE FILE EDUCFILE
DATEYYM/YM = DATE_ATTEND;
END

TABLE FILE EDUCFILE
COUNT
DATEYYM AS 'Count'
BY DATEYYM

-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows      = 'ROWS' ;

-REPEAT DATELOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &YrMth = AYM(&FROM_YRMTH, &Cntr , 'I4') ;
-SET &DtDesc= CHGDAT('YM', 'YXM', &YrMth , 'A17');

 &Rows &YrMth AS '&DtDesc'

-SET &Rows  = 'OVER' ;
-DATELOOP

WHERE DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH 

ON TABLE SUBTOTAL
END

This code will fill in missing months, the Where clause isn't realy neede as well because the Rows statement will remove the unwanted data.

Regards

Slick! Couple of items. This is negligible against EDUCFILE, but I try to avoid a selection on a defined field WHENEVER POSSIBLE. The criteria cannot be passed to the DBMS when you do this. So maybe ask for the complete date in the &FROM and &TO, strip off the day component using edit for use with your loops, and use the real field DATE_ATTENDED in your WHERE clause with the full dates entered by the user.

Alternatively, the user could enter year/month dates, you manually calculate beginning and ending dates using the fabulous WF DATEMOV date routines and use your input dates in the loop and calculated dates with the real field in the WHERE.

And throw in a SET NODATA=0 to make it look pretty....

Greetings you amazing people! Sorry for the delay in response. I was called off on another production issue. I'm back now and am going to try to put into practice your amazing suggestions. Stay tuned...

One quick question as I continue to piece this together... I slightly altered the code to fit my project and it worked like a charm with the same dates as in your fine example
-SET &FROM_YRMTH = '8201';
-SET &TO_YRMTH = '8311';

When I changed the dates to
-SET &FROM_YRMTH = '0801';
-SET &TO_YRMTH = '1101';
for 2008/01 - 2011/01, it returned 0 lines. It should've found some. What did I do wrong?

Thanks,
Sam

What format are the dates in the database you are comparing to? You have to put your & variables in the same format or you have to define the database fields to your format.


Sam, you will learn this language yet, you are doing a good job of researching and trying things for yourself!

The problem, as Pat says, is that the date must match the format of dates in your database. The EDUCFILE was created before the Y2K thing came up and the dates are in the pre-2000 century, while yours are year 2000+. If your date years are really only 2-digit, you will want to check out the DEFCENT and YRTHRESH settings.

Greetings,

Yall will never know how much your words of encouragement and technical guidance are helping me! I'm still stumbling through the 'Land Of Date Reformats' but I'm determined!!! Stay tuned...

Sincerely and with much gratitude,
Sam

Ok, I am trying to change my input dates to allow the user to type in a human-friendly date and translate it to a database-friendly date and I'm running into some problems. Can yall tell me what I am missing in the following code? I found something online which made me think I needed to move it inside a DEFINE but it got the same error.

-* File my.fex
-SET &FROMDATE = 01/01/2008;
-SET &TODATE = 01/01/2011;
FROMDATE_CONVERTED/HYYMDs=HINPUT(10, '&FROMDATE', 10, FROMDATE_CONVERTED);
TODATE_CONVERTED/HYYMDs=HINPUT(10, '&TODATE', 10, TODATE_CONVERTED);
EXTR_DATE_FROM_FROMDATETIME/YYMD=HDATE(FROMDATE_CONVERTED, 'YYMD');
EXTR_DATE_FROM_TODATETIME/YYMD=HDATE(TODATE_CONVERTED, 'YYMD');
EXTR_MMYY_FR_FROMDT/MYY=DATECVT(EXTR_DATE_FROM_FROMDATETIME, 'YYMD', 'MYY');
EXTR_MMYY_FR_TODT/MYY=DATECVT(EXTR_DATE_FROM_TODATETIME, 'YYMD', 'MYY');
-SET &FROM_YRMTH/YM = HDATE(EXTR_MMYY_FR_FROMDT, 'YYMD');
-SET &TO_YRMTH/YM = HDATE(EXTR_MMYY_FR_TODT, 'YYMD');
.
Gets this error...
.

0 ERROR AT OR NEAR LINE 10 IN PROCEDURE my.fex FOCEXEC *
(FOC318) A FIELD FORMAT IS NOT ALLOWED IN -SET: &FROM_YRMTH/YM

I'm getting closer thanks to yall!

You've got the right idea. You didn't specify what format the DATE_ATTENDED field is so I'm assuming it just a YYMD.

What you need to to do is all dialogue manager and requires a -SET for each line. Dialogue manager only deals with strings and numbers. So most of your code isn't necessary. Additionally, you can't specify a format in a -SET (and thus the error). Each of the functions you are using also had a dialogue manager equivalent - the most common difference being that the last parameter would be an output format enclosed in single quotes like 'A10'. There are a number of differences, however, and you should go back to you functions and examine them carefully. Last, the Hxxx functions are for converting DATE-TIME values which you are not dealing with. A little before those functions in the functions manual are the functions you need to use. Got an emergency - I'll check back later

The lines with the dashes, -SET, are amper variables and you cannot have a format.

All the lines without a dash belong in a define or need to be made amper variables. But since you are using datetime subroutines, define would be better.

However, if you are going to use these dates in a where clause against a data base, then I would recommend continuing down the amper variable path as that is more efficient than comparing a defined field against a data base field.

Do your dates actually have to be datetime stamps?

quote:

-SET &FROMDATE = 01/01/2008;
-SET &TODATE = 01/01/2011;
FROMDATE_CONVERTED/HYYMDs=HINPUT(10, '&FROMDATE', 10, FROMDATE_CONVERTED);
TODATE_CONVERTED/HYYMDs=HINPUT(10, '&TODATE', 10, TODATE_CONVERTED);
EXTR_DATE_FROM_FROMDATETIME/YYMD=HDATE(FROMDATE_CONVERTED, 'YYMD');
EXTR_DATE_FROM_TODATETIME/YYMD=HDATE(TODATE_CONVERTED, 'YYMD');
EXTR_MMYY_FR_FROMDT/MYY=DATECVT(EXTR_DATE_FROM_FROMDATETIME, 'YYMD', 'MYY');
EXTR_MMYY_FR_TODT/MYY=DATECVT(EXTR_DATE_FROM_TODATETIME, 'YYMD', 'MYY');
-SET &FROM_YRMTH/YM = HDATE(EXTR_MMYY_FR_FROMDT, 'YYMD');
-SET &TO_YRMTH/YM = HDATE(EXTR_MMYY_FR_TODT, 'YYMD');

You are mixing statements here. As Ginny said most of this code is unnecessary. If you are comparing against a database field where the format is HYYMDS and the user input is MM/DD/YYYY, I would do something like:

-*** THIS PUTS YOUR DATE IN YYMD FORMAT
-SET &FROMDATE1=EDIT('&FROMDATE.EVAL','$$$$$$9999')|EDIT('&FROMDATE.EVAL','99$99');
-SET &TODATE1=EDIT('&TODATE.EVAL','$$$$$$9999')|EDIT('&TODATE.EVAL','99$99');

-** THIS ADDS 1 DAY TO YOUR TODATE
-SET &TODATE2=AYMD(&TODATE1,1,'I8YYMD');

-*** THIS SETS THE WHERE STATEMENTS AND CAN APPEAR DIRECTLY IN YOUR CODE AND GL_DATE IS THE COMPARE FIELD IN
-* YOUR DATABASE

-SET &WHRFR='WHERE GL_DATE GE &FROMDATE1';
-SET &WHRTO='WHERE GL_DATE LT &TODATE2';

Greetings! Sorry for the delay - I've been busy with a conversion. Pat, I've been trying to implement your suggestion but I'm quite stuck on the REPEAT DATELOOP part. Nothing I've tried works for the &MnthCount calculation which is used in deriving the different 'Month Buckets' for the report. Here's my latest attempt which
1. is returning a 0 and
2. is probably not the best way to code what I'm trying to get at (my apologies in advance!):

DEFINE FILE BLAH
...
-*** THIS REFORMATS THE TIMESTAMP INTO YYMD FOR HDIFF
HDIFF_FRDT/YYMD = HDATE(&FROMDATE_TS, 'YYMD');
-SET &HDIFF_FROMDATE = HDIFF_FRDT;
HDIFF_TODT/YYMD = HDATE(&TODATE_TS, 'YYMD');
-SET &HDIFF_TODATE = HDIFF_TODT;
-SET &MNTHCOUNT = HDIFF(HDIFF_TODT, HDIFF_FRDT, 'MONTH', 'D12.2');
...

I'm pretty frustrated. I think these dates are going to do me in! I surely do appreciate your time, help and patience!
Sam

You are still mixing Dialogue Manager and defines. Anything in -SET command cannot reference a field, virtual or real. What is the format of the DATE_ATTEND field to which you are comparing?

Also, the Hdate (HDIFF, HDATE, etc.) commands are ONLY used for date-time stamp fields. There are other functions (DATEADD, DATEDIF) that can be used for regular date fields.

Going back to the 3/13-3/14 posts it seems like you were a lot closer. Let us know the formats of your actual database fields and we'll help you piece this together. And yes, dates are one of the most frustrating things to work with. There is a little booklet available to help you specifically with this topic. "1001 Ways to Ways to Work with Dates in WebFOCUS" or something like that.

Hey Darin, yes I felt a lot closer back around 3/13-3/14!! I actually found the 1,001 document and am petitioning the powers that be for a copy of it. The page I REALLY wanted to see (Page 193) wasn't in the sample copy I downloaded... RATS!

Anyway, thanks for the info regarding Hdate & other functions. I'll add this info to my ever-expanding "Date Notes"!

My database date formats are HYYMDs Date Time (Timestamp) with '/' Separator. Example: 2004/08/21 02:05:27.123

Thanks so much!!

Sincerely,
Sam

So the user inputs a beginning (&FROM_YRMTH) and ending (&TO_YRMTH) year/month in YYYYMM format. You can create the beginning string without any more info.

-SET &BEG_DT=EDIT(&FROM_YRMTH,'9999/99')|'/01 00:00:00.100';

doesn't matter that it's not a date. It's just a literal you are going to use for comparison. So if the user input 200803 you get 2008/03/01 00:00:00.100 - midnight of the first day of the month.

Next step is to calculate the ending date - a little more tricky.
-SET &END_DT1=CHGDAT('YYM','YYMD',&TO_YRMTH,'A8');

CHGDAT function throws in the last day of the month going from a short date YYM to a long date YYMD. Then we format it to the layout of your date:

-SET &END_DT=EDIT(&END_DT1,'9999/99/99') |' 23:59:59.999';
and in your where

WHERE DATE_ATTEND GE DT(&BEG_DT);
WHERE DATE_ATTEND LE DT(&END_DT);

the DT prefix is necessary to tell them that the character string is actual a date-time stamp.

Hope this works for you.

Hey Darin, actually the user is inputting From and To Dates in MM/DD/YYYY format. I'll see if I can figure out how to use your very helpful samples to convert them.

Thanks!
Sam

Even easier - no calculation necessary, just proper formatting to match your database field. Just use CHGDAT from MDYY to YYMD. Also remember that we are dealing with strings here - not actual dates - so you have to handle the slashes manually.

My apologies!

I DO have to calculate end date for each month in the DATELOOP. I'll use your nifty sample to do so. Well, I either have to do that

----OR----

In the WHERE statement, compare only the YYYY/MM part of the timestamp in the Database to the YYYY/MM part of the current month in the DATELOOP.

But I think it was mentioned above that processing would be slow if I tried something like that in the WHERE statement so I shall stick to the plan above the ----OR----!

quote:

In the WHERE statement, compare only the YYYY/MM part of the timestamp in the Database to the YYYY/MM part of the current month in the DATELOOP.

Sam, I recommend AGAINST that especially if the data base is relational. What will happen is that all of the rows will come back to WebFOCUS and WebFOCUS will do the WHERE. Not good. It it best to do a full range check with begin and end dates as Darin has showed you.

You don't need to calculate the end dates because you are sorting on year and month - no need to worry about days at all. Whether it's april 1, 17 or 30, it would still go in the April bucket.

I would worry, however that if a user input 04/01/2008 as the end date, it would show up in the report only as "April 2008." Without any indication that April 2008 only includes one day, your report could be misleading. If you include the dates entered by the user in a heading/footing, that would be OK.

Ginny, I've followed your sound advice. Thanks!

Darin, I will definitely put the From and To Date in a heading so the customer will know why a total might look misleading.

I'm getting close now, but, I still have one question...

In my REPEAT DATELOOP, I calculate the ToDate using the CHGDATE function (thanks, Darin!) Once it's calculated, I want to compare it against the To Date that the customer entered. If the calculated ToDate is greater than the customer ToDate, I want to use the customer ToDate, otherwise, I want to use the calculated ToDate.

First I tried just asking if one was greater than (GT) the other but I got the error, THE FIELDNAME IS NOT RECOGNIZED: GT'.

Then I tried using:

Line 34...-SET &DIFF = DATEDIF(&CALC_TO_DT, &CUST_TO_YYMD, 'D');
Line 35...IF &DIFF GT 0 THEN ...

but I got an error:

0 ERROR AT OR NEAR LINE 35 IN PROCEDURE lastwkcoFOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: 20081231


Am I on the right track? If so, what did I do wrong? If not, is there some other slick way to accomplish this?

Hmmm... I guess I had 3 questions!

Thanks,
Sam

quote:

In my REPEAT DATELOOP, I calculate the ToDate using the CHGDATE function

I thought you were calculating the ToDate based on the customer input date. I guess not - so I'm not quite sure what you're looking for. Maybe post your current code again.
I'm guessing that both are your errors are just syntax problems. Back to you for round 12

Oh dear! I'm sorry I've made such a mess of this! Though I think FINALLY I've figured it out! The LOOP uses month and year only when coming up with the totals for each month, and the WHERE clause takes care of the CustomerToDate limit. Ok, I'm off to try again! Stay tuned!!!