popq79 34287 (1/23/2013)
Hi AllCan someone help me how to select the row with min(E_DATE+E_Time) for each PATIID from the below table?
PATID NAME A_DATE E_DATE E_TIME E_ID
00001 AAA 01/01/2013 01/01/2013 06:00:00 X9625
00001 AAA 01/01/2013 01/01/2013 14:00:00 A2505
00002 BBB 01/03/2012 03/04/2012 15:45:00 G2582
00002 BBB 01/01/2012 01/02/2012 13:45:00 P4484
00002 BBB 01/01/2012 04/01/2012 11:45:00 M1402
...
...
Results need to be like below (ie. min (E_DATE+E_TIME) for each PATID
PATID NAME A_DATE E_DATE E_TIME E_ID
00001 AAA 01/01/2013 01/01/2013 06:00:00 X9625
00002 BBB 01/01/2012 04/01/2012 11:45:00 M1402
Please note I'm using SQL2000.
Thanks in advance
I cobbled together an example of this but I don't quite understand your business rules here. You said you wanted to get the earliest for each PATID but for PATID 00002 you did not get the earliest. The earliest one would be for E_ID P4484 not M1402 as you posted.
Here is the example of what I think you want.
IF OBJECT_ID('tempdb..#SomeTable') IS NOT NULL
drop table #SomeTable
create table #SomeTable
(
PATID char(5),
Name char(3),
A_DATE datetime,
E_DATE datetime,
E_TIME datetime,
E_ID char(5) primary key
)
insert #SomeTable
select '00001', 'AAA', '01/01/2013', '01/01/2013', '06:00:00', 'X9625' union all
select '00001', 'AAA', '01/01/2013', '01/01/2013', '14:00:00', 'A2505' union all
select '00002', 'BBB', '01/03/2012', '03/04/2012', '15:45:00', 'G2582' union all
select '00002', 'BBB', '01/01/2012', '01/02/2012', '13:45:00', 'P4484' union all
select '00002', 'BBB', '01/01/2012', '04/01/2012', '11:45:00', 'M1402'
select st.*
from #SomeTable st
join
(
select PATID, MIN(E_DATE + E_TIME) as E_DATETIME
from #SomeTable
group by PATID
) x on x.PATID = st.PATID and x.E_DATETIME = st.E_DATE + st.E_TIME
Please notice how I posted a sample table and populated with data. This is what we would like to see when posting questions. This way we all know what the datatypes are and the people who help answer questions (all volunteer) can get to work on the issue instead of setting up the problem.
I still don't quite get why you want to have 2 datetime fields to hold one piece of information. Especially when you have to constantly add them together to get the whole thing. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/