I have a sample set of data in this db<>fiddle. The data represents a batch of wells that fall into different well-type categories based on some criteria. I am trying to group the wells by the category they fall into and then count how many wells are in each category based on another set of criteria.

My current query partially works but only correctly counts wells that are higher in the CASE WHEN clause hierarchy. This is because the first CASE WHEN has the chance to assign well categories to all of the wells in the data set. However, as it goes through each CASE WHEN clause, the query "see's" fewer wells because it runs out of wells it can assign a category to. By the time it reaches the end, almost all of the wells have already had a category assigned to them, preventing some category counts from occurring at all.

Here is the current query I have, which is also in the db<>fiddle link above:

SELECT
    dt.WellCategory,
    ISNULL(SUM(CASE WHEN dt.LeaseType IN ('F','I','S','P') OR dt.LeaseType NOT IN ('F', 'I', 'S', 'P', 'U') THEN 1 END), 0) AS [Number of Wells], -- Federal   Indian   State   Fee   Multi-Lease
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'F' THEN 1 END), 0) AS [Federal], -- Sums up how many wells from the subquery have a leasetype of "Federal"
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'I' THEN 1 END), 0) AS [Indian],
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'S' THEN 1 END), 0) AS [State],
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'P' THEN 1 END), 0) AS [Fee (Private)],
    ISNULL(SUM(CASE WHEN dt.LeaseType NOT IN ('F', 'I', 'S', 'P', 'U') THEN 1 END), 0) AS [Multiple Lease Types]
FROM
(
    SELECT -- Subquery labels wells according to their wellstatus, welltype, etc.
        c.LeaseType,
        CASE 
            WHEN w.WellStatus = 'p' AND w.WellType = 'gw' OR ((w.WellStatus = 'pai' OR w.WellStatus = 'pii') AND (w.WellType = 'gwi' OR w.WellType = 'ggi' OR w.WellType = 'gwd')) THEN 'Producing Gas Wells'
            WHEN w.WellStatus = 'p' AND w.WellType = 'ow' OR ((w.WellStatus = 'pai' OR w.WellStatus = 'pii') AND (w.WellType = 'owi' OR w.WellType = 'ogi' OR w.WellType = 'owd')) THEN 'Producing Oil Wells'
            WHEN w.WellStatus = 's' AND w.WellType = 'ow' OR ((w.WellStatus = 'sai' OR w.WellStatus = 'sii') AND (w.WellType = 'owi' OR w.WellType = 'ogi' OR w.WellType = 'owd')) THEN 'Shut-In Oil Wells'
            WHEN w.WellStatus = 's' AND w.WellType = 'gw' OR ((w.WellStatus = 'sai' OR w.WellStatus = 'sii') AND (w.WellType = 'gwi' or w.WellType = 'ggi' or w.WellType = 'gwd')) THEN 'Shut-In Gas Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'wi' THEN 'Active Water Injection Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'gi' THEN 'Active Gas Injection Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'wd' THEN 'Active Water Disposal Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'gs' THEN 'Active Gas Storage Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'ws' THEN 'Active Water Source Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'tw' THEN 'Active Test Holes'
            WHEN w.WellStatus = 'i' AND w.WellType = 'wi' THEN 'Inactive Water Injection Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'gi' THEN 'Inactive Gas Injection Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'wd' THEN 'Inactive Water Disposal Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'gs' THEN 'Inactive Gas Storage Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'ws' THEN 'Inactive Water Source Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'tw' THEN 'Inactive Test Holes'
            WHEN w.WellStatus = 'ta' THEN 'Temporarily-Abandoned Wells'
            WHEN w.WellStatus = 'pa' THEN 'Plugged and Abandoned Wells'
            WHEN c.LateralStatus = 'NEW' THEN 'New Permits - Not Yet Approved'
            WHEN c.LateralStatus = 'APD' THEN 'Permits Approved - Not Yet Commenced'
            WHEN c.LateralStatus = 'DRL' THEN 'Drilling Commenced - Not Yet Completed'
            WHEN c.LateralStatus = 'OPS' THEN 'Drilling Operations Suspended'
            WHEN w.WellStatus IN ('drl','ops','p','s','ta','pai','pii','sai','sii','a','i') AND w.Operator = 101600 THEN 'Open Orphan Wells (no known operator)'
            WHEN w.WellStatus IN ('drl','ops') THEN 'Total Holes Not Yet Completed'
            WHEN ((w.WellStatus = 'p' or w.WellStatus = 's') AND w.WellType <> 'LI') OR (w.WellStatus = 'pai' OR w.WellStatus = 'pii' OR w.WellStatus = 'sai' OR w.WellStatus = 'sii') THEN 'Total Wells Capable of Production'
            WHEN (w.WellStatus = 'drl' OR w.WellStatus = 'ops' OR ((w.WellStatus = 'p' or w.WellStatus = 's') AND w.WellType <> 'LI') OR w.WellStatus = 'ta' OR w.WellStatus = 'pai' OR w.WellStatus = 'pii' OR w.WellStatus = 'sai' OR w.WellStatus = 'sii' OR w.WellStatus = 'a' OR w.WellStatus = 'i') THEN 'Total Non-Plugged Wells'
            WHEN (w.WellStatus = 'drl' or w.WellStatus = 'ops' or ((w.WellStatus = 'p' or w.WellStatus = 's') and w.WellType <> 'LI') or w.WellStatus = 'ta' or w.WellStatus = 'pai' or w.WellStatus = 'pii' or w.WellStatus = 'sai' or w.WellStatus = 'sii' or w.WellStatus = 'a' or w.WellStatus = 'i' or w.WellStatus = 'pa') THEN 'Total Wells Drilled'
        END AS WellCategory
    FROM HWell w
        LEFT JOIN HConstruct c ON c.WellKey = w.PKey
) dt
GROUP BY dt.WellCategory
ORDER BY dt.WellCategory DESC

