sql - 在SQL Server 2008中使用Sumif

Question

我目前担任医疗保健分析师。在过去的几年中，我一直在使用Microsoft Access来查询和生成报告。我的公司现在希望我每天学习，扩展我的SQL知识并每天使用SQL Server2008。

我在理解Access和SQL之间的差异方面取得了一些进展，但是它们仍然是我不熟悉的许多事情。

我想对子查询以及如何在SQL中使用SUMIF有所了解。在下面，我粘贴了步骤(1)，这是我放在一起的SQL子查询的第一部分。 步骤(2)是Microsoft Access 的SQL View ，我想将其转换为SQL，但是需要对SUMIF语句的正确语法有一些了解。

任何见解都将不胜感激...

步骤(1)SQL Server 2008:

SELECT
    [GROUPING_OF_CLINIC_ID].CLINIC_ID
,   OUTPAT_ACCT_REC.CHARGE_CLASS
,   [GROUPING_OF_CLINIC_ID].PATIENT_NUMBER
,   [GROUPING_OF_CLINIC_ID].REGISTRATION_CD
,   OUTPAT_REGISTER.PRIMARY_FINAN
,   REPLACE(CONVERT(varchar(10), OUTPAT_ACCT_REC.ENTRY_DATE, 101), '/', '') AS ENTRY_DATE
,   SUM(ENTRY_AMOUNT) AS ENTRY_AMOUNT
FROM
    OUTPAT_REGISTER
    LEFT JOIN 
        OUTPAT_BILL_REF
        ON 
        (
            OUTPAT_REGISTER.PATIENT_NUMBER = OUTPAT_BILL_REF.PATIENT_NUMBER
        )
            AND (OUTPAT_REGISTER.REGISTRATION_CD = OUTPAT_BILL_REF.REGISTRATION_CD)
    LEFT JOIN 
        OUTPAT_ACCT_REC
        ON 
        (
            OUTPAT_BILL_REF.PATIENT_NUMBER = OUTPAT_ACCT_REC.PATIENT_NUMBER
        )
            AND (OUTPAT_BILL_REF.BILL_REFERENCE = OUTPAT_ACCT_REC.REFERENCE_NUM)
    LEFT JOIN
        (
            SELECT
                CLINIC_ID
            ,   PATIENT_NUMBER
            ,   REGISTRATION_CD
            FROM
                OP_VISIT_HISTRY
            GROUP BY
                CLINIC_ID
            ,   PATIENT_NUMBER
            ,   REGISTRATION_CD
            --ORDER BY CLINIC_ID
        ) AS [GROUPING_OF_CLINIC_ID]
        ON 
        (
            OUTPAT_REGISTER.PATIENT_NUMBER = [GROUPING_OF_CLINIC_ID].PATIENT_NUMBER
        )
            AND (OUTPAT_REGISTER.REGISTRATION_CD = [GROUPING_OF_CLINIC_ID].REGISTRATION_CD)
GROUP BY
    [GROUPING_OF_CLINIC_ID].CLINIC_ID
,   OUTPAT_ACCT_REC.CHARGE_CLASS
,   [GROUPING_OF_CLINIC_ID].PATIENT_NUMBER
,   [GROUPING_OF_CLINIC_ID].REGISTRATION_CD
,   OUTPAT_ACCT_REC.ENTRY_DATE
,   OUTPAT_REGISTER.PRIMARY_FINAN
HAVING
    OUTPAT_ACCT_REC.ENTRY_DATE > '12/31/2010'

步骤(2)来自SQL View 中的Access:

