troubleshooting Question

Microsoft, SQL, SQL 2000, Query Assistance

Avatar of Jeff S
Jeff SFlag for United States of America asked on
Microsoft SQL ServerDB Reporting ToolsSQL
2 Comments1 Solution222 ViewsLast Modified:
Result Set example from my Query (see below):

Name: Abbey,Jeff
Created: 1999-07-17 12:14:46.340
Number of Visits: 8
Charges: 3214.0000
Payments: 10.0000
Grouping: Null

What I would like to have is only patients where the Number of Visits = 1. If it exceeds '1' visit, I dont want them to display. My client wants to know patients that came to clinic and never returned for a subsequent visit. So if they had 2 visits obviously they returned.

Query in Query Analyzer:

/* New Patient Analysis */

SET NOCOUNT ON

CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL ,
[Prefix] [varchar] (10) NULL ,
[First] [varchar] (30) NULL ,
[Middle] [varchar] (30) NULL ,
[Last] [varchar] (30) NULL ,
[Suffix] [varchar] (20) NULL ,
[Created] [datetime] NOT NULL
)

Insert #PatientTemp

SELECT PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix,
CASE
WHEN 1 = 1 THEN PP.Created
WHEN 1 = 2 THEN Min(PV.Visit)
ELSE NULL
END As Created

FROM PatientVisit PV
INNER JOIN PatientProfile PP ON PP.PatientProfileId = PV.PatientProfileId

GROUP BY PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix, PP.Created

HAVING (((1 = 1)AND
(PP.Created >= ISNULL(NULL,'1/1/1900') AND PP.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(PV.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))

Order By PP.First, PP.Last

SELECT dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix) AS Name,
CASE
WHEN 1 = 1 THEN #PatientTemp.Created
WHEN 1 = 2 THEN Min(pv.Visit)
ELSE NULL
END As Created,
COUNT(*) AS [Number Of Visits],
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULLA
END As Grouping

FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN DoctorFacility df1 ON pv.ReferringDoctorId = df1.DoctorFacilityId
LEFT JOIN DoctorFacility df2 ON pv.FacilityId = df2.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId

WHERE
(
(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.ReferringDoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on insurance carrier
(
(NULL IS NOT NULL AND ic.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)

GROUP BY #PatientTemp.Created,dbo.FormatName(#PatientTemp.Prefix, #PatientTemp .First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix),
pva.PatientProfileId,
CASE
WHEN '0' = 1 THEN df.ListName
WHEN '0' = 2 THEN df2.ListName
WHEN '0' = 3 THEN ic.ListName
ELSE NULL
END

HAVING (((1 = 1)AND
(#PatientTemp.Created >= ISNULL(NULL,'1/1/1900') AND #PatientTemp.Created < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))
OR
((1 = 2)AND
((MIN(pv.Visit) >= ISNULL(NULL,'1/1/1900') AND (MIN(pv.Visit)) < DATEADD(d, 1, ISNULL(NULL,'1/1/3000')))
))

ORDER BY dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp .Suffix), #PatientTemp.Created
Drop table #PatientTemp

Query in third party application:

/* New Patient Analysis */

SET NOCOUNT ON

CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL ,
[Prefix] [varchar] (10) NULL ,
[First] [varchar] (30) NULL ,
[Middle] [varchar] (30) NULL ,
[Last] [varchar] (30) NULL ,
[Suffix] [varchar] (20) NULL ,
[Created] [datetime] NOT NULL
)

Insert #PatientTemp

SELECT PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix,
CASE
WHEN ?CREATEDORFIRSTVISITDATE.VALUE.U? = 1 THEN PP.Created
WHEN ?CREATEDORFIRSTVISITDATE.VALUE.U? = 2 THEN Min(PV.Visit)
ELSE NULL
END As Created

FROM PatientVisit PV
INNER JOIN PatientProfile PP ON PP.PatientProfileId = PV.PatientProfileId

GROUP BY PP.PatientProfileId, PP.Prefix, PP.First, PP.Middle, PP.Last, PP.Suffix, PP.Created

HAVING (((?CREATEDORFIRSTVISITDATE.VALUE.U? = 1)AND
(PP.Created >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND PP.Created < DATEADD(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000')))
))
OR
((?CREATEDORFIRSTVISITDATE.VALUE.U? = 2)AND
((MIN(PV.Visit) >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND (MIN(PV.Visit)) < DATEADD(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000')))
))

Order By PP.First, PP.Last

SELECT dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix) AS Name,
CASE
WHEN ?CREATEDORFIRSTVISITDATE.VALUE.U? = 1 THEN #PatientTemp.Created
WHEN ?CREATEDORFIRSTVISITDATE.VALUE.U? = 2 THEN Min(pv.Visit)
ELSE NULL
END As Created,
COUNT(*) AS [Number Of Visits],
SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) AS Charges,
SUM(pva.InsPayment + pva.PatPayment) AS Payments,
CASE
WHEN ?GROUPBY.ITEMDATA? = 1 THEN df.ListName
WHEN ?GROUPBY.ITEMDATA? = 2 THEN df2.ListName
WHEN ?GROUPBY.ITEMDATA? = 3 THEN ic.ListName
ELSE NULL
END As Grouping

FROM PatientVisit pv
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN DoctorFacility df1 ON pv.ReferringDoctorId = df1.DoctorFacilityId
LEFT JOIN DoctorFacility df2 ON pv.FacilityId = df2.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId

WHERE
(
(?DOCTOR.ITEMDATA? IS NOT NULL AND pv.DoctorId IN (?DOCTOR.ITEMDATA.U?)) OR
(?DOCTOR.ITEMDATA? IS NULL)
)
AND
(
(?REFERRINGDOCTOR.ITEMDATA? IS NOT NULL AND pv.ReferringDoctorId IN (?REFERRINGDOCTOR.ITEMDATA.U?)) OR
(?REFERRINGDOCTOR.ITEMDATA? IS NULL)
)
AND
(
(?FACILITY.ITEMDATA? IS NOT NULL AND pv.FacilityId IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND --Filter on insurance carrier
(
(?INSURANCECARRIER.ITEMDATA? IS NOT NULL AND ic.InsuranceCarriersId IN (?INSURANCECARRIER.ITEMDATA.U?)) OR
(?INSURANCECARRIER.ITEMDATA? IS NULL)
)

GROUP BY #PatientTemp.Created,dbo.FormatName(#PatientTemp.Prefix, #PatientTemp .First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp.Suffix),
pva.PatientProfileId,
CASE
WHEN ?GROUPBY.ITEMDATA? = 1 THEN df.ListName
WHEN ?GROUPBY.ITEMDATA? = 2 THEN df2.ListName
WHEN ?GROUPBY.ITEMDATA? = 3 THEN ic.ListName
ELSE NULL
END

HAVING (((?CREATEDORFIRSTVISITDATE.VALUE.U? = 1)AND
(#PatientTemp.Created >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND #PatientTemp.Created < DATEADD(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000')))
))
OR
((?CREATEDORFIRSTVISITDATE.VALUE.U? = 2)AND
((MIN(pv.Visit) >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND (MIN(pv.Visit)) < DATEADD(d, 1, ISNULL(?DATERANGE.DATE2?,'1/1/3000')))
))
ORDER BY dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp .Suffix), #PatientTemp.Created
Drop table #PatientTemp
ASKER CERTIFIED SOLUTION
Nick Upson
Senior Scientific IT Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 2 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 2 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004