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

    Unanswered: Returning Rows with Null Values

    The below code returns only those rows that contain [Contractor Rig] values. However, I want to include rows whether or not [Contractor Rig] data was ever populated in the database. I've tried LEFT & RIGHT JOINS but keep getting the same data. I've thought about maybe doing a UNION but don't know how to write the code to pull only the data that's missing from the original code.

    Thanks for any help.
    sisterwolf


    Property # ... Well Name ... Contractor Rig ... Drill Date ... Depth Drilled
    50183 ......... Clark 1H ...... HWD 6 ............. 02/14/11 ... 65
    50183 ......... Clark 1H ...... HWD 6 ............. 02/15/11 ... 876
    50458 ......... Ensign 125 ... HWD 6 ............. 10/18/11 ... 130
    50458 ......... Ensign 125 ... HWD 6 ............. 10/18/11 ... 130
    50519 ......... Ensign 125 ... _____ ............. 03/01/10 ... 49
    50519 ......... Ensign 125 ... _____ ............. 03/02/10 ... 349

    Code:
     
    SELECT 
       wh.leasecode AS [Property #]
      ,wh.wellname AS [Well Name]
      ,ISNULL(rig.contractor + ' ' + ISNULL(rig.rigno,''),'') AS [Contractor Rig]
      ,CONVERT(VARCHAR(8),dp.dttmstart,1) AS [Drill Date]
      ,MAX(ROUND(dp.depthend*3.28084,0))-MIN(ROUND(dp.depthstart*3.28084,0)) as [Depth Drilled]
    FROM 
       wvjobdrillstringparam AS dp
      ,wvwellheader AS wh
      ,wvjobdrillstring AS ds
      ,wvjobrig AS rig
    WHERE 
          dp.idwell = wh.idwell
      AND ds.idrec = dp.idrecparent
      AND ds.idrecparent = rig.idrecparent  -- idrecparent here is the Job ID (from wvjob table)
      AND rig.dttmstart <= dp.dttmstart     -- matches contractor rig to specific job based on when contractor was on site
      AND wh.leasecode IS NOT NULL
      AND dp.depthstart IS NOT NULL
      AND dp.depthend IS NOT NULL
      AND dp.dttmstart IS NOT NULL
      AND wh.division = 'East'
      AND wh.operator = 'Surrio'  
      AND ROUND(dp.depthend*3.28084,0)- ROUND(dp.depthstart*3.28084,0) <> 0
      AND DATEDIFF(day,dp.dttmstart,dp.dttmend) <=1
    GROUP BY 
       wh.leasecode
      ,wh.wellname
      ,rig.contractor + ' ' + ISNULL(rig.rigno,'')
      ,CONVERT(VARCHAR(8),dp.dttmstart,1)
    ORDER BY 2,4

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT 
       wh.leasecode AS [Property #]
      ,wh.wellname AS [Well Name]
      ,ISNULL(rig.contractor + ' ' + ISNULL(rig.rigno,''),'') AS [Contractor Rig]
      ,CONVERT(VARCHAR(8),dp.dttmstart,1) AS [Drill Date]
      ,MAX(ROUND(dp.depthend*3.28084,0))-MIN(ROUND(dp.depthstart*3.28084,0)) as [Depth Drilled]
    FROM 
    	wvjobdrillstringparam AS dp
    		INNER JOIN wvwellheader AS wh ON
    			dp.idwell = wh.idwell
    		INNER JOIN wvjobdrillstring AS ds ON
    			dp.idrecparent = ds.idrec
    		LEFT OUTER JOIN wvjobrig AS rig ON
    			ds.idrecparent = rig.idrecparent  -- idrecparent here is the Job ID (from wvjob table)
    			AND dp.dttmstart >= rig.dttmstart -- matches contractor rig to specific job based on when contractor was on site
    WHERE 
    	wh.leasecode IS NOT NULL
      AND dp.depthstart IS NOT NULL
      AND dp.depthend IS NOT NULL
      AND dp.dttmstart IS NOT NULL
      AND wh.division = 'East'
      AND wh.operator = 'Surrio'  
      AND dp.depthend <> dp.depthstart
      AND DATEDIFF(day,dp.dttmstart,dp.dttmend) <=1
    GROUP BY 
       wh.leasecode
      ,wh.wellname
      ,ISNULL(rig.contractor + ' ' + ISNULL(rig.rigno,''),'')
      ,CONVERT(VARCHAR(8),dp.dttmstart,1)
    ORDER BY 2,4
    I rewrote the old school FROM WHERE to current best practice INNER JOINs.

    ROUND(dp.depthend*3.28084,0)- ROUND(dp.depthstart*3.28084,0) <> 0
    Can be rewritten as :
    dp.depthend - dp.depthstart <> 0
    or more simple:
    dp.depthend <> dp.depthstart
    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
    Oct 2011
    Location
    Pittsburgh, PA
    Posts
    13

    Thumbs up Solved

    Wim, you are awesome! Thanks for the help and the learning. I'm liking the JOINS and I'm already using them in other codes.

    sisterwolf

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

    Added Complexity...How to add CASE WHEN to LEFT OUTER JOIN

    This data and code will likely bring on my insanity.

    Once I was able to analyze and compare the data, I realized that I needed to add some more conditions to the code. Sometimes there are multiple rigs assigned to a site with DTTMSTART and DTTMEND (sometimes null depending on if rig is still on site). I'm trying to add a CASE WHEN statement to the LEFT OUTER JOIN but not having success.

    Code:
     
    SELECT 
       wh.leasecode AS [Property #]
      ,wh.wellname AS [Well Name]
      ,wh.county AS County
      ,ISNULL(rig.contractor + ' ' + ISNULL(rig.rigno,''),'') AS [Contractor Rig]
      ,CONVERT(VARCHAR(8),dp.dttmstart,1) AS [Drill Date]
      ,MAX(ROUND(dp.depthend*3.28084,0))-MIN(ROUND(dp.depthstart*3.28084,0)) as [Depth Drilled]  -- converts data to feet
    FROM 
       wvjobdrillparam AS dp
         INNER JOIN wvwellheader AS wh ON
           dp.idwell = wh.idwell
         INNER JOIN wvjobdrillstring AS ds ON
           dp.idrecparent = ds.idrec
         LEFT OUTER JOIN wvjobrig AS rig ON 
           ds.idrecparent = rig.idrecparent  -- idrecparent here is the Job ID (from wvjob table)
    	   AND (CASE WHEN rig.dttmend IS NOT NULL THEN dp.dttmstart >= rig.dttmstart 
     	                                              AND dp.dttmstart <= rig.dttmend
    	            ELSE dp.dttmstart >= rig.dttmstart
    	       END)                        -- matches contractor rig to specific job based on when contractor was on site
    WHERE 
            wh.leasecode IS NOT NULL
      AND dp.depthstart IS NOT NULL
      AND dp.depthend IS NOT NULL
      AND dp.dttmstart IS NOT NULL
      AND wh.division = 'East'
      AND wh.operator = 'Surrio'  
      AND ROUND(dp.depthend*3.28084,0)- ROUND(dp.depthstart*3.28084,0) <> 0 -- need to keep ROUND() else 0 is returned
      AND dp.depthend - dp.depthstart > 0
      AND DATEDIFF(day,dp.dttmstart,dp.dttmend) <=1
    GROUP BY 
       wh.leasecode
      ,wh.wellname
      ,wh.county
      ,ISNULL(rig.contractor + ' ' + ISNULL(rig.rigno,''),'')
      ,CONVERT(VARCHAR(8),dp.dttmstart,1)
    ORDER BY 2,6

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try replacing
    Code:
    	   AND (CASE WHEN rig.dttmend IS NOT NULL THEN dp.dttmstart >= rig.dttmstart 
     	                                              AND dp.dttmstart <= rig.dttmend
    	            ELSE dp.dttmstart >= rig.dttmstart
    	       END)                        -- matches contractor rig to specific job based on when contractor was on site
    by
    Code:
    	   AND ((rig.dttmend IS NOT NULL AND 
    		dp.dttmstart >= rig.dttmstart AND 
    		dp.dttmstart <= rig.dttmend) 
    		OR
    	         (rig.dttmend IS NULL AND 
    	         dp.dttmstart >= rig.dttmstart)
    	        )
    Last edited by Wim; 10-14-11 at 15:06. Reason: removed a "NOT", so the text after "OR" became "rig.dttmend IS NULL AND "
    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

  6. #6
    Join Date
    Oct 2011
    Location
    Pittsburgh, PA
    Posts
    13
    Nope. That didn't work. No rows were returned.

  7. #7
    Join Date
    Oct 2011
    Location
    Pittsburgh, PA
    Posts
    13
    Wait a minute. I think I spoke to soon (it's lunch time & I'm not thinking straight). Let me look at what I just did first. Then I'll let you know for sure.

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

    Thumbs up Solved

    You are the SQL Master. I definitely want to be you when I grow up.

    The only thing I changed from your last code was the 2nd IS NOT NULL to IS NULL and I got the correct data.

    Code:
    AND ((rig.dttmend IS NOT NULL AND 
    		dp.dttmstart >= rig.dttmstart AND 
    		dp.dttmstart <= rig.dttmend) 
    		OR
    	         (rig.dttmend IS NULL AND 
    	         dp.dttmstart >= rig.dttmstart)
    	        )
    Thanks so much for your help.
    sisterwolf

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Wim, you are awesome!
    You are the SQL Master. I definitely want to be you when I grow up.
    You really are a charmer! But please, keep posting more kudos in the future.

    The only thing I changed from your last code was the 2nd IS NOT NULL to IS NULL and I got the correct data.
    OMG, the pupil has already surpassed her master, 2.5 hours after I posted my solution, OMG, OMG. I'm getting too old for this.

    Tanks for the beer. Earned beers is now 9.

    I have corrected the error in my previous post. Copy and paste has its drawbacks.
    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

Posting Permissions

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