Need Help in Constructing looping SELECT statement but calculating the result of rows

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

thanks pat for your replied sql, but the rowdata i put it in my query is only a sample data it will grow to more rows with more ponds, so instead of using VALUES...
i shall use this? correct me it this is wrong please.

WITH c1 AS (
SELECT PondCrop, WeekNumber, ABW FROM table1 as v),
c2 AS (
SELECT c1.PondCrop, c1.WeekNumber, c1.ABW
, Coalesce(c1.ABW - Lag(c1.ABW)
OVER (PARTITION BY PondCrop ORDER BY PondCrop, WeekNumber)
, c1.ABW) AS ResultNeeded
FROM c1
)
SELECT *
FROM c2
ORDER BY PondCrop, WeekNumber

Without access to your server I don't know a way to give you precise "copy and watch it work" code.

Copy the sample from my first post into a SSMS (SQL Server Management Studio) Query window then execute it in the tempdb database. If the query fails to run in tempdb, then there is a problem with your SQL Server installation.

If the sample query runs properly in tempdb, then switch to your own database and run the sample query there. If the query ran in tempdb but fails in your database, then the problem lies in your database settings. My first guess would be the database compatibility level. You can fix that via the SSMS GUI or using the sp_dbcmptlevel stored procedure.

If the sample query works in both tempdb and in your own database, then the problem lies in the query that you've written. Post it EXACTLY as you are trying to run it, and I'll try to help debug it. This may be quite complicated because I don't have access to your platform (server, schema, data, etc.).

-PatP

In theory, theory and practice are identical. In practice, theory and practice are unrelated.

I tried that and post it to the temp table but its getting the same LAG function error, it seems the error comes from the installation of SQL Server leaving that function not working but I will try to refer to our DBA thanks Pat. I will keep you posted about the outcome.