Need substitute for using Cross Apply with SQL Server 2000

I wrote the following script that works fine in my SQL Server 2008 developer database using the CROSS APPLY parameter but it will not work for the client who has a SQL Server 2000 database and does not want to change the compatibility setting in SSMS.

Can someone suggest a way to convert my script below so as not to use the CROSS APPLY parameter? Thanks!

  • Performance Impact on Service Broker WAITFOR RECEIVE with no TIMEOUT
  • Why would a Entity Framework count statement timeout when the direct sql returns instantly?
  • How to find the other tables that a foreign key in my table is related to?
  • SQL Server Full Text Index query
  • T-SQL Is a sub query for an Update restriction Atomic with the update?
  • Count multiple repeats after event as single repeat
  • DECLARE @Flag INT
    SET @Flag = 1
    WHILE (@Flag < (SELECT TOP 1 (COUNT(CUSTOMERPN)) AS COUNTCUST FROM FC_Nestle
    GROUP BY CustomerPN
    ORDER BY COUNTCUST DESC))
    BEGIN
    --UPDATE AFS_TOPRODUCE COUNTS
    UPDATE FC_Nestle
    SET AFS_ToProduce = CustReqQty - AFS_OH
    --UPDATE SUBSEQUENT AFS_OH INVENTORY COUNTS
    update FC_Nestle 
    set AFS_OH = - fc2.AFS_ToProduce 
    from FC_Nestle 
    CROSS APPLY 
    (   
        select fc2.AFS_ToProduce   
        from    
        (     
            select top 1         
                fc2.AFS_ToProduce       
            from FC_Nestle fc2      
            where fc2.ForecastID < FC_Nestle.ForecastID and fc2.CustomerPN = FC_Nestle.CustomerPN     
            order by fc2.ForecastID desc   
        ) fc2   
        where fc2.AFS_ToProduce < 0 
    ) fc2 
    where FC_Nestle.AFS_ToProduce > 0 
    SET @Flag = @Flag + 1
    END
    

    One Solution collect form web for “Need substitute for using Cross Apply with SQL Server 2000”

    this should help you out:

    update fc_test
       set AFS_OH = - fc2.AFS_ToProduce
      from fc_test
     -- Self join to find previous row
     inner join fc_test fc2
     -- which has lower forecastid
        on fc2.ForecastID < fc_test.ForecastID
     where fc_test.AFS_ToProduce > 0
     -- and negative afs_toproduce
       and fc2.afs_toproduce < 0
     -- and is a row immediately before
     -- ie there is no other row closer to fc_test then current fc2
       and not exists (select null 
                         from fc_test fc3
                        where fc3.forecastid > fc2.forecastid
                          and fc3.ForecastID < fc_test.ForecastID)
    

    I’m using the same table and column names as last time.

    Live test is at Sql Fiddle.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.