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

1. Registered User
Join Date
May 2014
Posts
16

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

Hi I have this select statement and results

SELECT PondCrop, Week,ABW FROM table1 ORDER BY PondCrop

PondCrop Week ABW Calculation
03PA01-20 1 0.45 get the first week of ABW for the same pondcrop
03PA01-20 2 1.02 abw of 2nd week minus 1st week
03PA01-20 3 2.1 abw of 3rd week minus 2nd week
03PA02-21 1 0.5 get the first week of ABW for the same pondcrop
03PA02-21 2 1.23 abw of 2nd week minus 1st week
03PA03-20 1 0.71
03PA03-20 2 1.39
03PA03-20 3 2.43

Desired OUTPUT

PondCrop Week ABW Result Needed
03PA01-20 1 0.45 0.45
03PA01-20 2 1.02 0.57
03PA01-20 3 2.1 1.08
03PA02-21 1 0.5 0.5
03PA02-21 2 1.23 0.73
03PA03-20 1 0.71 0.71
03PA03-20 2 1.39 0.68
03PA03-20 3 2.43 1.04

2. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
An outer join on week=week+1 should do the trick

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I'd suggest something like:
Code:
```WITH c1 AS (
SELECT v.*
FROM (VALUES
('03PA01-20', 1, 0.45)
,	('03PA01-20', 2, 1.02)
,	('03PA01-20', 3, 2.1)
,	('03PA02-21', 1, 0.5)
,	('03PA02-21', 2, 1.23)
,	('03PA03-20', 1, 0.71)
,	('03PA03-20', 2, 1.39)
,	('03PA03-20', 3, 2.43)) AS v (PondCrop, WeekNumber, ABW)
), 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```
-PatP

4. Registered User
Join Date
May 2014
Posts
16
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

Lag(c1.ABW) this will give me an error? is this function or what?
Last edited by jun_lopez; 05-15-16 at 02:54.

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Your sample code looks viable to me, but it can probably be simplified once you understand how Lag() works.

Yes, Lag() is a function in SQL Server 2012 and later. If it works in my CTE example, it will work for your table too.

-PatP

6. Registered User
Join Date
May 2014
Posts
16
how to enable the Lag function, I am using ms sql server 2012 but im getting the error. 'Lag' is not a recognized built-in function name.

7. Registered User
Join Date
May 2014
Posts
16
sir pls. can you give me the syntax of this trick you were referring

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

9. Registered User
Join Date
May 2014
Posts
16
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.

10. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
LAG() https://msdn.microsoft.com/en-us/library/hh231256.aspx

2012 onwards... compatibility mode 110 or greater.
Code:
```SELECT name AS database_name
, compatibility_level
FROM   sys.databases
;```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•