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 2008 Forums
 Transact-SQL (2008)
 Finding the first appointment per patient per mont

Author  Topic 

bbk0919
Starting Member

1 Post

Posted - 2013-03-21 : 23:58:47
I am trying write an efficient t-sql query that will return, for a specific date range, the first appointment for each patient for each month. For example, a particular patient has the folowing appointments for 1/1/2012 - 3/31/2012:

Patient Name Appt Number Appt Timestamp
------------- ------------- ---------------
John Smith 18374832 2012-01-05 08:15:00
John Smith 19837289 2012-01-07 14:30:00
John Smith 19982776 2012-02-15 09:00:00
John Smith 20091092 2012-02-27 15:45:00
John Smith 20100939 2012-02-28 07:25:00
John Smith 20110938 2012-03-05 16:50:00

Given that data the query will only return:
John Smith 18374832 2012-01-05 08:15:00
John Smith 19982776 2012-02-15 09:00:00
John Smith 20110938 2012-03-05 16:50:00

Any ideas what an efficient code would be go create such a result set?

Thanks in advance!
Babak
Phoenix, AZ

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-22 : 00:27:24
[code]SELECT PatientName, ApptNumber, ApptTimestamp
FROM (SELECT PatientName, ApptNumber, ApptTimestamp
,Row_numbeR() OVER(PARTITION BY PatientName, MONTH(ApptTimestamp) ORDER BY ApptTimestamp) Seq
FROM PatientTable
)t
WHERE t.Seq=1
[/code]

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-22 : 01:23:39
quote:
Originally posted by bandi

SELECT PatientName, ApptNumber, ApptTimestamp
FROM (SELECT PatientName, ApptNumber, ApptTimestamp
,Row_numbeR() OVER(PARTITION BY PatientName, MONTH(ApptTimestamp) ORDER BY ApptTimestamp) Seq
FROM PatientTable
)t
WHERE t.Seq=1


--
Chandu


Just taking month alone will cause an issue if date range falls above an year.

see this illustration


declare @test table
(
[PatientName] varchar(100),
[ApptNumber] int,
[ApptTimestamp] datetime
)
insert @test
([PatientName], [ApptNumber],[ApptTimestamp])
values ('John Smith',18374832,40913.34375),
('John Smith',19837289,40915.6041666667),
('John Smith',19982776,40954.375),
('John Smith',20091092,40966.65625),
('John Smith',20100939,40967.3090277778),
('John Smith',20110938,40973.7013888889)

insert @test
([PatientName], [ApptNumber],[ApptTimestamp])
values('John Smith',20129193,'20130101 13:10:24'),
('John Smith',20121123,'20130111 16:10:23'),
('John Smith',20130056,'20130213 14:25:34')

SELECT * FROM @test
SELECT 'Bandi''s solution'
SELECT PatientName, ApptNumber, ApptTimestamp
FROM (SELECT PatientName, ApptNumber, ApptTimestamp
,Row_numbeR() OVER(PARTITION BY PatientName, MONTH(ApptTimestamp) ORDER BY ApptTimestamp) Seq
FROM @test
)t
WHERE t.Seq=1

SELECT 'VIsakh Solution'
SELECT PatientName, ApptNumber, ApptTimestamp
FROM (SELECT PatientName, ApptNumber, ApptTimestamp
,Row_numbeR() OVER(PARTITION BY PatientName, DATEADD(mm,DATEDIFF(mm,0,ApptTimestamp),0) ORDER BY ApptTimestamp) Seq
FROM @test
)t
WHERE t.Seq=1



output
----------------------------------------
inserted data
----------------------------------------
PatientName ApptNumber ApptTimestamp
---------------------------------------------------------------------------------------------------- ----------- -----------------------
John Smith 18374832 2012-01-07 08:15:00.000
John Smith 19837289 2012-01-09 14:30:00.000
John Smith 19982776 2012-02-17 09:00:00.000
John Smith 20091092 2012-02-29 15:45:00.000
John Smith 20100939 2012-03-01 07:25:00.000
John Smith 20110938 2012-03-07 16:50:00.000
John Smith 20129193 2013-01-01 13:10:24.000
John Smith 20121123 2013-01-11 16:10:23.000
John Smith 20130056 2013-02-13 14:25:34.000



----------------
Bandi's solution
----------------


PatientName ApptNumber ApptTimestamp
---------------------------------------------------------------------------------------------------- ----------- -----------------------
John Smith 18374832 2012-01-07 08:15:00.000
John Smith 19982776 2012-02-17 09:00:00.000
John Smith 20100939 2012-03-01 07:25:00.000




---------------
VIsakh Solution
---------------


PatientName ApptNumber ApptTimestamp
---------------------------------------------------------------------------------------------------- ----------- -----------------------
John Smith 18374832 2012-01-07 08:15:00.000
John Smith 19982776 2012-02-17 09:00:00.000
John Smith 20100939 2012-03-01 07:25:00.000
John Smith 20129193 2013-01-01 13:10:24.000
John Smith 20130056 2013-02-13 14:25:34.000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-23 : 04:33:08
[code],Row_numbeR() OVER(PARTITION BY PatientName, DATEADD(mm, DATEDIFF(mm, 0, ApptTimestamp) ,0) ORDER BY ApptTimestamp) AS Seq[/code]


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

- Advertisement -