We help IT Professionals succeed at work.
Get Started

Microsoft, SQL, SQL 2000, Query Assistance

218 Views
Last Modified: 2010-03-20
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
Comment
Watch Question
Principal Operations Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE