Should I use Max? - SQL Server Forums

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)

Should I use Max?

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-04-24 : 20:12:58

I need to figure out a way to code the last visit date .. any thoughts? Should I use the Max function to do this? I used "MAX(pv.Visit)AS 'last visit'" - would this be best way? I am still learning SQL and could use some pros.

/*Recalls*/

SET NOCOUNT ON

DECLARE @AlphaStart Varchar(20),
@AlphaEnd Varchar(20),
@PatAgeStart INT,
@PatAgeEnd INT

SET @AlphaStart= CAST(NULL as VARCHAR(20))
SET @AlphaEnd = CAST(NULL as VARCHAR(19)) +'z'

SET @PatAgeStart = CAST(NULL as INT)
SET @PatAgeEnd = CAST(NULL as INT)

SELECT dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS Name,
pp.PatientId,
ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],
ISNULL(pp.city,'')+ ' '+ ISNULL(pp.state,'')+' '+ ISNULL(pp.zip,'')AS [Patient CSZ],
'PatPhone1' = IsNull(dbo.formatphone(pp.Phone1,1),''),
'PatPhone2' = IsNull(dbo.formatphone(pp.Phone2,1),''),
ApptType.Name AS ApptType,
r.ListName AS Doctor, f.ListName AS Facility,
Recall.Created, Recall.[Date] AS RecallDate,
Recall.LastLetter AS ContactDate,
md.Description AS ContactMethod,
Recall.Inactive,
CASE WHEN ISNULL(Recall.ApptId,0) = 0 THEN 'No' ELSE 'Yes' END AS Resolved,
GroupBy=CONVERT(varchar(50),'(None)'),
Flag=CONVERT(varchar(50),'(None)'),
Notes=CONVERT(varchar(255), Recall.Notes)

FROM Recall
LEFT JOIN PatientProfile pp ON Recall.PatientProfileId = pp.PatientProfileId
LEFT JOIN DoctorFacility r ON Recall.DoctorResourceId = r.DoctorFacilityId
LEFT JOIN DoctorFacility f ON Recall.FacilityId = f.DoctorFacilityId
LEFT JOIN ApptType ON Recall.ApptTypeId = ApptType.ApptTypeId
LEFT JOIN Medlists md ON Recall.ContactMethod = md.MedlistsId
INNER JOIN PatientVisit pv ON pp.PatientProfileId = pv.PatientProfileId