Here is the table the query above produces:

Table #1

Here is the table I know the same data set can produce and the one I want to replicate:

Table #2

Table #2 is generated using a much longer query that uses several subqueries and temp tables to count how many wells belong to each category using their DISTINCT w.WellID. In addition, the full data set has a lot more entries, usually each category has at least some wells in it and not so many "0's".

I don't know how to tell the query to count wells more than once if they fall into several well categories without making the query super long and start introducing temp tables, which I would prefer not to do. *NOTE I do not want to count a well twice for same category, only count it once per category.

CodePudding user response：

Here's one way you could do it while keeping it somewhat easy to read:

I broke the CASE statement into logical pieces. Parts that don't overlap but seem related go into their own query to determine the category. Then I UNION ALL them together to avoid de-duping.

There's tons of ways this could be done, and this is just one of them...

Also, like I mentioned in the chat session, if you are able to, I would highly recommend working on normalizing this data, creating lookup tables, etc.

WITH cte_Wells AS (
    SELECT w.WellStatus, w.WellType, c.LateralStatus, c.LeaseType, w.Operator
    FROM dbo.HWell w
        LEFT JOIN dbo.HConstruct c ON c.WellKey = w.PKey
)
SELECT
    dt.WellCategory,
    ISNULL(SUM(CASE WHEN dt.LeaseType IN ('F','I','S','P') OR dt.LeaseType NOT IN ('F', 'I', 'S', 'P', 'U') THEN 1 END), 0) AS [Number of Wells], -- Federal   Indian   State   Fee   Multi-Lease
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'F' THEN 1 END), 0) AS [Federal], -- Sums up how many wells from the subquery have a leasetype of "Federal"
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'I' THEN 1 END), 0) AS [Indian],
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'S' THEN 1 END), 0) AS [State],
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'P' THEN 1 END), 0) AS [Fee (Private)],
    ISNULL(SUM(CASE WHEN dt.LeaseType NOT IN ('F', 'I', 'S', 'P', 'U') THEN 1 END), 0) AS [Multiple Lease Types]
