Get Latest Records For Join Statement

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

General SQL Server Forums

New to SQL Server Programming

Get Latest Records For Join Statement

Author

Topic

boybles
Starting Member

23 Posts

Posted - 2009-03-14 : 14:29:17

This one has me stumped.

What SQL would I use to join the following tables :
(Event, EventDetail, Contact, Schedule) to get the desired result
where the times set (StartTime,EndTime) in the Schedule table are the most current (ie. latest RequestDate)?

[Event]
------
<ID|ContactID>
------------
1 |1
2 |1
3 |2

[EventDetail]
-------
<ID|EventID|EventName>
---------------------
1 |1|Office Party X
2 |2|John's Y Event
3 |3|Phone Training

[Contact]
---------------
<ID|ContactName>
---------------
1|Jim
2|Kim

[Schedule]
-------
<ID|EventID|RequestDate|StartTime|EndTime>
------------------------------------------
1|1|2/2/2003|12:30|14:30
2|1|2/5/2003|12:45|14:45
3|1|2/7/2003|13:00|15:00
4|2|4/4/2004|08:00|13:00
5|2|5/5/2004|09:30|14:00
6|3|6/6/2005|10:00|15:00

Desired Result:
<EventID|EventName|ContactName|StartTime|EndTime>
------------------------------------------------
1|Office Party X|Jim|13:00|15:00
2|John's Y Event|Jim|09:30|14:00
3|Phone Training|Kim|10:00|15:00

Please help!!

Thanks,
Anthony

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 15:19:32

Not Tested:

Select C.Contactname,E.Eventname,MAX(Sc.Starttime),MAX(Sc.EndTime)
from Contact c inner join Eventdetail E
on c.ID = E.EventID inner join Event m
on m.ContactID = c.ID
inner join Schedule sc
on sc.EventID = E.ID
Group by C.Contactname,E.Eventname

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-14 : 16:23:55

Sodeep, I think you missed to check the latest requestdate.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-14 : 16:24:42

You might want to change your query to this,

Select 
	a.eventId,
	E.EventName,
	Con.ContactName,
	a.StartTime,
	a.EndTime 
from 
	schedule a join
	(
	select 
		Id,
		EventId,
		max(RequestDate) MaxDate
	from 
		schedule 
	group by 
		Id,EventId
	)b
	on a.Id=b.Id and a.EventId=b.EventId and b.MaxDate=a.RequestDate

	join Eventdetail E on E.Id=b.Id join [Event] Ev 
	on Ev.Id=a.EventId join Contact Con on Con.Id=Ev.ContactId

boybles
Starting Member

23 Posts

Posted - 2009-03-14 : 19:02:35

sackets 2000,
Thank you for the suggestion. I tried it, but you'll see it didn't come out as expected. What could I be doing wrong?

Tony

Example Output:

<EventID|EventName|ContactName|StartTime|EndTime>
---------------------------------------------------
1|Phone Training|Jim|2003-02-07|13:00:00.000|2003-02-07 15:00:00.000
1|John's Y Event|Jim|2003-02-05|12:45:00.000|2003-02-05 14:45:00.000
1|Office Party X|Jim|2003-02-02|12:30:00.000|2003-02-02 14:30:00.000

-- Prepare sample data
DECLARE @Events TABLE (ID INT, ContactID INT)

INSERT @Events
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2

DECLARE @EventDetail TABLE(ID INT, EventID INT, EventName Varchar(20))

INSERT @EventDetail
SELECT 1, 1,'Office Party X' UNION ALL
SELECT 2, 2,'John''s Y Event' UNION ALL
SELECT 3, 3, 'Phone Training'

DECLARE @Contact TABLE (ID INT, ContactName VarChar(20))

INSERT @Contact
SELECT 1, 'Jim' UNION ALL
SELECT 2, 'Kim'

DECLARE @Schedule TABLE (ID INT, EventID INT, RequestDate DateTime, StartTime DateTime, EndTime DateTime)

INSERT @Schedule
SELECT 1, 1, '2/2/2003','2/2/2003 12:30:00 PM', '2/2/2003 2:30:00 PM' UNION ALL
SELECT 2, 1, '2/5/2003','2/5/2003 12:45:00 PM', '2/5/2003 2:45:00 PM' UNION ALL
SELECT 3, 1, '2/7/2003','2/7/2003 1:00:00 PM', '2/7/2003 3:00:00 PM' UNION ALL
SELECT 4, 2, '4/4/2004','4/4/2004 8:00:00 AM', '4/4/2004 1:00:00 PM' UNION ALL
SELECT 5, 2, '5/5/2004','5/5/2004 9:30:00 AM', '5/5/2004 2:00:00 PM' UNION ALL
SELECT 6, 3, '6/6/2005','6/6/2005 10:00:00 AM', '6/6/2005 3:00:00 PM'

-- Execute select

Select
a.eventId,
E.EventName,
Con.ContactName,
a.StartTime,
a.EndTime
from
@Schedule a join
(
select
Id,
EventId,
max(RequestDate) MaxDate
from
@Schedule
group by
Id,EventId
)b
on a.Id=b.Id and a.EventId=b.EventId and b.MaxDate=a.RequestDate