WHERE ISNULL(Recall.Inactive,0) = 0 AND
(( ISNULL(Recall.ApptId,0) = 0 and 0 = 0) OR 0 = 1) and
--Filter on Doctor
(
(NULL IS NOT NULL AND Recall.DoctorResourceId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Facility
(
(NULL IS NOT NULL AND Recall.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter On Appointment Type
(
(NULL <>0 AND Recall.ApptTypeId = NULL) OR
(NULL IS NULL)
)
AND --Created Date Range
Recall.Created >= ISNULL(NULL,'1/1/1900') AND Recall.Created < dateadd(d, 1, ISNULL(NULL,'1/1/3000'))
AND --Created Date Range
Recall.[Date] >= ISNULL(NULL,'1/1/1900') AND Recall.[Date] < dateadd(d, 1, ISNULL(NULL,'1/1/3000'))
AND -- Filter on Patname
(
(NULL IS NOT NULL and pp.last >= @AlphaStart) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL and pp.last <= @AlphaEnd) OR
(NULL IS NULL)
)
AND -- Filter on PatAge
(
(NULL IS NOT NULL and datediff(month, pp.birthdate, getdate()) >= @PatAgeStart) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL and datediff(month, pp.birthdate, getdate()) <= @PatAgeEnd) OR
(NULL IS NULL)
)

ORDER BY NULL

Kristen
Test

22859 Posts

Posted - 2007-04-25 : 05:30:49

"I need to figure out a way to code the last visit date"

Something like this:


SELECT
    ...
    (SELECT MAX(MyDate) FROM dbo.MyTable) AS [Last Visit],
    ...

"ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.address2,'')AS [Patient Address],"

You might prefer to do:

ISNULL(pp.Address1+ ' ','')+ ISNULL(pp.address2,'')AS [Patient Address],

otherwise you will get leading space on address2 when Address1 is Null

"WHERE ... ISNULL(Recall.Inactive,0) = 0 ..."

You would be MUCH better making this column NOT allow NULLs (and Default to 0) so that you don't need to use a Function on it in the WHERE clause like this - the function is preventing possible optimisation / use of indexes [even if you don't have an index that could use this, you may very well need to as the table grows bigger!)

"--Filter on Doctor
(
(NULL IS NOT NULL AND Recall.DoctorResourceId IN (NULL)) OR
(NULL IS NULL)
)"

I presume you are generating this dynamically ... I expect that the optimiser will just ignore this, but it might be better to get the thing that generates the SQL to exclude this altogether when there is not filter condition.

"ISNULL(NULL,'1/1/1900')"

String dates should use ISO Format '19000101', any other format can be ambiguous and subject to Locale settings on the server etc.

Kristen

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-04-25 : 20:09:33

I changed the report query to another report and still trying to pull out the last visit date. I am having issues with the below mentioned subquery:

(SELECT MAX(pv.visit)
FROM PatientVisit pv
JOIN PatientProfile pp ON pv.patientprofileid = pp.patientprofileid) AS [Last Visit]

Currently, its reporting back all the same date "04/09/2007". This is not a date for any patient! I know im missing something in my subquery and could use some direction.

Full Query Below:

/* Recall Letters */

SET NOCOUNT ON

SELECT DISTINCT
r.[Date],
df.[First] AS DoctorFirst,
df.[Last] AS DoctorLast,
df.Suffix AS DoctorSuffix,
'(' + SUBSTRING(df.Phone1, 1, 3) + ')' + SUBSTRING(df.Phone1, 4, 3)
+ '-' + SUBSTRING(df.Phone1, 7, 4) AS DoctorPhone,
df2.Address1 AS DoctorAddr1,
df2.Address2 AS DoctorAddr2,
df2.City AS DoctorCity,
df2.State AS DoctorState,
df2.Zip AS DoctorZip,
pp.Prefix AS PatientPrefix,
pp.First AS PatientFirst,
pp.Last AS PatientLast,
pp.Address1 AS PatientAddr1,
pp.Address2 AS PatientAddr2,
pp.City AS PatientCity,
pp.State AS PatientState,
pp.Zip AS PatientZip,
g.Prefix AS PatientRespPrefix,
g.First AS PatientRespFirst,
g.Last AS PatientRespLast,
g.Address1 AS PatientRespAddr1,
g.Address2 AS PatientRespAddr2,
g.City AS PatientRespCity,
g.State AS PatientRespState,
g.Zip AS PatientRespZip,
pp.PatientSameAsGuarantor AS RespSameAsPatient,
r.DoctorResourceId,
r.FacilityId,
ISNULL(at.Name, ' ') AS AppointmentType,
(SELECT MAX(pv.visit)
FROM PatientVisit pv
JOIN PatientProfile pp ON pv.patientprofileid = pp.patientprofileid) AS [Last Visit]

-- 'Last Visit' = (select top 1 pv.visit from patientvisit pv where pv.PatientProfileId IN (NULL) OR
-- (NULL IS NULL) order by pv.visit desc)

FROM Recall r
INNER JOIN PatientProfile pp ON r.PatientProfileId = pp.PatientProfileId
LEFT OUTER JOIN ApptType at ON r.ApptTypeId = at.ApptTypeId
LEFT OUTER JOIN DoctorFacility df ON r.DoctorResourceId = df.DoctorFacilityId
LEFT OUTER JOIN DoctorFacility df2 ON r.FacilityId = df2.DoctorFacilityId
LEFT OUTER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
INNER JOIN PatientVisit pv ON pp.PatientProfileId = pv.PatientProfileId

WHERE r.ApptId IS NULL AND r.Date >= ISNULL(NULL,'1/1/1900') AND r.Date < dateadd(d, 1, ISNULL(NULL,'1/1/3000')) AND
--Filter on resource
(
(NULL IS NOT NULL AND r.DoctorResourceID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND r.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter On Appointment Type
(
(NULL <>0 AND r.ApptTypeId = NULL) OR
(NULL IS NULL)
)
AND --Filter on patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

-- ORDER BY ISNULL(g.Last,'')+ISNULL(g.First,'')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources