I am faceing this problem. I am stuck at the first step actually:

I have two fields:
Weekend & lastbilldate
Both of field format is datetime. I am trying to convert datetime to date. I was able to convert in my metadata. Now i want to use

  
WHERE lastbilldate = MAX.weekenddate - 21 days
I looked at 1001 ways to work with dates but nothing seems to be working. 

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

A multi-verb request might work.

TABLE FILE <filename>
 SUM COMPUTE compare_date/HYYMD = HADD(MAX.weekenddate,'DAY',-21,8,'HYYMD'); NOPRINT
 PRINT <column names>
 WHERE TOTAL lastbilldate EQ compare_date ;
END

Thanks for the help. It seemed like it was working. However, when i did QA with SQL query, I got different numbers. Somehow comparison is not working. Its looking for value in the quotes and since its the field name and not variable I cant use quotes around the field name. Any idea how can i use two fields in comparison? For example,
WHERE LASTBILLDATE LE compare_date;

There should not be any quotes around the field name. WHERE TOTAL lastbilldate EQ compare_date is a valid clause. Don't forget the TOTAL keyword in the WHERE clause. Remove the NOPRINT on the COMPUTE statement so you can see the value calculated for compare_date. Turn on SQL traces so you can see the SQL query generated by the WebFOCUS code.

Dan,
Thanks again for helping out. I agree with you that it is a valid clause. However, I am getting two different results when I run SQL code for example:

  
select schoolid, COUNT(*) from stage_ChildDetail_POS 
WHERE weekendingdate >= '2010/09/24' AND (LastBillDate < '2010/09/03') 
Group by schoolid 
HERE is FOCUS CODE:
JOIN
 INNER STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.SCHOOLID IN
STAGE_CHILDDETAIL_POS TO UNIQUE DIM_SCHOOL.DIM_SCHOOL.SCHOOLID IN DIM_SCHOOL
 TAG J0 AS J0
 END
TABLE FILE STAGE_CHILDDETAIL_POS
SUM
     COMPUTE compare_date/HYYMD = HADD(MAX.WEEKENDINGDATE, 'DAY', -21, 8, 'HYYMD'); NOPRINT

     'CNT.STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.CHILDLASTNAME'
 AS 'COUNT,STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.CHILDLASTNAME'
 -* Highest 1 weekend is 9/24/2010 and compare value 9/24 - 21 = 09/03
BY HIGHEST 1 'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.WEEKENDINGDATE'
BY 'STAGE_CHILDDETAIL_POS.STAGE_CHILDDETAIL_POS.SCHOOLID' AS 'School ID'
WHERE SCHOOL_STATUS IN ('I','O');
WHERE TOTAL LASTBILLDATE LE compare_date

Or is there a way to get last fridays date and compare against becasue my MAX.weekend will alwayse be last friday.

Okay I am able to figure it out using the LASTFRIDAY as MAX weekending date. So I used last fridays date

 -SET &adj   = DECODE &dowk( 'SUN' 2 'MON' 3 'TUE' 4 'WED' 5 'THU' 6 'FRI' 0 'SAT' 1);
-SET &adj       = (-1) * &adj ;
-SET &LASTDAY   = AYMD( &YYMD, &adj , 'I8YYMD');
-SET &NEEDED = AYMD(&LASTDAY, -21, 'I8YYMD'); 

AND I defined my LASTBILLDATE as
NEWDATE = HDATE(lASTBILLDATE, 'YYMD')
 and
SUM
CNT.FIELD
BY School
BY HIGHEST 1 WEEKENDINGFIELD
-* Since I couldnt do date LE, or GE, or EQ on DATE time to YYMD formate. Is this the best way to do it? It works though.
WHERE NEWDATE LE &NEEDED
 

I thank you DAN for helping me. Looking at your example gave me idea of how to minus dates. One of your PAST posts gave me idea about how to get DOW. Thanks!