FROM (
    SELECT w.LeaseType, x.WellCategory
    FROM cte_Wells w
        CROSS APPLY (
            SELECT WellCategory = CASE 
                                    WHEN w.WellStatus = 'p' AND w.WellType = 'gw' OR (w.WellStatus IN ('pai','pii') AND w.WellType IN ('gwi','ggi','gwd')) THEN 'Producing Gas Wells'
                                    WHEN w.WellStatus = 'p' AND w.WellType = 'ow' OR (w.WellStatus IN ('pai','pii') AND w.WellType IN ('owi','ogi','owd')) THEN 'Producing Oil Wells'
                                    WHEN w.WellStatus = 's' AND w.WellType = 'gw' OR (w.WellStatus IN ('sai','sii') AND w.WellType IN ('gwi','ggi','gwd')) THEN 'Shut-In Gas Wells'
                                    WHEN w.WellStatus = 's' AND w.WellType = 'ow' OR (w.WellStatus IN ('sai','sii') AND w.WellType IN ('owi','ogi','owd')) THEN 'Shut-In Oil Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'wi' THEN 'Active Water Injection Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'gi' THEN 'Active Gas Injection Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'wd' THEN 'Active Water Disposal Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'gs' THEN 'Active Gas Storage Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'ws' THEN 'Active Water Source Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'tw' THEN 'Active Test Holes'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'wi' THEN 'Inactive Water Injection Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'gi' THEN 'Inactive Gas Injection Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'wd' THEN 'Inactive Water Disposal Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'gs' THEN 'Inactive Gas Storage Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'ws' THEN 'Inactive Water Source Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'tw' THEN 'Inactive Test Holes'
                                    WHEN w.WellStatus = 'ta' THEN 'Temporarily-Abandoned Wells'
                                    WHEN w.WellStatus = 'pa' THEN 'Plugged and Abandoned Wells'
                                    ELSE NULL
                                END
        ) x
    WHERE x.WellCategory IS NOT NULL
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, x.WellCategory
    FROM cte_Wells w
        CROSS APPLY (
            SELECT WellCategory = CASE 
                                    WHEN w.LateralStatus = 'NEW' THEN 'New Permits - Not Yet Approved'
                                    WHEN w.LateralStatus = 'APD' THEN 'Permits Approved - Not Yet Commenced'
                                    WHEN w.LateralStatus = 'DRL' THEN 'Drilling Commenced - Not Yet Completed'
                                    WHEN w.LateralStatus = 'OPS' THEN 'Drilling Operations Suspended'
                                    ELSE NULL
                                END
        ) x
    WHERE x.WellCategory IS NOT NULL
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Open Orphan Wells (no known operator)'
    FROM cte_Wells w
    WHERE w.WellStatus IN ('drl','ops','p','s','ta','pai','pii','sai','sii','a','i') AND w.Operator = 101600
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Total Holes Not Yet Completed'
    FROM cte_Wells w
    WHERE w.WellStatus IN ('drl','ops')
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Total Wells Capable of Production'
    FROM cte_Wells w
    WHERE (w.WellStatus IN ('p','s') AND w.WellType <> 'LI') OR w.WellStatus IN ('pai','pii','sai','sii')
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Total Non-Plugged Wells'
    FROM cte_Wells w
    WHERE (w.WellStatus IN ('p','s') AND w.WellType <> 'LI') OR w.WellStatus IN ('drl','ops','ta','pai','pii','sai','sii','a','i')
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Total Wells Drilled'
    FROM cte_Wells w
    WHERE (w.WellStatus IN ('p','s') AND w.WellType <> 'LI') or w.WellStatus IN ('drl','ops','ta','pai','pii','sai','sii','a','i','pa')
) dt
GROUP BY dt.WellCategory
ORDER BY dt.WellCategory DESC

I've checked the results of this query against your sample data and proposed expected results, and they appear to match. So this will get you what you want. However, since I don't know the data, you'll need to inspect it for logic errors, and performance.

NOTE: The results of this query do not produce counts for empty categories. If that is something you need, then comment on this solution and I can fix it. The fix is to add a table with all categories, then LEFT JOIN to that the results of the counts.

CodePudding user response：

One way in which you could do this would be to generate a row for each criteria and then aggregate up those that return a match, which can be done using cross apply and a values table generator that ensures all your case expressions are evaluated for all wells.

This approach at present assumes that each well only has the one WellCategory value. If there can be more than one then you will need to make those changes yourself. I also haven't wrapped the output in a pivot as I think this is generally something best done in your presentation layer rather than the raw SQL.

I suggest keeping the SQL output in this format as presentation tools are much better at dynamically handling new categories (e.g. a new LeaseType is added) than SQL is, which in this normalised format wouldn't require a change in either the script or the presentation layer, barring any bespoke labels.

If you want to retain the pivoted output, you can simply wrap this query in your current outer select:

Query

select c.LeaseType
      ,cat.WellCategory
      ,count(w.PKey) as Wells
