新手SQL求助：基于Test_Table实现按NODE分组及故障时长计算

Let's break down the issues in your original query and build a working solution step by step.

First, Let's List the Key Issues in Your Current SQL #

• Field Name Typos: Your original query uses FIrst OCCURENCE and LAST OCCURENCE but your actual table fields are FIRSTOCCURRENCE and LASTOCCURRENCE (no spaces). Also, you have mismatched brackets like LASTOCCURRENCE] (missing opening [) and Tally], plus a non-existent field [Severity] (your table doesn't include this).
• Table Name Mismatch: You referenced [XYZ].[XYZ].[XYZ_STATUS] but your actual table is Test_Table.
• Date Type Conversion: Your FIRSTOCCURRENCE and LASTOCCURRENCE are stored as VARCHAR, so you can't directly use DATEDIFF on them—you need to convert them to DATETIME first.
• GROUP BY Rule Violation: When using GROUP BY, every non-aggregated column in your SELECT clause must be included in the GROUP BY clause. Your original query selects multiple fields but only groups by node, which will throw an error.

Step 1: Calculate Outage Duration for Individual Records #

First, let's write a query that correctly computes the outage duration for each row, fixing the date conversion and field names:

SELECT 
    NODE,
    EVENTID,
    TYPE,
    FIRSTOCCURRENCE,
    LASTOCCURRENCE,
    -- Convert varchar dates to datetime (style 101 = MM/DD/YYYY) then calculate minutes
    DATEDIFF(MINUTE, CONVERT(DATETIME, FIRSTOCCURRENCE, 101), CONVERT(DATETIME, LASTOCCURRENCE, 101)) AS [Outage in MIN],
    TICKETNUMBER,
    TALLY
FROM Test_Table
-- Filter to records from the last 24 hours (convert first to compare dates correctly)
WHERE CONVERT(DATETIME, FIRSTOCCURRENCE, 101) >= DATEADD(HOUR, -24, GETDATE())

Step 2: Group by Node to Aggregate Outage Data #

If you want to summarize outages per node (like total downtime, number of outages, etc.), use aggregate functions with GROUP BY. Here's an example that calculates total outage minutes, number of outages, and the latest outage time per node:

SELECT 
    NODE,
    -- Total outage minutes across all events for the node
    SUM(DATEDIFF(MINUTE, CONVERT(DATETIME, FIRSTOCCURRENCE, 101), CONVERT(DATETIME, LASTOCCURRENCE, 101))) AS Total_Outage_Minutes,
    -- Number of outage events for the node
    COUNT(*) AS Outage_Count,
    -- Most recent outage end time
    MAX(CONVERT(DATETIME, LASTOCCURRENCE, 101)) AS Latest_Outage_Time,
    -- Most recent outage type (adjust aggregate function if you need a different metric)
    MAX(TYPE) AS Most_Recent_Outage_Type
FROM Test_Table
WHERE CONVERT(DATETIME, FIRSTOCCURRENCE, 101) >= DATEADD(HOUR, -24, GETDATE())
GROUP BY NODE
-- Optional: Sort by total downtime to see worst-affected nodes first
ORDER BY Total_Outage_Minutes DESC;

Key Notes for SQL Beginners #

• Always match field names exactly: SQL is case-insensitive in most environments, but spaces and typos will break your query.
• Convert string dates to datetime: Storing dates as VARCHAR is not ideal, but if you have to work with it, use CONVERT with the correct style code (101 for MM/DD/YYYY in your case).
• GROUP BY requires consistency: Any column in SELECT that isn't wrapped in an aggregate function (like SUM, MAX, COUNT) must be in the GROUP BY clause.

内容的提问来源于stack exchange，提问作者JarJArB