Solved

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.

Posted on 2009-05-11
4
1,260 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Jeff S
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
ezraa earned 250 total points
Comment Utility
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
 
LVL 9

Expert Comment

by:ezraa
Comment Utility
Also, same for line 107 (Both the year counts and the month counts)
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
Comment Utility
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
 
LVL 7

Author Closing Comment

by:Jeff S
Comment Utility
Split points to be fair. Great explanantion. Thanks again!!!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now