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 SQL server, therefore some items in my WHERE clause will look odd to you. Please disregard.
I am getting the following error. Any help is appreciated.
Msg 512, Level 16, State 1, Line 282
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.
I am getting the following error. Any help is appreciated.
Msg 512, Level 16, State 1, Line 282
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.
SET NOCOUNT ON
DECLARE @ResTypeID INT
SELECT
@ResTypeID = medlistsID
FROM
medlists
WHERE
tablename = 'resourcetypes'
AND description = 'Reporting'
DECLARE
@startdate DATETIME ,
@enddate DATETIME ,
@groupby1 VARCHAR(20) ,
@groupby2 VARCHAR(20) ,
@groupby3 VARCHAR(20)
IF 1 = 1
BEGIN -- Date mode
SET @startdate = ISNULL(NULL , '1/1/1900')
SET @enddate = DATEADD(DAY , 1 , ISNULL(NULL , '1/1/3000'))
END
ELSE
BEGIN --month mode
SET @startdate = ISNULL(NULL , '1/1/1900')
SET @enddate = DATEADD(month , 1 , ISNULL(NULL , '1/1/3000'))
END
SET @groupby1 = CONVERT(VARCHAR(20) , 'Company')
SET @groupby2 = CONVERT(VARCHAR(20) , 'None')
SET @groupby3 = CONVERT(VARCHAR(20) , 'None')
--load the charges
SELECT
pv.ticketnumber ,
pv.patientvisitID ,
dr.listname AS ProviderName ,
dr.doctorfacilityID AS DoctorID ,
ISNULL(ref.Listname , 'No Referring Phys') AS ReferringDocName ,
0 AS ResourceID ,
fac.listname AS FacilityName ,
comp.listname AS CompanyName ,
p.cptcode ,
p.code ,
ISNULL(pvd.Code , '') AS Diag1 ,
ISNULL(pvd1.Code , '') AS Diag2 ,
ISNULL(pvd2.Code , '') AS Diag3 ,
ISNULL(pvd3.Code , '') AS Diag4 ,
ISNULL(pvd4.Code , '') AS Diag5 ,
ISNULL(pvd5.Code , '') AS Diag6 ,
ISNULL(pvd6.Code , '') AS Diag7 ,
ISNULL(pvd7.Code , '') AS Diag8 ,
ISNULL(mod1.code , '') + ISNULL(mod2.code , '') + ISNULL(mod3.code , '') + ISNULL(mod4.code , '') AS Modifier ,
ISNULL(p.description , 'No Description') AS CPTDescription ,
--pp.last+', '+pp.first+' '+Isnull(pp.middle,'') as PatientName,
ISNULL(dept.description , 'No Dept') AS Department ,
pvp.totalfee AS Charges ,
CAST('0.00' AS MONEY) AS Payments ,
CAST('0.00' AS MONEY) AS Adjustments ,
pvp.dateofentry AS DateOfEntry ,
pvp.units AS Units ,
pvp.patientvisitprocsID ,
pv.Visit AS DateOfService ,
ISNULL(fc.description , 'No Financial Class') AS FinancialClass ,
ISNULL(ic.listname , 'No Insurance') AS CarrierName ,
ISNULL(pi.InsuredId , '') AS InsuredId ,
ISNULL(ig.name , 'No Ins Group') AS InsGroupName ,
CASE @groupby1
WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
WHEN 'Facility' THEN fac.listname
WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
WHEN 'Provider' THEN dr.listname
WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
ELSE 'None'
END AS Group1 ,
CASE @groupby2
WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
WHEN 'Facility' THEN fac.listname
WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
WHEN 'Provider' THEN dr.listname
WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
ELSE fac.listname
END AS Group2 ,
CASE @groupby3
WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
WHEN 'Facility' THEN fac.listname
WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
WHEN 'Provider' THEN dr.listname
WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
ELSE fac.listname
END AS Group3
INTO
#tbl_temp
FROM
patientvisit pv
INNER JOIN patientprofile pp ON pp.patientprofileID = pv.patientProfileID
LEFT JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID
AND (
pi.Inactive IS NULL
OR pi.Inactive = 0
)
AND pi.OrderForClaims = 1
INNER JOIN patientvisitprocs pvp ON pv.patientvisitID = pvp.patientvisitID
INNER JOIN batch b ON pvp.batchID = b.batchID
INNER JOIN patientvisitprocsagg pvpa ON pvp.patientvisitprocsID = pvpa.patientvisitprocsID
INNER JOIN procedures p ON pvp.proceduresID = p.proceduresID
LEFT JOIN insurancecarriers ic ON pv.primaryinsurancecarriersID = ic.insurancecarriersID
LEFT JOIN insurancegroup ig ON ic.insurancegroupID = ig.insurancegroupID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'department'
) dept ON p.departmentMID = dept.medlistsID
INNER JOIN (
SELECT * FROM doctorfacility WHERE type = 1 OR type = 7
) dr ON pv.doctorID = dr.doctorfacilityID
INNER JOIN (
SELECT * FROM doctorfacility WHERE type = 2
) fac ON pv.facilityID = fac.doctorfacilityID
INNER JOIN (
SELECT * FROM doctorfacility WHERE type = 5
) comp ON pv.companyID = comp.doctorfacilityID
LEFT JOIN (
SELECT * FROM doctorfacility WHERE type = 3
) ref ON pv.referringdoctorID = ref.doctorfacilityID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'FinancialClass'
) fc ON pv.financialclassMID = fc.medlistsID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'Modifiers'
) mod1 ON pvp.Modifier1MID = mod1.medlistsID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'Modifiers'
) mod2 ON pvp.Modifier2MID = mod2.medlistsID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'Modifiers'
) mod3 ON pvp.Modifier3MID = mod3.medlistsID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'Modifiers'
) mod4 ON pvp.Modifier4MID = mod4.medlistsID
LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
AND pvp.PatientVisitDiags1 = pvd.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId
AND pvp.PatientVisitDiags2 = pvd1.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId
AND pvp.PatientVisitDiags3 = pvd2.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId
AND pvp.PatientVisitDiags4 = pvd3.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId
AND pvp.PatientVisitDiags5 = pvd4.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId
AND pvp.PatientVisitDiags6 = pvd5.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId
AND pvp.PatientVisitDiags7 = pvd6.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId
AND pvp.PatientVisitDiags8 = pvd7.ListOrder
WHERE
b.Entry >= @startdate
AND b.Entry < @enddate
AND --Filter on Carrier
(
(
NULL IS NOT NULL
AND pv.primaryinsurancecarriersID 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 Department
(
(
NULL IS NOT NULL
AND p.DepartmentMId 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 Financial Class
(
(
NULL IS NOT NULL
AND pv.FinancialClassMID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Ins Group
(
(
NULL IS NOT NULL
AND ic.insuranceGroupID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Procedure
(
(
NULL IS NOT NULL
AND pvp.ProceduresId IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Provider
(
(
NULL IS NOT NULL
AND pv.DoctorID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Referring Doc
(
(
NULL IS NOT NULL
AND pv.ReferringDoctorID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Diagnosis
(
(
NULL IS NOT NULL
AND pvd.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd1.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd2.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd3.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd4.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd5.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd6.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd7.DiagnosisId IN ( NULL )
)
OR ( NULL IS NULL )
)
--Insert rows with payments and adjustments
INSERT INTO
#tbl_temp
(
ticketnumber ,
patientvisitID ,
providername ,
doctorID ,
referringdocName ,
resourceID ,
facilityname ,
companyname ,
CPTCode ,
code ,
Diag1 ,
Diag2 ,
Diag3 ,
Diag4 ,
Diag5 ,
Diag6 ,
Diag7 ,
Diag8 ,
modifier ,
CPTDescription ,
department ,
charges ,
payments ,
adjustments ,
dateofentry ,
units ,
patientvisitprocsID ,
dateofservice ,
FinancialClass ,
CarrierName ,
InsuredId ,
InsGroupName ,
Group1 ,
Group2 ,
Group3
)
SELECT
pv.ticketnumber ,
pv.patientvisitID ,
dr.listname AS ProviderName ,
pv.doctorID AS DoctorID ,
ISNULL(ref.Listname , 'No Referring Phys') AS ReferringDocName ,
0 AS ResourceID ,
fac.listname AS FacilityName ,
comp.listname AS CompanyName ,
ISNULL(p.cptcode , 'Deposit') AS CPTCode ,
CASE tr.type
WHEN 'P' THEN ISNULL(p.code , 'Deposit')
ELSE ISNULL(p.code , 'None Adj')
END AS Code ,
ISNULL(pvd.Code , '') AS Diag1 ,
ISNULL(pvd1.Code , '') AS Diag2 ,
ISNULL(pvd2.Code , '') AS Diag3 ,
ISNULL(pvd3.Code , '') AS Diag4 ,
ISNULL(pvd4.Code , '') AS Diag5 ,
ISNULL(pvd5.Code , '') AS Diag6 ,
ISNULL(pvd6.Code , '') AS Diag7 ,
ISNULL(pvd7.Code , '') AS Diag8 ,
ISNULL(mod1.code , '') + ISNULL(mod2.code , '') + ISNULL(mod3.code , '') + ISNULL(mod4.code , '') AS Modifier ,
ISNULL(p.description , 'No Description') AS CPTDescription ,
--pp.last+', '+pp.first+' '+Isnull(pp.middle,'') as PatientName,
ISNULL(dept.description , 'No Dept') AS Department ,
0 AS Charges ,
CASE tr.type
WHEN 'P' THEN td.amount
ELSE 0
END AS Payments ,
CASE tr.type
WHEN 'P' THEN 0
ELSE td.amount
END AS Adjustments ,
b.entry AS DateOfEntry ,
0 AS Units ,
ISNULL(pvp.patientvisitprocsID , 0) AS patientvisitprocsID ,
pv.Visit AS DateOfService ,
ISNULL(fc.description , 'No Financial Class') AS FinancialClass ,
ISNULL(ic.listname , 'No Insurance') AS CarrierName ,
(
SELECT
ISNULL(pi.InsuredId , '')
FROM
PatientVisit pv2
LEFT JOIN PatientVisitInsurance pvi ON pv2.PatientVisitId = pvi.PatientVisitId
LEFT JOIN PatientInsurance pi ON pv2.CurrentPICarrierId = pi.PatientInsuranceId AND pvi.PatientInsuranceId = pi.PatientInsuranceId
LEFT JOIN InsuranceCarriers ic ON pv2.CurrentInsuranceCarriersId = ic.InsuranceCarriersId AND pi.InsuranceCarriersId = ic.InsuranceCarriersId
WHERE
pv2.PatientVisitId = pv.PatientVisitId
)
AS InsuredId ,
ISNULL(ig.name , 'No Ins Group') AS InsGroupName ,
CASE @groupby1
WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
WHEN 'Facility' THEN fac.listname
WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
WHEN 'Procedure' THEN ISNULL(p.Code , 'Deposit')
WHEN 'Provider' THEN dr.listname
WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
ELSE fac.listname
END AS Group1 ,
CASE @groupby2
WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
WHEN 'Facility' THEN fac.listname
WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
WHEN 'Provider' THEN dr.listname
WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
ELSE fac.listname
END AS Group2 ,
CASE @groupby3
WHEN 'Carrier' THEN ISNULL(ic.listname , 'No Carrier')
WHEN 'Company' THEN ISNULL(comp.listname , ' No Company')
WHEN 'Department' THEN ISNULL(dept.description , 'No Dept')
WHEN 'Facility' THEN fac.listname
WHEN 'Financial Class' THEN ISNULL(fc.description , 'No Financial Class')
WHEN 'Insurance Group' THEN ISNULL(ig.name , 'No Ins Group')
WHEN 'Procedure' THEN ISNULL(p.Code , 'No Procedure')
WHEN 'Provider' THEN dr.listname
WHEN 'Referring Physician' THEN ISNULL(ref.listname , 'No Referring Doc')
ELSE fac.listname
END AS Group3
FROM
transactiondistributions td
INNER JOIN transactions tr ON td.transactionsID = tr.transactionsID
INNER JOIN visittransactions vt ON tr.visittransactionsID = vt.visittransactionsID
INNER JOIN paymentmethod pm ON vt.paymentmethodID = pm.paymentmethodID
LEFT JOIN insurancecarriers ic ON vt.insurancecarriersID = ic.insurancecarriersID
LEFT JOIN insurancegroup ig ON ic.insurancegroupID = ig.insurancegroupID
INNER JOIN patientvisit pv ON pv.patientvisitID = vt.patientvisitID
LEFT JOIN patientvisitprocs pvp ON td.patientvisitprocsID = pvp.patientvisitprocsID
LEFT JOIN procedures p ON pvp.proceduresID = p.proceduresID
INNER JOIN batch b ON pm.batchID = b.batchID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'department'
) dept ON p.departmentMID = dept.medlistsID
LEFT JOIN (
SELECT * FROM doctorfacility WHERE type = 2
) fac ON pv.facilityID = fac.doctorfacilityID
LEFT JOIN (
SELECT * FROM doctorfacility WHERE type = 5
) comp ON pv.companyID = comp.doctorfacilityID
LEFT JOIN (
SELECT * FROM doctorfacility WHERE type = 1 OR type = 7
) dr ON pv.doctorID = dr.doctorfacilityID
LEFT JOIN (
SELECT * FROM doctorfacility WHERE type = 3
) ref ON pv.referringdoctorID = ref.doctorfacilityID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'FinancialClass'
) fc ON pv.financialclassMID = fc.medlistsID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'Modifiers'
) mod1 ON pvp.Modifier1MID = mod1.medlistsID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'Modifiers'
) mod2 ON pvp.Modifier2MID = mod2.medlistsID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'Modifiers'
) mod3 ON pvp.Modifier3MID = mod3.medlistsID
LEFT JOIN (
SELECT * FROM medlists WHERE tablename = 'Modifiers'
) mod4 ON pvp.Modifier4MID = mod4.medlistsID
LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
AND pvp.PatientVisitDiags1 = pvd.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId
AND pvp.PatientVisitDiags2 = pvd1.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId
AND pvp.PatientVisitDiags3 = pvd2.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId
AND pvp.PatientVisitDiags4 = pvd3.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId
AND pvp.PatientVisitDiags5 = pvd4.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId
AND pvp.PatientVisitDiags6 = pvd5.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId
AND pvp.PatientVisitDiags7 = pvd6.ListOrder
LEFT OUTER JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId
AND pvp.PatientVisitDiags8 = pvd7.ListOrder
WHERE
(
tr.type = 'P'
OR tr.type = 'A'
)
-- and tr.action = 'P'
-- and pv.ticketnumber = @ticketnum
AND b.entry >= @startdate
AND b.entry < @enddate
AND td.amount <> 0
AND --Filter on Carrier
(
(
NULL IS NOT NULL
AND pv.primaryinsurancecarriersID 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 Department
(
(
NULL IS NOT NULL
AND p.DepartmentMId 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 Financial Class
(
(
NULL IS NOT NULL
AND pv.FinancialClassMID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Ins Group
(
(
NULL IS NOT NULL
AND ic.insuranceGroupID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Procedure
(
(
NULL IS NOT NULL
AND pvp.ProceduresId IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Provider
(
(
NULL IS NOT NULL
AND pv.DoctorID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Referring Doc
(
(
NULL IS NOT NULL
AND pv.ReferringDoctorID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Diagnosis
(
(
NULL IS NOT NULL
AND pvd.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd1.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd2.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd3.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd4.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd5.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd6.DiagnosisId IN ( NULL )
)
OR (
NULL IS NOT NULL
AND pvd7.DiagnosisId IN ( NULL )
)
OR ( NULL IS NULL )
)
--Update the 'reporting' resource types
UPDATE
#tbl_temp
SET
resourceID = res.doctorfacilityID
FROM
#tbl_temp t
LEFT JOIN patientvisitresource pvr2 ON t.patientvisitID = pvr2.patientvisitID
LEFT JOIN doctorfacility res ON pvr2.resourceID = res.doctorfacilityID
LEFT JOIN ResourceTypeAssignments rta ON res.doctorfacilityID = rta.resourceID
WHERE
rta.resourcetypeID = @ResTypeID
--Update the non-reporting resources
UPDATE
#tbl_temp
SET
resourceID = ISNULL(res.doctorfacilityID , 0)
FROM
#tbl_temp t
LEFT JOIN patientvisitresource pvr2 ON t.patientvisitID = pvr2.patientvisitID
LEFT JOIN doctorfacility res ON pvr2.resourceID = res.doctorfacilityID
WHERE
t.ResourceID = 0
--Update everything that is left with the doctor
--update #tbl_temp
--set resourceID = t.doctorID
--from #tbl_temp t
--where t.ResourceID = 0
SELECT
t.Ticketnumber ,
t.ProviderName ,
t.ReferringDocName ,
ISNULL(res.ListName , 'No Resource') AS ResourceName ,
t.FacilityName ,
t.CompanyName ,
t.CPTCode ,
t.Code ,
t.Diag1 ,
t.Diag2 ,
t.Diag3 ,
t.Diag4 ,
t.Diag5 ,
t.Diag6 ,
t.Diag7 ,
t.Diag8 ,
t.Modifier ,
t.Units ,
t.Department ,
t.Charges ,
t.Payments ,
t.Adjustments ,
t.DateOfEntry ,
t.DateOfService ,
t.FinancialClass ,
t.Carriername ,
t.InsuredId ,
t.InsGroupName AS InsGroupName ,
t.CPTDescription ,
t.ticketnumber + '_' + t.code + '_' + t.modifier AS TicketCode ,
CASE @groupby1
WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
ELSE t.Group1
END AS Group1 ,
CASE @groupby2
WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
ELSE t.Group2
END AS Group2 ,
CASE @groupby3
WHEN 'Resource' THEN ISNULL(res.listname , 'No Resource')
ELSE t.Group3
END AS Group3
--select sum(t.Charges), sum(t.Payments), sum(t.adjustments)
--into cus_ProcCrossRef
--drop table cus_ProcCrossRef
FROM
#tbl_temp t
LEFT JOIN doctorfacility res ON t.resourceID = res.doctorfacilityID
WHERE
--Filter on resource
(
(
NULL IS NOT NULL
AND t.ResourceID IN ( NULL )
)
OR ( NULL IS NULL )
)
--order by ticketnumber desc
DROP TABLE #tbl_temp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.