March 21, 2013 at 9:54 pm
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
March 21, 2013 at 10:49 pm
This should help you get started. Please note how I created a table and populated that table with sample data. This is what you need to provide in the future when posting questions like this. I do have to give you credit for also posting the expected results as that is needed as well.
create table #TestTable (
PatientName varchar(132),
ApptNumber int,
ApptDate datetime
);
insert into #TestTable
values
('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');
with BaseData as (
select
PatientName,
ApptNumber,
ApptDate,
rn = row_number() over (partition by PatientName, dateadd(mm, datediff(mm, 0, ApptDate), 0) order by ApptDate asc)
from
#TestTable
)
select
PatientName,
ApptNumber,
ApptDate
from
BaseData
where
rn = 1;
drop table #TestTable;
/*
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
*/
March 22, 2013 at 12:16 am
Lynn, thanks for the information and the feedback! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply