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,269 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
ID: 24356728
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
ID: 24356736
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
ID: 24356739
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
ID: 31580231
Split points to be fair. Great explanantion. Thanks again!!!!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

948 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

20 Experts available now in Live!

Get 1:1 Help Now