Results 1 to 10 of 10
  1. #1
    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. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    An outer join on week=week+1 should do the trick

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    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. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    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. #7
    Join Date
    May 2014
    Posts
    16
    sir pls. can you give me the syntax of this trick you were referring

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

  9. #9
    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. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    ;
    George
    Home | Blog

Posting Permissions

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