SELECT [Non Recurring Clinic Step1].CLINIC_ID, dbo_HR_FINAN_CLASS.SUMMARY_CATGRY, [TABLE LAYOUT].ENTRY_TYPE, 
Sum(IIf([entry_date] Between #12/1/2012# And #12/31/2012#,[sumofentry_amount],0)) AS [Dec'12], Sum(IIf([entry_date] Between #11/1/2012# And #11/30/2012#,[sumofentry_amount],0)) AS [Nov'12], Sum(IIf([entry_date] Between #10/1/2012# And #10/31/2012#,[sumofentry_amount],0)) AS [Oct'12], Sum(IIf([entry_date] Between #9/1/2012# And #9/30/2012#,[sumofentry_amount],0)) AS [Sept'12], Sum(IIf([entry_date] Between #8/1/2012# And #8/31/2012#,[sumofentry_amount],0)) AS [Aug'12], Sum(IIf([entry_date] Between #7/1/2012# And #7/31/2012#,[sumofentry_amount],0)) AS [Jul'12], Sum(IIf([entry_date] Between #6/1/2012# And #6/30/2012#,[sumofentry_amount],0)) AS [Jun'12], Sum(IIf([entry_date] Between #5/1/2012# And #5/31/2012#,[sumofentry_amount],0)) AS [May'12], Sum(IIf([entry_date] Between #4/1/2012# And #4/30/2012#,[sumofentry_amount],0)) AS [Apr'12], Sum(IIf([entry_date] Between #3/1/2012# And #3/31/2012#,[sumofentry_amount],0)) AS [Mar'12], Sum(IIf([entry_date] Between #2/1/2012# And #2/29/2012#,[sumofentry_amount],0)) AS [Feb'12], Sum(IIf([entry_date] Between #1/1/2012# And #1/31/2012#,[sumofentry_amount],0)) AS [Jan'12], Sum(IIf([entry_date] Between #12/1/2011# And #12/31/2011#,[sumofentry_amount],0)) AS [Dec'11], Sum(IIf([entry_date] Between #1/1/2012# And #12/31/2012#,[sumofentry_amount],0)) AS 2012YTD, Sum(IIf([entry_date] Between #1/1/2011# And #12/31/2011#,[sumofentry_amount],0)) AS 2011YTD
FROM [TABLE LAYOUT] INNER JOIN ([Non Recurring Clinic Step1] INNER JOIN ((dbo_OUTPAT_REGISTER INNER JOIN dbo_HR_FINAN_CLASS ON dbo_OUTPAT_REGISTER.PRIMARY_FINAN = dbo_HR_FINAN_CLASS.FINAN_CLASS_CD) INNER JOIN dbo_OUTPAT_AREA_CDS ON dbo_OUTPAT_REGISTER.REGISTER_AREA = dbo_OUTPAT_AREA_CDS.REGISTER_AREA) ON ([Non Recurring Clinic Step1].REGISTRATION_CD = dbo_OUTPAT_REGISTER.REGISTRATION_CD) AND ([Non Recurring Clinic Step1].PATIENT_NUMBER = dbo_OUTPAT_REGISTER.PATIENT_NUMBER)) ON [TABLE LAYOUT].ENTRY_CLASS = [Non Recurring Clinic Step1].CHARGE_CLASS
WHERE (((dbo_OUTPAT_AREA_CDS.REG_AREA_TYPE)<>"RE"))
GROUP BY [Non Recurring Clinic Step1].CLINIC_ID, dbo_HR_FINAN_CLASS.SUMMARY_CATGRY, [TABLE LAYOUT].ENTRY_TYPE
ORDER BY [Non Recurring Clinic Step1].CLINIC_ID;

Answer 1

SUM(IIF())等效于使用带有CASE表达式的聚合:

sum(case 
        when [entry_date] >= '2012-12-01' and [entry_date] <= '2012-12-31' 
        then [sumofentry_amount] 
        else 0 
    end) AS [Dec'12]

然后，您将根据需要在其他列中重复此操作。
CASE和聚合函数将数据从行值转换为列。在SQL Server 2005+中，您可以实现 PIVOT 函数，但也可以使用类似于以下的内容:

select CLINIC_ID,
    SUMMARY_CATGRY,
    ENTRY_TYPE,
    SUM(case when EntryMonth = 12 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Dec12],
    SUM(case when EntryMonth = 11 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Nov12],
    SUM(case when EntryMonth = 10 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Oct12],
    SUM(case when EntryMonth = 9 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Sep12],
    SUM(case when EntryMonth = 8 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Aug12],
    SUM(case when EntryMonth = 7 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jul12],
    SUM(case when EntryMonth = 6 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jun12],
    SUM(case when EntryMonth = 5 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [May12],
    SUM(case when EntryMonth = 4 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Apr12],
    SUM(case when EntryMonth = 3 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Mar12],
    SUM(case when EntryMonth = 2 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Feb12],
    SUM(case when EntryMonth = 1 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jan12],
    SUM(case when EntryYear = 2012 then [sumofentry_amount] else 0 end) [2012YTD],
    SUM(case when EntryYear = 2011 then [sumofentry_amount] else 0 end) [2011YTD]
from
(
    SELECT [Non Recurring Clinic Step1].CLINIC_ID, 
        dbo_HR_FINAN_CLASS.SUMMARY_CATGRY, 
        [TABLE LAYOUT].ENTRY_TYPE, 
        datepart(month,[entry_date]) EntryMonth,
        datepart(year,[entry_date]) EntryYear,
        [sumofentry_amount],
    FROM <yourtables and joins go here>
    WHERE dbo_OUTPAT_AREA_CDS.REG_AREA_TYPE)<>'RE'
        and datepart(year,[entry_date]) in (2011, 2012)
) src
group by CLINIC_ID, SUMMARY_CATGRY, ENTRY_TYPE