Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Location
    Pittsburgh, PA
    Posts
    13

    Unanswered: Return Data Row (including Date) with Max Value

    The below code returns the total depth for a given well for each day. Now I want to have code that returns only 1 row of data with the maximum total depth per well (including the date). If I excluded the date from the below code, I get the correct depth but I need the date for it. Please help. Thanks.

    This is the data that I can currently get with below code:

    Property #....Well Name........County....Job Date.......Largest Depth
    50183..........TANNER 1H........Ellis........01/01/11.......1,505
    50183..........TANNER 1H........Ellis........01/02/11.......459
    50183..........TANNER 1H........Ellis........01/04/11.......10
    50196..........MURRAY 1H.......Dioga......06/30/10.......62
    50196..........MURRAY 1H.......Dioga......07/01/10.......8
    50196..........MURRAY 1H.......Dioga......08/04/10.......620

    The data that I want now is....

    Property #....Well Name.........County...Job Date......Largest Depth
    50183..........TANNER 1H........Ellis........01/01/11.....1,505
    50196..........MURRAY 1H.......Dioga......08/04/10.....620

    Code:
     
    SELECT 
       wh.leasecode AS [Property #]
      ,wh.wellname AS [Well Name]
      ,wh.county AS County
      ,CONVERT(VARCHAR(8), jp.dttmstart,1) AS [Job Date]
      ,MAX(ROUND(jp.depthend*3.28084,0))-MIN(ROUND(jp.depthstart*3.28084,0)) AS [Largest Depth]
    FROM
       wv90.dbo.wvjobparam AS jp
      ,wv90.dbo.wvwellheader AS wh
    WHERE 
          jp.idwell = wh.idwell
      AND wh.leasecode IS NOT NULL
      AND jp.depthstart IS NOT NULL
      AND jp.depthend IS NOT NULL
      AND jp.dttmstart IS NOT NULL
      AND wh.division = 'East'
      AND wh.operator = 'SR'  
      AND ROUND(jp.depthend*3.28084,0)- ROUND(jp.depthstart*3.28084,0) <> 0
      AND DATEDIFF(day,jp.dttmstart,jp.dttmend) <=1
    GROUP BY
       wh.leasecode
      ,wh.wellname
      ,wh.county
      ,CONVERT(VARCHAR(8), jp.dttmstart,1)
    ORDER BY
      2
    Last edited by sisterwolf_pa; 10-11-11 at 09:00.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work:
    Code:
    SELECT T.*
    FROM DaTable as T
    	INNER JOIN (SELECT Property#, MAX(TotalDepth) as maxDepth
    			FROM DaTable
    			GROUP BY Property#) as MaxT ON
    		T.Property# = MaxT.Property# AND
    		T.TotalDepth = MaxT.maxDepth
    Just replace "DaTable" with your SELECT statement (or turn it into a view, and use the view everywhere I wrote "DaTable".

    This solution will give multiple records per Property#, when there are multiple records with the same depth (= MaxDepth) for one Property#.
    Last edited by Wim; 10-10-11 at 20:06. Reason: added "multiple records" possible
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    You can use CTE feature of sql server for such hierarchical relationships.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by jassi.singh View Post
    Hello,

    You can use CTE feature of sql server for such hierarchical relationships.
    Jassi,

    Can you not only write that this is possible by using CTE, but also show, with working SQL code, how that can be accomplished?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2011
    Location
    Pittsburgh, PA
    Posts
    13

    Thumbs up Solved

    @ Wim...

    Your INNER JOIN worked. I thought that that's what I needed but just couldn't get the specifics. It took some time for me to get the substituting to work due to the size of the code for me but I finally got it.

    THANKS.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The simplest solution would have been wrapping your query in a view, and use that view.
    Code:
    CREATE VIEW DaTable AS
    SELECT 
       wh.leasecode AS [Property #]
      ,wh.wellname AS [Well Name]
      ,wh.county AS County
      ,CONVERT(VARCHAR(8), jp.dttmstart,1) AS [Job Date]
      ,MAX(ROUND(jp.depthend*3.28084,0))-MIN(ROUND(jp.depthstart*3.28084,0)) AS [Largest Depth]
    FROM
       wv90.dbo.wvjobparam AS jp
      ,wv90.dbo.wvwellheader AS wh
    WHERE 
          jp.idwell = wh.idwell
      AND wh.leasecode IS NOT NULL
      AND jp.depthstart IS NOT NULL
      AND jp.depthend IS NOT NULL
      AND jp.dttmstart IS NOT NULL
      AND wh.division = 'East'
      AND wh.operator = 'SR'  
      AND ROUND(jp.depthend*3.28084,0)- ROUND(jp.depthstart*3.28084,0) <> 0
      AND DATEDIFF(day,jp.dttmstart,jp.dttmend) <=1
    GROUP BY
       wh.leasecode
      ,wh.wellname
      ,wh.county
      ,CONVERT(VARCHAR(8), jp.dttmstart,1)
    You can use this in conjunction with my query. It should work right away.
    I'm sure you can come up with a far better name than "DaTable" for the view, but what I showed is all there is to it.

    Why did you want to substitute DaTable with your query? I hope not out of fear for a loss of performance. Views can offer a nice wrapper around a complicated query, as in your case - I wouldn't want to have every programmer write things like "MAX(ROUND(jp.depthend*3.28084,0))-MIN(ROUND(jp.depthstart*3.28084,0))" and hope they have all the numbers right - . Use those views.

    In the rare case when performance does suffer, only then look for an alternative to speed things up. And a view doesn't slow things down, only when you combine views that are based on the very same base tables, that performance can become a problem.

    The only time I had to optimise a view that performed bad was in a MS Dynamics database, where a view was used that consisted of 32 left outer joins with other views. And those 32 views often used the same base views over and over again in their own definition.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Oct 2011
    Location
    Pittsburgh, PA
    Posts
    13
    I had to substitute DaTable with the query because I don't have permission to create views.

Posting Permissions

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