SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Finding the first appointment per patient per mont
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bbk0919
Starting Member

USA
1 Posts

Posted - 03/21/2013 :  23:58:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/22/2013 :  00:27:24  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/22/2013 :  01:23:39  Show Profile  Reply with Quote
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

Sweden
30190 Posts

Posted - 03/23/2013 :  04:33:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
,Row_numbeR() OVER(PARTITION BY PatientName, DATEADD(mm, DATEDIFF(mm, 0, ApptTimestamp) ,0) ORDER BY ApptTimestamp) AS Seq



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000