I currently work as a Healthcare Analyst. I have been using Microsoft Access the past couple years for querying and generating reports. My company now wants me to learn, expand my SQL knowledge and use SQL Server2008 daily going forward.

I have made some progress understanding the differences between Access and SQL, but they are still many things I'm unfamiliar with.

I would like some insight on sub queries, and how to use SUMIF in SQL. Below I have pasted step (1) the first part of a SQL sub query I have put together. Step(2) is the SQL view from Microsoft Access that I want to translate into SQL, but some insight is needed on the proper syntax for a SUMIF statement.

Any insight is greatly appreciated...

Step (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]
sql sql-server-2008
Edit
Report