from @HWell as w
    left join @HConstruct as c
        on w.Pkey = c.WellKey
    cross apply(values(case when w.WellStatus = 'p' AND w.WellType = 'gw' OR ((w.WellStatus = 'pai' OR w.WellStatus = 'pii') AND (w.WellType = 'gwi' OR w.WellType = 'ggi' OR w.WellType = 'gwd')) then 'Producing Gas Wells' end)
                     ,(case when w.WellStatus = 'p' AND w.WellType = 'ow' OR ((w.WellStatus = 'pai' OR w.WellStatus = 'pii') AND (w.WellType = 'owi' OR w.WellType = 'ogi' OR w.WellType = 'owd')) then 'Producing Oil Wells' end) -- Need to count DISTINCT WellID's to get to 4770
                     ,(case when w.WellStatus = 's' AND w.WellType = 'ow' OR ((w.WellStatus = 'sai' OR w.WellStatus = 'sii') AND (w.WellType = 'owi' OR w.WellType = 'ogi' OR w.WellType = 'owd')) then 'Shut-In Oil Wells' end)
                     ,(case when w.WellStatus = 's' AND w.WellType = 'gw' OR ((w.WellStatus = 'sai' OR w.WellStatus = 'sii') AND (w.WellType = 'gwi' or w.WellType = 'ggi' or w.WellType = 'gwd')) then 'Shut-In Gas Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'wi' then 'Active Water Injection Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'gi' then 'Active Gas Injection Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'wd' then 'Active Water Disposal Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'gs' then 'Active Gas Storage Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'ws' then 'Active Water Source Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'tw' then 'Active Test Holes' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'wi' then 'Inactive Water Injection Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'gi' then 'Inactive Gas Injection Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'wd' then 'Inactive Water Disposal Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'gs' then 'Inactive Gas Storage Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'ws' then 'Inactive Water Source Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'tw' then 'Inactive Test Holes' end)
                     ,(case when w.WellStatus = 'ta' then 'Temporarily-Abandoned Wells' end)
                     ,(case when w.WellStatus = 'pa' then 'Plugged and Abandoned Wells' end)
                     ,(case when c.LateralStatus = 'NEW' then 'New Permits - Not Yet Approved' end)
                     ,(case when c.LateralStatus = 'APD' then 'Permits Approved - Not Yet Commenced' end)
                     ,(case when c.LateralStatus = 'DRL' then 'Drilling Commenced - Not Yet Completed' end)
                     ,(case when c.LateralStatus = 'OPS' then 'Drilling Operations Suspended' end)
                     ,(case when w.WellStatus IN ('drl','ops','p','s','ta','pai','pii','sai','sii','a','i') AND w.Operator = 101600 then 'Open Orphan Wells (no known operator)' end)
                     ,(case when w.WellStatus IN ('drl','ops') then 'Total Holes Not Yet Completed' end)
                     ,(case when ((w.WellStatus = 'p' or w.WellStatus = 's') AND w.WellType <> 'LI') OR (w.WellStatus = 'pai' OR w.WellStatus = 'pii' OR w.WellStatus = 'sai' OR w.WellStatus = 'sii') then 'Total Wells Capable of Production' end)
                     ,(case when (w.WellStatus = 'drl' OR w.WellStatus = 'ops' OR ((w.WellStatus = 'p' or w.WellStatus = 's') AND w.WellType <> 'LI') OR w.WellStatus = 'ta' OR w.WellStatus = 'pai' OR w.WellStatus = 'pii' OR w.WellStatus = 'sai' OR w.WellStatus = 'sii' OR w.WellStatus = 'a' OR w.WellStatus = 'i') then 'Total Non-Plugged Wells' end)
                     ,(case when (w.WellStatus = 'drl' or w.WellStatus = 'ops' or ((w.WellStatus = 'p' or w.WellStatus = 's') and w.WellType <> 'LI') or w.WellStatus = 'ta' or w.WellStatus = 'pai' or w.WellStatus = 'pii' or w.WellStatus = 'sai' or w.WellStatus = 'sii' or w.WellStatus = 'a' or w.WellStatus = 'i' or w.WellStatus = 'pa') then 'Total Wells Drilled' end)
               ) as cat(WellCategory)
where cat.WellCategory is not null
group by c.LeaseType
        ,cat.WellCategory
order by c.LeaseType
        ,Wells desc;

Output