As far as I have understood the problem, what is required is, if total number of units of item in table 2 exceeds that mentioned in table 1 then flag should be 'N' . If the number of units of item in table 2 is less than that in table 1 , then the flag should be 'Y'. And this should be sorted as per the date.
Joining your stock to those Order rows is pretty much the easiest way to get to your end-result. Keep in mind that in the end such a solution wouldn't force you to keep any superfluous fields in your order table. Just the Flag field.
As possible methodology:
Create a mapping table that maps each product ot its stock level.
Take your orders table, and order it by product and date. Use an ORDER BY clause when performing the next two steps. ORDER BY happens before anything else.
At the same time, whenever the previous product is different from the current one, lookup the initial stock value using the mapping table and set Flag=Y if you have enough stock and Flag=N if the initial stock cannot fulfill the requested amount. Store the remaining stock in a new column.
If the current product is the same as the previous product, compare the remaining stock of the previous row to the ordered amount. Set Flag=Y if you still have enough stock and Flag=N if the previous stock cannot fulfill the requested amount. Store the remaining stock in a new column.
Now remove the Remaining stock column and reorder your table as you like. IS that possible in your data model?