selecting next date - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

selecting next date

Author

Topic

anishap
Yak Posting Veteran

61 Posts

Posted - 2011-01-14 : 16:59:09

Can someone help me with the below query?

Sample Patient Table
ID Name
001 PAUL
002 JOHN
003 MARY
004 JIM
005 GEORGE
006 JEAN
007 SUZANNE

Sample Vaccine table
ID TYPE DUE-DATE
001 Tetanus 09/01/2016
001 TB 02/12/2011
001 Hep B 03/04/2011
002 TB 02/01/2011
002 Tetanus 01/23/2013
003 TB 02/14/2011
004 TB 09/12/2011
004 Tetatus 03/12/2019

Sample Output
ID Name DueDate
001 PAUL 02/12/2011
002 JOHN 02/01/2011
003 MARY 02/14/2011
004 JIM 09/12/2011

I tried the below query
SELECT DISTINCT P.ID,P.NAME,V.DUEDATE from PATIENT E, VACCINE V
WHERE E.ID = V.ID

But it is listing all rows. I just need the next duedate for each PATIENT.

Please let me know

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 17:31:40

Something like this?

Select ID, Name,b.NextDueDate
FROM Patients inner join (SELECT ID,MIN([DUE-DATE]) as NextDueDate
FROM Vaccine
WHERE [Due-Date] > getdate()) b
on Patients.ID = b.ID

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 06:09:41

also


SELECT ID, Name,NextDueDate
FROM Patients p
CROSS APPLY (SELECT TOP 1 DueDate
             FROM Vaccine 
             WHERE ID = p.IDDUE-DATE
             AND DUE-DATE > GETDATE()
             ORDER BY DUE-DATE
             )v

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

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-01-18 : 02:02:55

create table #Patient(id int,name varchar(20))
insert into #Patient(ID, Name)
select 001 ,'pAUL' union all
select 002, 'JOHN'union all
select 003, 'MARY'union all
select 004, 'JIM' union all
select 005 ,'GEORGE' union all
select 006 ,'JEAN'union all
select 007 ,'SUZANNE'
GO
create table #Vaccine(id int,TYPE varchar(20),due date )
insert into #Vaccine
select 001, 'Tetanus',' 09/01/2016'union all
select 001,' TB',' 02/12/2011'union all
select 001,' HepB',' 03/04/2011'union all
select 002,' TB ','02/01/2011'union all
select 002 ,'Tetanus',' 01/23/2013'union all
select 003,' TB ','02/14/2011'union all
select 004,' TB',' 09/12/2011'union all
select 004,' Tetatus',' 03/12/2019'
GO

select
p.id
,name
,CONVERT(VARCHAR(10), DueDate, 101)
from #Patient p
join (select
ID
,MIN(due) as DueDate
from #Vaccine
group by id) v
on p.ID = v.ID

IRK

anishap
Yak Posting Veteran

61 Posts

Posted - 2011-01-18 : 11:16:26

Thanks Ranjit, it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources