SQL条件求和问题求助：按日期日份拆分con_Amount至D01-D31列

Hey there! Let's work through the error you're hitting with your SUM(CASE WHEN...) approach to pivot daily con_Amount totals into columns D01 through D31. I'll walk through common pitfalls and share a corrected example to get you up and running.

Common Causes of Errors #

First, let's cover the most likely issues that could be breaking your code:

• Incomplete CASE Statements: Forgetting the END keyword or an ELSE branch (missing END will throw a syntax error immediately).
• Database-Specific Date Function Mismatch: DATEPART(DAY, ...) works in SQL Server, but other databases use different syntax (e.g., DAY() in MySQL, EXTRACT(DAY FROM ...) in PostgreSQL). Using the wrong function for your DB will cause errors.
• Data Type Mismatches: If con_Amount is stored as a string instead of a numeric type, trying to SUM it will fail—you'll need to cast it first.
• Missing Grouping Logic: If you don't group by the year/month of Con_PubDate, you'll get a single row for all dates, which might not be what you want, and could lead to unexpected results or errors if other columns are included.
• Unfiltered NULL Dates: If Con_PubDate has NULL values, DATEPART will return NULL, and those rows won't be counted in any Dxx column—this might not throw an error, but it will skew your totals.

Corrected Example Code #

Here's a solid, error-resistant version of the stored procedure tailored for SQL Server (adjust the date function if you're using a different database):

CREATE PROCEDURE GetDailyAmountPivot
AS
BEGIN
    -- Suppress row count messages for cleaner output
    SET NOCOUNT ON;

    SELECT
        -- Group by year and month to keep daily totals per calendar month
        YEAR(Con_PubDate) AS PublicationYear,
        MONTH(Con_PubDate) AS PublicationMonth,
        -- Daily totals for each day of the month
        SUM(CASE WHEN DATEPART(DAY, Con_PubDate) = 1 THEN con_Amount ELSE 0 END) AS D01,
        SUM(CASE WHEN DATEPART(DAY, Con_PubDate) = 2 THEN con_Amount ELSE 0 END) AS D02,
        SUM(CASE WHEN DATEPART(DAY, Con_PubDate) = 3 THEN con_Amount ELSE 0 END) AS D03,
        -- Continue this pattern up to day 31
        SUM(CASE WHEN DATEPART(DAY, Con_PubDate) = 31 THEN con_Amount ELSE 0 END) AS D31
    FROM YourTableName -- Replace with your actual table name
    WHERE Con_PubDate IS NOT NULL -- Filter out invalid NULL dates
    GROUP BY YEAR(Con_PubDate), MONTH(Con_PubDate)
    ORDER BY PublicationYear, PublicationMonth;
END

Fixes for Specific Scenarios #

• If you're using MySQL: Replace DATEPART(DAY, Con_PubDate) with DAY(Con_PubDate)
• If con_Amount is a string type: Cast it to a numeric type inside the CASE statement, like:

  SUM(CASE WHEN DATEPART(DAY, Con_PubDate) = 1 THEN CAST(con_Amount AS DECIMAL(18,2)) ELSE 0 END) AS D01
  

• If you need a single row for all dates: Remove the YEAR/MONTH columns and the GROUP BY clause (but this will aggregate all dates into one set of Dxx totals).

If You're Still Getting Errors #

If you run into a specific error message (like syntax errors, data type conflicts, etc.), share the exact error text and a snippet of your actual code—this will help narrow down the issue even faster.

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