How To Give a Condition To Data Row based on First and Next Data?

I'm not sure this is the answer you're looking for, but I would loop through the result set and store the data in a local array and then loop through the array and output the data with the calculated column set as you wish. The array would allow you to loop through each ID to find the latest to mark as new.  This is an ObjectScript approach along with SQL. I wonder if you could just re-save the data back to the table once calculated so you can still project it as needed to other systems.  

Suggestion:
you crate your own stored procedure to decide during SELECT
example:

/// Return NEW for first occurance of item 
/// otherwise return OLD
Class User.ItemStat Extends %RegisteredObject
{
ClassMethod NewOld(item As %String = "") As %String [ SqlProc ]
{
    if item="" quit "?"
    if $d(^||list(item)) quit "OLD"
    if $i(^||list(item)) quit "NEW"
}
}

How to use it:

SELECT *, ItemStat_NewOld(item) as Status
FROM items order by 2

Result:

ID	date	  item	Status
1	09/13/1932	A	NEW
2	04/06/1933	D	NEW
10	06/15/1940	A	OLD
4	11/26/1940	A	OLD
6	02/19/1956	B	NEW
8	04/22/1957	D	OLD
7	05/01/1959	D	OLD
9	06/29/1961		?
3	07/04/1992	B	OLD
5	12/08/2020	D	OLD

Hi,

Using COS is pretty simple. As you loop the global with $o() simply put the ID value in an array or local global at the last line before going into the loop for the next id. Then for each loop, if $d(array(id)) it means is OLD, otherwise is NEW.

Right?

Hi everyone,

That stored procedure solution looks great, and may work, but I'd just like to point out that you have no control over what order the rows are in when the method is called. That's going to be decided by the compiler.

If you've done "ORDER BY Date DESC" then it could build a temporary index by date and then run through that to extract the output, so row 4 would be processed first and the call to the method will return "New" instead of "Old".

Putting in the required order in the query might work, but even then, you have no certainty what the compiler will do. You either need a subquery, or calculate the column outside of SQL when you pull the rows (in the right order).

Regards,

Mike

💡 This question is considered a Key Question. More details here.