join @Eventdetail E on E.Id=b.Id join @Events Ev
on Ev.Id=a.EventId join @Contact Con on Con.Id=Ev.ContactId

boybles
Starting Member

23 Posts

Posted - 2009-03-14 : 19:11:51

Sorry..this is the output I was getting:

<EventID|EventName|ContactName|StartTime|EndTime>
---------------------------------------------------
1|Phone Training|Jim|2003-02-07 13:00:00.000|2003-02-07 15:00:00.000
1|John's Y Event|Jim|2003-02-05 12:45:00.000|2003-02-05 14:45:00.000
1|Office Party X|Jim|2003-02-02 12:30:00.000|2003-02-02 14:30:00.000

Please help!
Thanks
Tony

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 22:25:56

[code]DECLARE @Events TABLE (ID INT, ContactID INT)

INSERT @Events
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2

DECLARE @EventDetail TABLE(ID INT, EventID INT, EventName Varchar(20))

INSERT @EventDetail
SELECT 1, 1,'Office Party X' UNION ALL
SELECT 2, 2,'John''s Y Event' UNION ALL
SELECT 3, 3, 'Phone Training'

DECLARE @Contact TABLE (ID INT, ContactName VarChar(20))

INSERT @Contact
SELECT 1, 'Jim' UNION ALL
SELECT 2, 'Kim'

DECLARE @Schedule TABLE (ID INT, EventID INT, RequestDate DateTime, StartTime DateTime, EndTime DateTime)

INSERT @Schedule
SELECT 1, 1, '2/2/2003','2/2/2003 12:30:00 PM', '2/2/2003 2:30:00 PM' UNION ALL
SELECT 2, 1, '2/5/2003','2/5/2003 12:45:00 PM', '2/5/2003 2:45:00 PM' UNION ALL
SELECT 3, 1, '2/7/2003','2/7/2003 1:00:00 PM', '2/7/2003 3:00:00 PM' UNION ALL
SELECT 4, 2, '4/4/2004','4/4/2004 8:00:00 AM', '4/4/2004 1:00:00 PM' UNION ALL
SELECT 5, 2, '5/5/2004','5/5/2004 9:30:00 AM', '5/5/2004 2:00:00 PM' UNION ALL
SELECT 6, 3, '6/6/2005','6/6/2005 10:00:00 AM', '6/6/2005 3:00:00 PM'

-- Execute select

Select
a.eventId,
E.EventName,
Con.ContactName,
a.StartTime,
a.EndTime
from
@Schedule a join
(
select
EventId,
max(RequestDate) MaxDate
from
@Schedule
group by
EventId
)b
on a.EventId=b.EventId and b.MaxDate=a.RequestDate
join @Eventdetail E on E.Id=b.EventId join @Events Ev
on Ev.Id=a.EventId join @Contact Con on Con.Id=Ev.ContactId
Order by a.eventID[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 22:31:47

In SQL 2005:

DECLARE @Events TABLE (ID INT, ContactID INT)

INSERT @Events
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2

DECLARE @EventDetail TABLE(ID INT, EventID INT, EventName Varchar(20))

INSERT @EventDetail
SELECT 1, 1,'Office Party X' UNION ALL
SELECT 2, 2,'John''s Y Event' UNION ALL
SELECT 3, 3, 'Phone Training'

DECLARE @Contact TABLE (ID INT, ContactName VarChar(20))

INSERT @Contact
SELECT 1, 'Jim' UNION ALL
SELECT 2, 'Kim' 

DECLARE @Schedule TABLE (ID INT, EventID INT, RequestDate DateTime, StartTime DateTime, EndTime DateTime)

INSERT @Schedule
SELECT 1, 1, '2/2/2003','2/2/2003 12:30:00 PM', '2/2/2003 2:30:00 PM' UNION ALL
SELECT 2, 1, '2/5/2003','2/5/2003 12:45:00 PM', '2/5/2003 2:45:00 PM' UNION ALL
SELECT 3, 1, '2/7/2003','2/7/2003 1:00:00 PM', '2/7/2003 3:00:00 PM' UNION ALL
SELECT 4, 2, '4/4/2004','4/4/2004 8:00:00 AM', '4/4/2004 1:00:00 PM' UNION ALL
SELECT 5, 2, '5/5/2004','5/5/2004 9:30:00 AM', '5/5/2004 2:00:00 PM' UNION ALL
SELECT 6, 3, '6/6/2005','6/6/2005 10:00:00 AM', '6/6/2005 3:00:00 PM' 


-- Execute select

Select * from 
(Select 
a.eventId,
E.EventName,
Con.ContactName,
ROW_NUMBER () OVER(PARTITION BY a.EventID Order by a.RequestDate Desc)as ROWID,
a.StartTime,
a.EndTime 
from 
@Schedule a join
@Eventdetail E on E.Id=a.EventId join @Events Ev 
on Ev.Id=a.EventId join @Contact Con on Con.Id=Ev.ContactId
)Z
Where Z.ROWID =1
Order by EventID

boybles
Starting Member

23 Posts

Posted - 2009-03-18 : 03:01:47

Sodeep,
That works wonderfully. Thank you for your help/insight. Bravo.
Anthony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources