Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Where count = 1 help.

Author  Topic 

JeffS23
Posting Yak Master

212 Posts
Posted - 2007-10-26 : 10:26:47
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

Kristen
Test

22859 Posts
Posted - 2007-10-26 : 13:23:53
"What I would like to have is only patients where the Number of Visits = 1"

You either need a

HAVING COUNT(*) = 1

in the appropriate query, or perhaps a:

WHERE NOT EXISTS (SELECT * FROM VisitsTable WHERE VisitID <> ThisVisitID)

to exclude visits where there exists "at least one other visit"

Hope that makes sense?

Kristen
Go to Top of Page

JeffS23
Posting Yak Master

212 Posts
Posted - 2007-10-26 : 13:37:28
Kristen,

I appreciate the response back. I think the best avenue to go is the Having Count(*) = 1, however I cant seem to get the desired results (actually It looks to have no affect on the result set) when I use this. Perhaps I am using it in the wrong place? I was using it on the second temp table insert.

/* 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 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
(
(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')))
))
AND COUNT(*) = 1
ORDER BY dbo.FormatName(#PatientTemp.Prefix, #PatientTemp.First, #PatientTemp.Middle, #PatientTemp.Last, #PatientTemp .Suffix), #PatientTemp.Created
Drop table #PatientTemp
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-26 : 17:16:52
I agree it looks to be the right place, but its a lot of code to fully comprehend, and that will probably take more time than I have.

Only other though is to insert the results from that second query into another Temp Table, and then use a HAVING COUNT(*) > 1 or EXISTS test on the results of that. It might be more "focused" than trying to do it in the current Query 2, and once you have that sorted out it will probably become more obvious how to fit it into an existing query, which you can then do as a "refinement"

Kristen
Go to Top of Page

JeffS23
Posting Yak Master

212 Posts
Posted - 2007-10-26 : 17:23:39
Kristen -

I actually moved it up and it worked like a charm. Thanks for your help!

HAVING COUNT(*) = 1 AND
(((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')))
))
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-26 : 17:38:49
Couple of other suggestions:

String dates ought to be formatted as 'yyyymmdd' (no hyphens) so they are locale-independent and unambiguous.

The
OR ((1=2 AND ...)
is not going to execute, but perhaps that code is generated by some front end ...

Likewise ISNULL(NULL,'1/1/1900') - and the optimiser might perform better if this was just the date, not the expression.

Also things like

WHERE
(
(NULL IS NOT NULL AND pv.DoctorId IN (NULL)) OR
(NULL IS NULL)
)

might be better if left out - the cached query plans may be more readily reused

I prefer to use DATEADD(Day, ... to be more obvious than the single character shortcuts.

D = Day, right?

1) What about DD?
2) DY?

If D is Day what about Year:

3) Y?
4) how about YY?
5) YYYY?

And what about the shortcode M:

6) Millisecond, Minute or Month?

7) And is H = Hour?
8) W=Week?

Kristen
Go to Top of Page

JeffS23
Posting Yak Master

212 Posts
Posted - 2007-10-26 : 17:43:55
Most of this unfortuantely is like you thought .... on the front end (third party application). Thanks for the tips and help though ... you pointed me in the right direction.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site