Microsoft, SQL, SQL 2000, Query Assistance

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