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

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Finding first date within fields
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
2 Posts

Posted - 06/27/2005 :  11:05:15  Show Profile
Hi all, I am an SQL newbie, so hopefully someone can point me in the right direction:-
I am using SQL Server8, and have a dbo with the following variables:
I want to be able to find the first date from the 4 date fields, and then return the associated code, where:
PrimaryOpDate is associated with PrimaryOpCode,
SecOpDate1 is associate with SecOpCode1 etc.
Many thanks for any help someone can give, I just need to know the start of the syntax I should use within my query.

Flowing Fount of Yak Knowledge

6062 Posts

Posted - 06/27/2005 :  11:54:58  Show Profile
Is it possible to re-organize your table(s) so that you have a row for each OpDate/OpCode?

create table opCodeType
	(opCodeTypeID tinyint
	,opCodeTypeDesc varchar(50))
create table myTable
	(PatientID int
	,Opdate datetime
	,OpCode int
	,OpCodeTypeID tinyint references opCodeType(opCodeTypeID))


insert opCodeType values (1, 'Primary')
insert opCodeType values (2, 'Secondary')

--then your query could be something like:

select	patientID
from	myTable a
join	(--derived table to get first date for each Patient
	select	patientID
		,min(opDate) opDate 
	from	myTable 
	group by patientID
	) b
		on a.patientID = b.patientID
		and a.opDate = b.opDate
join	opCodeType c
		on a.opCodeTypeID = c.opCodeTypeDesc

Be One with the Optimizer
Go to Top of Page

Starting Member

United Kingdom
2 Posts

Posted - 06/27/2005 :  12:04:20  Show Profile
TG, thanks for your reply. I am really a VBA novice, but i will give what you are suggesting a go, and will submit a new post if it all goes wrong!
thanks again
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000