Hi everyone!
I'm trying to write a report that will take a user entered date range, grab all the corresponding rows that match the subject but only show the highest row if the highest row matches the date range entered. If the date range could corespond to anther row and there is a higher one the subject should not show at all.

Example:
TABLE FILE CARS
PRINT
NAME AS 'Buyer Name'
MAKE AS 'Car Make'
MODEL AS 'Car Model'
DATE AS 'Purchase Date'
BY DEALSHIP NOPRINT
BY SALESPERSON NOPRINT
BY CALENDARYR NOPRINT
BY NAME NOPRINT

WHERE (STARTDATE GE DT(date1) AND ENDDATE LE DT(date2) AND ENDDATE GE DT(date1)) OR (ENDDATE LE DT(date2) AND ENDDATE GE DT(date1));

Assuming this date range,
date1 = 1/1/2010
date2 = 1/1/2013
I'd get something like this:

_____________
HONDA DEALERSHIP
2010
BOB SALESMAN
MARY BUYER
HONDA | CIVIC | 1/1/2010

HONDA DEALERSHIP
2012
BOB SALESMAN
MARY BUYER
HONDA | ACCORD| 1/1/2012

HONDA DEALERSHIP
2013
BOB SALESMAN
MARY BUYER
HONDA | CIVIC | 1/1/2013
______________

But we only want to show the most recent (the highest) row and disregard everything else no matter what the user enters. Like a "Show me the most recent car purchase from every buyer during whatever date range I put in there" Report. The cherry on top is if there is another row AFTER the end date (like MARY Bought a car 4/5/2013, well after end date 1/1/2013) we don't want anything to show at all in the report. That is where I'm really struggling.

How do we grab all rows for a certain 'thing' then out of those grab the highest row and, finally, determine whether or not we want to show it?

Thank you so much,
Shawna

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

You have in fact two reports
First select the most recent sold car and hold that record then do the second selection
Combine the two selections to one record set and make a report on that.
Or read the first line into some amper values and use them in a sub foot.
Many ways to solve this.

Could you use BY HIGHEST 1 to get the most recent and WHERE TOTAL to filter the period. This should give what you require.

Wouldn't something like this work?

TABLE FILE etc
PRINT NAME etc
BY HIGHEST 1 DATE NOPRINT
BY ...(other BY cluses)
WHERE DATE GE 20100101
AND DATE LE 20121231;
END

Your WHERE clause looks unnecessarily complex to me ....

You might have to HOLD and rearrange the result of course.

If I understand you correctly, and if the code that you supplied already retrieves the correct records, then have you tried to add BY HIGHEST 1 DATE to your query e.g.

TABLE FILE CARS
PRINT
NAME AS 'Buyer Name'
MAKE AS 'Car Make'
MODEL AS 'Car Model'
DATE AS 'Purchase Date'
BY DEALSHIP NOPRINT
BY SALESPERSON NOPRINT

BY HIGHEST 1 DATE

BY CALENDARYR NOPRINT
BY NAME NOPRINT

WHERE ...... ;

The way WebFOCUS should process your request is that it will first retrieve all the records that match your WHERE clause, and then, within that answer set, it will find the "HIGHEST 1" date per DEALERSHIP/SALESPERSON combination.

Ah wait - I read your request again.
This won't be very efficient, but a possible approach:
First select the most recent record for every buyer - then hold that data - and then apply your WHERE clause for your date range on the HOLD file
e.g.

TABLE FILE CARS
PRINT all the fields you need
BY NAME
BY HIGHEST 1 DATE
ON TABLE HOLD AS XXX
END
-RUN
TABLE FILE XXX
PRINT whatever you want to see
WHERE selection for date range ;
END
  

Apologies - I only just saw Alan's earlier post regarding combining "BY HIGHEST 1" with "WHERE TOTAL". That may in fact be a more elegant "one step" solution which should produce the same result as my last suggestion.

Go Alan, thats the way, one step.

Its important to know the order of processing of a TABLE FILE.

I remember some of the old course notes that has diagrams of the order of processing. I think that these should be readily available so we can pin it to our workstations.

Who can remember the order ?

Why would a WHERE TOTAL work?
I've looked through some documentation and I'm just not seeing how to properly use this. Hmm.

Thanks for all your help!
Shawna

This Totally WORKED. OMG THANK YOU.
I'm not going to close this just yet though, I'm still interested in the WHERE TOTAL clause.
Thank you again, I owe ya a drink.

quote:

Originally posted by Twanette:
Ah wait - I read your request again.
This won't be very efficient, but a possible approach:
First select the most recent record for every buyer - then hold that data - and then apply your WHERE clause for your date range on the HOLD file
e.g.

TABLE FILE CARS
PRINT all the fields you need
BY NAME
BY HIGHEST 1 DATE
ON TABLE HOLD AS XXX
END
-RUN
TABLE FILE XXX
PRINT whatever you want to see
WHERE selection for date range ;
END
  

Shawna

WHERE/IF TOTAL works on the aggregated values, but it is also used to create a final screening condition before report display.

TABLE FILE CARS
PRINT all the fields you need
BY NAME
-* Select top record(s) with the highest date 
BY HIGHEST 1 DATE
-* Limit to including and above the lowest date in the date range selected
WHERE DATE GE 'lowest date'
-* Finally, after everything has been retrieved, limit the result shown to chosen date range
WHERE TOTAL DATE FROM 'lowest date' TO 'highest date'
END

For some reason realised I had missed the TOTAL out of the last where clause! The most important bit...

This message has been edited. Last edited by: Alan B,

Geez, Alan gets all the credit, snif ....

I tried this but experienced some interesting results. It works with 2 BY fields, but as I add more (like 'BY SALESPERSON', etc) I'm no longer only returning 1 row back per the entire report.

I'll keep trying though and thank you very much for your help.

quote:

Originally posted by Alan B:
Shawna

WHERE/IF TOTAL works on the aggregated values, but it is also used to create a final screening condition before report display.

TABLE FILE CARS
PRINT all the fields you need
BY NAME
-* Select top record(s) with the highest date 
BY HIGHEST 1 DATE
-* Limit to including and above the lowest date in the date range selected
WHERE DATE GE 'lowest date'
-* Finally, after everything has been retrieved, limit the result shown to chosen date range
WHERE DATE FROM 'lowest date' TO 'highest date'
END

omg George, I am so sorry! THANK YOU!
You helped me out too. Every1's suggestion was helpful, I just don't understand how to implement all of the suggestions, haha.
You rock.

quote:

Originally posted by George Patton:
Geez, Alan gets all the credit, snif ....

I have a program that is pulling various variables and I asking to pull by the highest 1 effective term (date). It is placed on hold using format alpha. However, at the end of the result file there is a '1'. I do not want it to print. I am using the following code but it is not working. How do I get rid of it?

BY HIGHEST 1 SCBCRSE_EFF_TERM NOPRINT

It is holding the rank number in your hold file.

Try adding this ...

ON TABLE SET HOLDLIST PRINTONLY

quote:

ON TABLE SET HOLDLIST PRINTONLY

Hello,
It worked but it is not in a text format.

I need the results to be in text format. With this command, it is printing in html