Appointments: Kept and Future - 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 2005 Forums

Transact-SQL (2005)

Appointments: Kept and Future

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-11-03 : 15:45:35

Scenario: I need to get the last kept appointment and type as well as the next scheduled appointment and type.

The problem is that if there are multiple types, I'm getting more than the desired records. I would like to only get the last kept and next scheduled. I have my query written below.

SELECT p.PatsOfficeAccount, p.PatsFirstName+' '+p.PatsLastName as Patient, wa.ContractBalance,
ap.LastApptDate, ap.ApptType, ap2.FutureApptDate, ap2.ApptType
FROM tPats p
INNER JOIN Ageing wa
on wa.OfficeNumber = p.OfficeNumber
and wa.PatsNumber = p.PatsNumber
--Gets the last kept appointment.
RIGHT JOIN (
SELECT appt.OfficeNumber, appt.PatsNumber, isnull(max(appt.dt_appt),'') as LastApptDate, tc.task_desc_short as ApptType
FROMtbl_appt appt
INNER JOIN tbl_task_codes tc
on tc.task_cd = appt.task_cd
and tc.OfficeNumber = appt.OfficeNumber
WHERE appt.stat_Cd in (1,7)
and appt.OfficeNumber = '110835'
and appt.dt_appt < getdate()
GROUP BY appt.OfficeNumber, appt.PatsNumber, tc.task_desc_short
) ap
on ap.OfficeNumber = p.OfficeNumber
and ap.PatsNumber = p.PatsNumber
--Gets the next scheduled appointment.
RIGHT JOIN (
SELECT appt.OfficeNumber, appt.PatsNumber, isnull(min(appt.dt_appt),'') as FutureApptDate, tc.task_desc_short as ApptType
FROM tbl_appt appt
INNER JOIN tbl_task_codes tc
on tc.task_cd = appt.task_cd
and tc.OfficeNumber = appt.OfficeNumber
WHERE appt.stat_Cd = 0
and appt.OfficeNumber = '110835'
and appt.dt_appt > getdate()
GROUP BY appt.OfficeNumber, appt.PatsNumber, tc.task_desc_short
) ap2
on ap2.OfficeNumber = p.OfficeNumber
and ap2.PatsNumber = p.PatsNumber
WHERE p.OfficeNumber = '110835'
and wa.AgeingDate >= dateadd(dd,-2,getdate())
and wa.PatsIsActive = 1
ORDER BY p.PatsLastName, p.PatsFirstName, ap.LastApptDate desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 16:20:26

[code]DECLARE @Sample TABLE
(
patID INT,
typeVisit INT,
dateVisit DATETIME,
walkOver TINYINT
)

INSERT @Sample
SELECT 1, 1, '20080910', 1 UNION ALL
SELECT 1, 1, '20080927', 0 UNION ALL
SELECT 1, 1, '20081012', 0 UNION ALL
SELECT 1, 1, '20081013', 1 UNION ALL
SELECT 1, 1, '20081113', NULL

SELECT *
FROM @Sample

SELECT patID,
typeVisit,
dateVisit,
walkOver
FROM (
SELECT patID,
typeVisit,
dateVisit,
walkOver,
ROW_NUMBER() OVER (PARTITION BY patID ORDER BY dateVisit DESC) AS recID
FROM @Sample
WHERE walkOver = 0
) AS d
WHERE recID = 1

UNION ALL

SELECT patID,
typeVisit,
dateVisit,
walkOver
FROM (
SELECT patID,
typeVisit,
dateVisit,
walkOver,
ROW_NUMBER() OVER (PARTITION BY patID ORDER BY dateVisit) AS recID
FROM @Sample
WHERE walkOver IS NULL
) AS d
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 16:25:23

And in one step

SELECT	patID,
	typeVisit,
	dateVisit,
	walkOver
FROM	(
		SELECT	patID,
			typeVisit,
			dateVisit,
			walkOver,
			ROW_NUMBER() OVER (PARTITION BY walkOver ORDER BY DATEDIFF(MINUTE, dateVisit, GETDATE())) AS recID
		FROM	@Sample
		WHERE	walkOver IS NULL
			OR walkOver = 0
	) AS d
WHERE	recID = 1

E 12°55'05.63"
N 56°04'39.26"

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-11-03 : 16:49:07

I'm getting an error:
Msg 195, Level 15, State 10, Line 28
'ROW_NUMBER' is not a recognized function name.
Msg 195, Level 15, State 10, Line 45
'ROW_NUMBER' is not a recognized function name.

I have both versions of SQL 2000 and 2005. Both have the same error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 16:51:12

If you are using SQL Server 2005, set compatibility level for the current database to 90 or higher.

E 12°55'05.63"
N 56°04'39.26"

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-11-03 : 16:53:53

I'm not an administrator, only a developer. How or where do I go to set this?

Edit: The company is using SQL 2000. I'm using the tools from 2005.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 17:04:42

Then you have posted in the wrong forum.
It is the DATABASE ENGINE version that is important, not your tool version.
Beacuse you can query a SQL Server 2000 database from SSMS 2005.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-03 : 17:07:10

Assuming no patient have more than one visit any given day...

DECLARE	@Sample TABLE
	(
		patID INT,
		typeVisit INT,
		dateVisit DATETIME,
		walkOver TINYINT
	)

INSERT	@Sample
SELECT	1, 1, '20080910', 1 UNION ALL
SELECT	1, 2, '20080927', 0 UNION ALL
SELECT	1, 3, '20081012', 0 UNION ALL
SELECT	1, 4, '20081013', 1 UNION ALL
SELECT	1, 5, '20081113', NULL

SELECT	*
FROM	@Sample

SELECT		s.*
FROM		@Sample AS s
INNER JOIN	(
			SELECT		patID,
					MAX(dateVisit) AS theDate
			FROM		@Sample
			WHERE		walkOver = 0
			GROUP BY	patID

			UNION ALL

			SELECT		patID,
					MIN(dateVisit)
			FROM		@Sample
			WHERE		walkOver IS NULL
			GROUP BY	patID
		) AS w ON w.patID = s.patID
			AND w.theDate = s.dateVisit

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources