SQL 2005 - Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Please Note, the SQL is handled dynamically by the Server, therefore a lot of what's in my WHERE clause will look odd to you. Please disregard this. I am getting the below mentioned error on my sub-query's and need help.

Server: Msg 512, Level 16, State 1, Line 90
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Server: Msg 512, Level 16, State 1, Line 198
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
/* Montly Revenue Analysis */
SET NOCOUNT ON
 
/*********Determine the beginning of the fiscal year***************************/
DECLARE @FiscalYearMonth INT
DECLARE @RequestedMonth INT
DECLARE @FiscalYearStarts DATETIME
 
SELECT
    @FiscalYearMonth = 1
SELECT
    @FiscalYearMonth = ISNULL(otherlong , 1)
FROM
    medlists
WHERE
    tablename = 'fiscalyear'
SELECT
    @RequestedMonth = MONTH(CONVERT(DATETIME , '4/01/2009'))
 
IF @FiscalYearMonth <= @RequestedMonth 
   BEGIN
         SELECT
            @FiscalYearStarts = CONVERT(VARCHAR , @FiscalYearMonth) + '/1/' + CONVERT(VARCHAR , YEAR(CONVERT(DATETIME , '4/01/2009')))
   END
ELSE 
   BEGIN
         SELECT
            @FiscalYearStarts = CONVERT(DATETIME , CONVERT(VARCHAR , @FiscalYearMonth) + '/1/' + CONVERT(VARCHAR , YEAR(CONVERT(DATETIME , '4/01/2009')) - 1))
   END
/******************************************************************************/
 
 
CREATE TABLE #A
       (
         PatientvisitprocsId INT NULL ,
         Code VARCHAR(10) NULL ,
         CPTCode VARCHAR(10) NULL ,
         TotalCharge MONEY NULL ,
         TotalPayment MONEY NULL ,
         TotalAdjustment MONEY NULL ,
         Month INT NULL ,
         Department VARCHAR(255) NULL ,
         FinancialClass VARCHAR(255) NULL ,
         Doctor VARCHAR(255) NULL ,
         Facility VARCHAR(255) NULL ,
         Resource VARCHAR(255) NULL ,
         Company VARCHAR(255) NULL ,
         Description VARCHAR(255) NULL ,
         MonthCount INT NULL
       )
 
CREATE TABLE #B
       (
         PatientvisitprocsId INT NULL ,
         Code VARCHAR(10) NULL ,
         CPTCode VARCHAR(10) NULL ,
         TotalCharge MONEY NULL ,
         TotalPayment MONEY NULL ,
         TotalAdjustment MONEY NULL ,
         Month INT NULL ,
         YearCount INT NULL ,
         Department VARCHAR(255) NULL ,
         FinancialClass VARCHAR(255) NULL ,
         Doctor VARCHAR(255) NULL ,
         Facility VARCHAR(255) NULL ,
         Resource VARCHAR(255) NULL ,
         Company VARCHAR(255) NULL ,
         Description VARCHAR(255) NULL ,
         MTD MONEY NULL ,
         MTDCharge MONEY NULL ,
         MTDAdj MONEY NULL ,
         MonthCount INT NULL
       )
 
CREATE TABLE #C
       (
         CPTCode VARCHAR(10) NULL ,
         Month INT NULL ,
         MTD MONEY NULL ,
         MTDCharge MONEY NULL ,
         MTDAdj MONEY NULL ,
         MonthCount INT NULL ,
         YTD MONEY NULL ,
         YTDCharge MONEY NULL ,
         YTDAdj MONEY NULL ,
         YearCount INT NULL ,
         Department VARCHAR(255) NULL ,
         FinancialClass VARCHAR(255) NULL ,
         Doctor VARCHAR(255) NULL ,
         Facility VARCHAR(255) NULL ,
         Resource VARCHAR(255) NULL ,
         Company VARCHAR(255) NULL ,
         Description VARCHAR(255) NULL ,
         Code VARCHAR(10) NULL
       )
 
--MONTH TOTALS
 
INSERT
    #A
    SELECT
        pvp.patientvisitprocsid ,
        pvp.Code ,
        pvp.CPTCode ,
	---- ISNULL(pvp.Fee , 0) AS TotalCharge ,
        ISNULL((
                 SELECT
                    pvp1.Fee
                 FROM
                    PatientVisitProcs pvp1
                    INNER JOIN PatientVisit pv1 ON pvp1.PatientVisitId = pv1.PatientVisitId
                 WHERE
                    (
                      pv1.Entered < DATEADD(month , 1 , '4/01/2009')
                      AND pv1.Entered >= '4/01/2009'
                    )
                    AND pvp1.PatientVisitId = pvp.PatientVisitId /* the innner PVP to the outer PVP */
                    AND pv1.PatientVisitId = pv.PatientVisitId /* the innner PV to the outer PV */
               ) , 0) AS TotalCharge ,
        SUM(CASE WHEN t.Action = 'P' THEN td.Amount
                 ELSE 0
            END) AS TotalPayment ,
        SUM(CASE WHEN t.Action = 'A' THEN td.Amount
                 ELSE 0
            END) AS TotalAdjustment ,
        MONTH(pm.DateOfEntry) AS Month ,
        ISNULL(dept.Description , 'Unspecified') AS Department ,
        ISNULL(fc.Description , 'Unspecified') AS FinancialClass ,
        doc.ListName AS Doctor ,
        fac.ListName AS Facility ,
        ISNULL(dffr.ListName , 'Unspecified') AS Resource ,
        co.ListName AS Company ,
        prc.Description ,
        MonthCount = 1
    FROM
        PatientVisitProcs pvp
        INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
        INNER JOIN Procedures prc ON pvp.ProceduresId = prc.ProceduresId
        LEFT JOIN MedLists dept ON prc.DepartmentMId = dept.MedListsId
        INNER JOIN TransactionDistributions td ON pvp.PatientVisitProcsId = td.PatientVisitProcsId
        INNER JOIN Transactions t ON t.TransactionsId = td.TransactionsId
        INNER JOIN VisitTransactions vt ON t.VisitTransactionsId = vt.VisitTransactionsId
        INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
                                       AND (
                                             pm.InsuranceTransfer IS NULL
                                             OR pm.InsuranceTransfer = 0
                                           )
        LEFT JOIN MedLists fc ON pv.FinancialClassMId = fc.MedListsId
        INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
        INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
        INNER JOIN DoctorFacility co ON pv.CompanyId = co.DoctorFacilityId
        LEFT OUTER JOIN PatientVisitResource pvr ON pv.PatientVisitId = pvr.PatientVisitId
                                                    AND 1 <> 1
        LEFT OUTER JOIN DoctorFacility dffr ON pvr.ResourceId = dffr.DoctorFacilityId
    WHERE
        --Filter on Department
        (
          (
            NULL IS NOT NULL
            AND prc.DepartmentMId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on financial class
        (
          (
            NULL IS NOT NULL
            AND pv.FinancialClassMId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Doctor
        (
          (
            NULL IS NOT NULL
            AND pv.DoctorId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on resource
        (
          (
            NULL IS NOT NULL
            AND pvr.ResourceId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on date
        (
          pm.DateOfEntry < DATEADD(month , 1 , '4/01/2009')
          AND pm.DateOfEntry >= '4/01/2009'
        )
    GROUP BY
        pvp.patientvisitprocsid ,
        pvp.Code ,
        pvp.CPTCode ,
        pvp.PatientVisitId ,
        pv.PatientVisitId ,
        pvp.Fee ,
        MONTH(pm.DateOfEntry) ,
        dept.Description ,
        fc.Description ,
        doc.ListName ,
        co.ListName ,
        dffr.ListName ,
        fac.ListName ,
        prc.Description
    ORDER BY
        pvp.Code
 
--YEAR TOTALS
 
INSERT
    #B
    SELECT
        pvp.patientvisitprocsid ,
        pvp.Code ,
        pvp.CPTCode ,
	---ISNULL(pvp.Fee , 0) AS TotalCharge ,
        ISNULL((
                 SELECT
                    pvp1.Fee
                 FROM
                    PatientVisitProcs pvp1
                    INNER JOIN PatientVisit pv1 ON pvp1.PatientVisitId = pv1.PatientVisitId
                 WHERE
                    (
                      pv1.Entered < DATEADD(month , 1 , '4/01/2009')
                      AND pv1.Entered >= '4/01/2009'
                    )
                    AND pvp1.PatientVisitId = pvp.PatientVisitId /* the innner PVP to the outer PVP */
                    AND pv1.PatientVisitId = pv.PatientVisitId /* the innner PV to the outer PV */
               ) , 0) AS TotalCharge ,
        SUM(CASE WHEN t.Action = 'P' THEN td.Amount
                 ELSE 0
            END) AS TotalPayment ,
        SUM(CASE WHEN t.Action = 'A' THEN td.Amount
                 ELSE 0
            END) AS TotalAdjustment ,
        MONTH('4/01/2009') AS Month ,
        YearCount = 1 ,
        ISNULL(dept.Description , 'Unspecified') AS Department ,
        ISNULL(fc.Description , 'Unspecified') AS FinancialClass ,
        doc.ListName AS Doctor ,
        fac.ListName AS Facility ,
        ISNULL(dffr.ListName , 'Unspecified') AS Resource ,
        co.ListName AS Company ,
        prc.Description ,
        0 ,
        0 ,
        0 ,
        0
    FROM
        PatientVisitProcs pvp
        INNER JOIN Procedures prc ON pvp.ProceduresId = prc.ProceduresId
        LEFT JOIN MedLists dept ON prc.DepartmentMId = dept.MedListsId
        INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
        LEFT JOIN MedLists fc ON pv.FinancialClassMId = fc.MedListsId
        INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
        INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
        INNER JOIN DoctorFacility co ON pv.CompanyId = co.DoctorFacilityId
        INNER JOIN TransactionDistributions td ON pvp.PatientVisitProcsId = td.PatientVisitProcsId
        INNER JOIN Transactions t ON t.TransactionsId = td.TransactionsId
        INNER JOIN VisitTransactions vt ON t.VisitTransactionsId = vt.VisitTransactionsId
        INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
                                       AND (
                                             pm.InsuranceTransfer IS NULL
                                             OR pm.InsuranceTransfer = 0
                                           )
        LEFT OUTER JOIN PatientVisitResource pvr ON pv.PatientVisitId = pvr.PatientVisitId
                                                    AND 1 <> 1
        LEFT OUTER JOIN DoctorFacility dffr ON pvr.ResourceId = dffr.DoctorFacilityId
    WHERE
        --Filter on Department
        (
          (
            NULL IS NOT NULL
            AND prc.DepartmentMId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on financial class
        (
          (
            NULL IS NOT NULL
            AND pv.FinancialClassMId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on Doctor
        (
          (
            NULL IS NOT NULL
            AND pv.DoctorId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on company
        (
          (
            NULL IS NOT NULL
            AND pv.CompanyId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on resource
        (
          (
            NULL IS NOT NULL
            AND pvr.ResourceId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND --Filter on date
        pm.DateOfEntry >= @FiscalYearStarts
        AND pm.DateOfEntry < DATEADD(month , 1 , '4/01/2009')
    GROUP BY
        pvp.patientvisitprocsid ,
        pvp.Code ,
        pvp.CPTCode ,
        pvp.PatientVisitID ,
        pv.PatientVisitId ,
        pvp.Fee ,
        dept.Description ,
        fc.Description ,
        doc.ListName ,
        co.ListName ,
        dffr.ListName ,
        fac.ListName ,
        prc.Description
    ORDER BY
        pvp.Code
 
 
--Merge month totals onto year totals
 
UPDATE
    #B
SET 
    MTD = a.TotalPayment ,
    MTDAdj = a.TotalAdjustment ,
    MTDCharge = a.TotalCharge ,
    MonthCount = a.MonthCount
FROM
    #A a
WHERE
    a.Code = #B.Code
    AND ISNULL(a.Department , '') = ISNULL(#B.Department , '')
    AND ISNULL(a.FinancialClass , '') = ISNULL(#B.FinancialClass , '')
    AND ISNULL(a.Doctor , '') = ISNULL(#B.Doctor , '')
    AND ISNULL(a.Facility , '') = ISNULL(#B.Facility , '')
    AND ISNULL(a.Resource , '') = ISNULL(#B.Resource , '')
    AND ISNULL(a.Company , '') = ISNULL(#B.Company , '')
    AND ISNULL(a.CPTCode , '') = ISNULL(#B.CPTCode , '')
    AND a.PatientvisitProcsId = #B.PatientvisitProcsId
 
--Remove items that have no occurences this month
 
DELETE
    #B
WHERE
    MonthCount = 0
    AND #B.CPTCode NOT IN ( SELECT
                                CPTCode
                            FROM
                                #A )
 
 
--insert into #C so that we can eliminate the ones that have MonthCount=0 even after grouping by.
--there is payment for code 99212 for doctor Casey in this fiscal year and for doctor Bailey this month. 
--So 99212 should not appear for Casey when you groupby doctor.
 
IF 'Department' = 'Department' 
   INSERT
    #C
    SELECT
        CPTCode ,
        Month ,
        SUM(MTD) AS MTD ,
        SUM(MTDCharge) AS MTDCharge ,
        SUM(MTDAdj) AS MTDAdj ,
        SUM(MonthCount) AS MonthCount ,
        SUM(TotalPayment) AS YTD ,
        SUM(TotalCharge) AS YTDCharge ,
        SUM(TotalAdjustment) AS YTDAdj ,
        SUM(YearCount) AS YearCount ,
        Department ,
        FinancialClass = CONVERT(VARCHAR(255) , NULL) ,
        Doctor = CONVERT(VARCHAR(255) , NULL) ,
        Facility = CONVERT(VARCHAR(255) , NULL) ,
        Resource = CONVERT(VARCHAR(255) , NULL) ,
        Company = CONVERT(VARCHAR(255) , NULL) ,
        Description = Description ,
        Code = Code
    FROM
        #B
    GROUP BY
        CPTCode ,
        Month ,
        Description ,
        Department ,
        Code
    ORDER BY
        Code
 
IF 'Department' = 'Financial Class' 
   INSERT
    #C
    SELECT
        CPTCode ,
        Month ,
        SUM(MTD) AS MTD ,
        SUM(MTDCharge) AS MTDCharge ,
        SUM(MTDAdj) AS MTDAdj ,
        SUM(MonthCount) AS MonthCount ,
        SUM(TotalPayment) AS YTD ,
        SUM(TotalCharge) AS YTDCharge ,
        SUM(TotalAdjustment) AS YTDAdj ,
        SUM(YearCount) AS YearCount ,
        Department = CONVERT(VARCHAR(255) , NULL) ,
        FinancialClass = FinancialClass ,
        Doctor = CONVERT(VARCHAR(255) , NULL) ,
        Facility = CONVERT(VARCHAR(255) , NULL) ,
        Resource = CONVERT(VARCHAR(255) , NULL) ,
        Company = CONVERT(VARCHAR(255) , NULL) ,
        Description = Description ,
        Code = Code
    FROM
        #B
    GROUP BY
        CPTCode ,
        Month ,
        Description ,
        FinancialClass ,
        Code
    ORDER BY
        Code
 
IF 'Department' = 'Doctor' 
   INSERT
    #C
    SELECT
        CPTCode ,
        Month ,
        SUM(MTD) AS MTD ,
        SUM(MTDCharge) AS MTDCharge ,
        SUM(MTDAdj) AS MTDAdj ,
        SUM(MonthCount) AS MonthCount ,
        SUM(TotalPayment) AS YTD ,
        SUM(TotalCharge) AS YTDCharge ,
        SUM(TotalAdjustment) AS YTDAdj ,
        SUM(YearCount) AS YearCount ,
        Department = CONVERT(VARCHAR(255) , NULL) ,
        FinancialClass = CONVERT(VARCHAR(255) , NULL) ,
        Doctor = Doctor ,
        Facility = CONVERT(VARCHAR(255) , NULL) ,
        Resource = CONVERT(VARCHAR(255) , NULL) ,
        Company = CONVERT(VARCHAR(255) , NULL) ,
        Description = Description ,
        Code = Code
    FROM
        #B
    GROUP BY
        CPTCode ,
        Month ,
        Description ,
        Doctor ,
        Code
    ORDER BY
        Code
 
IF 'Department' = 'Facility' 
   INSERT
    #C
    SELECT
        CPTCode ,
        Month ,
        SUM(MTD) AS MTD ,
        SUM(MTDCharge) AS MTDCharge ,
        SUM(MTDAdj) AS MTDAdj ,
        SUM(MonthCount) AS MonthCount ,
        SUM(TotalPayment) AS YTD ,
        SUM(TotalCharge) AS YTDCharge ,
        SUM(TotalAdjustment) AS YTDAdj ,
        SUM(YearCount) AS YearCount ,
        Department = CONVERT(VARCHAR(255) , NULL) ,
        FinancialClass = CONVERT(VARCHAR(255) , NULL) ,
        Doctor = CONVERT(VARCHAR(255) , NULL) ,
        Facility = Facility ,
        Resource = CONVERT(VARCHAR(255) , NULL) ,
        Company = CONVERT(VARCHAR(255) , NULL) ,
        Description = Description ,
        Code = Code
    FROM
        #B
    GROUP BY
        CPTCode ,
        Month ,
        Description ,
        Facility ,
        Code
    ORDER BY
        Code
 
IF 'Department' = 'Company' 
   INSERT
    #C
    SELECT
        CPTCode ,
        Month ,
        SUM(MTD) AS MTD ,
        SUM(MTDCharge) AS MTDCharge ,
        SUM(MTDAdj) AS MTDAdj ,
        SUM(MonthCount) AS MonthCount ,
        SUM(TotalPayment) AS YTD ,
        SUM(TotalCharge) AS YTDCharge ,
        SUM(TotalAdjustment) AS YTDAdj ,
        SUM(YearCount) AS YearCount ,
        Department = CONVERT(VARCHAR(255) , NULL) ,
        FinancialClass = CONVERT(VARCHAR(255) , NULL) ,
        Doctor = CONVERT(VARCHAR(255) , NULL) ,
        Facility = CONVERT(VARCHAR(255) , NULL) ,
        Resource = CONVERT(VARCHAR(255) , NULL) ,
        Company = Company ,
        Description = Description ,
        Code = Code
    FROM
        #B
    GROUP BY
        CPTCode ,
        Month ,
        Description ,
        Company ,
        Code
    ORDER BY
        Code
 
IF 'Department' = 'Resource' 
   INSERT
    #C
    SELECT
        CPTCode ,
        Month ,
        SUM(MTD) AS MTD ,
        SUM(MTDCharge) AS MTDCharge ,
        SUM(MTDAdj) AS MTDAdj ,
        SUM(MonthCount) AS MonthCount ,
        SUM(TotalPayment) AS YTD ,
        SUM(TotalCharge) AS YTDCharge ,
        SUM(TotalAdjustment) AS YTDAdj ,
        SUM(YearCount) AS YearCount ,
        Department = CONVERT(VARCHAR(255) , NULL) ,
        FinancialClass = CONVERT(VARCHAR(255) , NULL) ,
        Doctor = CONVERT(VARCHAR(255) , NULL) ,
        Facility = CONVERT(VARCHAR(255) , NULL) ,
        Resource = Resource ,
        Company = CONVERT(VARCHAR(255) , NULL) ,
        Description = Description ,
        Code = Code
    FROM
        #B
    GROUP BY
        CPTCode ,
        Month ,
        Description ,
        Resource ,
        Code
    ORDER BY
        Code
 
SELECT
    *
FROM
    #C
WHERE
    MonthCount >= 0
 
DROP TABLE #A
DROP TABLE #B
DROP TABLE #C

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
ezraaConnect With a Mentor Commented:
You have an inner query:

SELECT
                    pvp1.Fee
                 FROM
                    PatientVisitProcs pvp1

(line 237)

that may return more that one result.  It looks like you need a sum of this field:

SELECT
                    sum(pvp1.Fee)
                 FROM
                    PatientVisitProcs pvp1


0
 
ezraaCommented:
Also, same for line 107 (Both the year counts and the month counts)
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
I think, the following is returning more than a value

(
                 SELECT
                    pvp1.Fee
                 FROM
                    PatientVisitProcs pvp1
                    INNER JOIN PatientVisit pv1 ON pvp1.PatientVisitId = pv1.PatientVisitId
                 WHERE
                    (
                      pv1.Entered < DATEADD(month , 1 , '4/01/2009')
                      AND pv1.Entered >= '4/01/2009'
                    )
                    AND pvp1.PatientVisitId = pvp.PatientVisitId /* the innner PVP to the outer PVP */
                    AND pv1.PatientVisitId = pv.PatientVisitId /* the innner PV to the outer PV */
               )


How you need to handle if it returns more than a value, should it be the SUM or the MAX of those values ?
0
 
Jeff SAuthor Commented:
Split points to be fair. Great explanantion. Thanks again!!!!
0
All Courses

From novice to tech pro — start learning today.