Select Max into one row 2

JohnnyLong · Apr 17, 2007

Hi,

I have 2 tables, rc_Contacts and rc_Calls.
Each contact can have many calls.
rc_Calls has a CompletedFlag column of 1 or 0.

I need to select the last completed call and the last uncompleted call and display the results in one row.

Surname Subject MaxCallDate1 Subject MaxCallDate2
Smith No reply 15/04/2007 Spoke with.. 17/04/2007
Brown Call back 12/03/2007 NULL NULL

This is what I have so far:

SELECT co.Surname, ca.Subject, Max(ca.CallDate) AS MaxCallDate1, ca1.Subject, Max(ca1.CallDate) AS MaxCallDate2

FROM rc_Contacts co

JOIN rc_Calls ca
ON co.ContactID = ca.ContactID

LEFT OUTER JOIN rc_Calls ca1
ON co.ContactID = ca1.ContactID

WHERE co.BranchCode = '233'

GROUP BY co.Surname, ca.Subject, ca1.Subject, ca.CallDate, ca1.CallDate

ORDER BY co.Surname

However, this does not select the latest (by date) records, it selects all records, and where do I put the Where CompletedFlag = 0 / Where CompletedFlag = 1 ??

Any help much appreciated.

I am using SQL 7

John

AlexCuse · Apr 17, 2007

Maybe this? You can filter for ca/ca1 CompletedFlag values in your join conditions or where clauses.

Code:

[COLOR=blue]SELECT[/color] co.Surname
, ca.Subject
, [COLOR=#FF00FF]Max[/color](ca.CallDate) [COLOR=blue]AS[/color] MaxCallDate1
, ca1.Subject, [COLOR=#FF00FF]Max[/color](ca1.CallDate) [COLOR=blue]AS[/color] MaxCallDate2

[COLOR=blue]FROM[/color] rc_Contacts co

[COLOR=blue]JOIN[/color] rc_Calls ca 
[COLOR=blue]ON[/color] co.ContactID = ca.ContactID
and ca.CompletedFlag = 0

[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] rc_Calls ca1
[COLOR=blue]ON[/color] co.ContactID = ca1.ContactID
and ca1.CompletedFlag = 1

[COLOR=blue]WHERE[/color] co.BranchCode = [COLOR=red]'233'[/color] 

[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] co.Surname, ca.Subject, ca1.Subject, ca.CallDate, ca1.CallDate

[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] co.Surname

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

JohnnyLong · Apr 17, 2007

Thanks Alex, that works. Any idea why my Max(Date) isn't working though. I'm getting all the calls for each contact whereas I only want the latest completed call and the latest uncompleted call.

Thanks

AlexCuse · Apr 17, 2007

I think its' your group by. You should only be grouping by surname/subject. Do you want to show only the 1 subject that the most recent call was in regards to? If so, thats' a bit different. If this is what you need let me know, and I will do my best o explain how you will do this.

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

JohnnyLong · Apr 17, 2007

I need to show the subject for both calls, the most recent completed & the most recent uncompleted. Here are my fields:

SELECT co.BranchCode, co.Representative, rc_Clients.ClientName, co.DecisionMaker, co.ContactSurname, co.ContactFirstname, co.SalesType, co.SalesStatus, rc_Clients.Terms, rc_Clients.TermsReturned, ca.CompletedFlag, ca.Subject MAX(ca.CallDate) AS MaxOfCallDate1, ca1.CompletedFlag, ca1.Subject, Max(ca1.CallDate) AS MaxCallDate2

This means for the query to run I have to list them all in my GROUP BY, I think?!

Appreciate the help.

gmmastros · Apr 17, 2007

Try this...

Code:

[COLOR=blue]SELECT[/color] co.Surname, 
       ca.Subject, 
       ca.MaxCallDate1, 
       ca1.Subject, 
       ca1.MaxCallDate2
[COLOR=blue]FROM[/color]   rc_Contacts co
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]JOIN[/color] (

         [COLOR=blue]Select[/color] ContactId,
                Subject,
                MaxCallDate1
         [COLOR=blue]From[/color]   rc_calls
                [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
                  [COLOR=blue]Select[/color] ContactId, [COLOR=#FF00FF]Max[/color](CallDate) [COLOR=blue]As[/color] MaxCallDate1
                  [COLOR=blue]From[/color]   rc_Calls
                  [COLOR=blue]Where[/color]  CompletedFlag = 0
                  [COLOR=blue]Group[/color] [COLOR=blue]By[/color] ContactId
                  ) [COLOR=blue]As[/color] A
                  [COLOR=blue]On[/color] rc_calls.ContactId = A.ContactId
                  and rc_Calls.CallDate = A.MaxCallDate1
         ) ca 
         [COLOR=blue]ON[/color] co.ContactID = ca.ContactID
       [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] (
         [COLOR=blue]Select[/color] ContactId,
                Subject,
                MaxCallDate1
         [COLOR=blue]From[/color]   rc_calls
                [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
                   [COLOR=blue]Select[/color] ContactId, [COLOR=#FF00FF]Max[/color](CallDate) [COLOR=blue]As[/color] MaxCallDate2
                   [COLOR=blue]From[/color]   rc_Calls
                   [COLOR=blue]Where[/color]  CompletedFlag = 1
                   [COLOR=blue]Group[/color] [COLOR=blue]By[/color] ContactId
                   ) A
                  [COLOR=blue]On[/color] rc_calls.ContactId = A.ContactId
                  and rc_Calls.CallDate = A.MaxCallDate1
         ) ca1
         [COLOR=blue]ON[/color] co.ContactID = ca1.ContactID
[COLOR=blue]WHERE[/color] co.BranchCode = [COLOR=red]'233'[/color] 
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] co.Surname

I couldn't test it, but I think it will do what you want.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

AlexCuse · Apr 17, 2007

Hey JohnnyLong -

I think I got all the kinks worked out of this, but I did not test it. This should get you on the right track though.

Code:

[COLOR=blue]SELECT[/color] co.Surname
, ca.Subject
, ca.MCD [COLOR=blue]AS[/color] MaxCallDate1
, ca1.Subject
, ca1.MCD [COLOR=blue]AS[/color] MaxCallDate2

[COLOR=blue]FROM[/color] rc_Contacts co

[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
(
[COLOR=blue]select[/color] a.ContactID, a.Subject, b.MCD
[COLOR=blue]from[/color] rc_Calls a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
	(
	[COLOR=blue]select[/color] ContactID, [COLOR=#FF00FF]Max[/color](CallDate) [COLOR=blue]as[/color] MCD
	[COLOR=blue]from[/color] rc_Calls
	[COLOR=blue]where[/color] CompletedFlag = 0
	[COLOR=blue]group[/color] [COLOR=blue]by[/color] ContactID
	) b
[COLOR=blue]on[/color] a.ContactID = b.ContactID
and a.CallDate = b.MCD
) 
ca 
[COLOR=blue]ON[/color] co.ContactID = ca.ContactID

[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
(
[COLOR=blue]select[/color] z.ContactID, z.Subject, y.MCD
[COLOR=blue]from[/color] rc_Calls z
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
	(
	[COLOR=blue]select[/color] ContactID, [COLOR=#FF00FF]Max[/color](CallDate) [COLOR=blue]as[/color] MCD
	[COLOR=blue]from[/color] rc_Calls
	[COLOR=blue]where[/color] CompletedFlag = 0
	[COLOR=blue]group[/color] [COLOR=blue]by[/color] ContactID
	) y
[COLOR=blue]on[/color] z.ContactID = y.ContactID 
and z.CallDate = y.MCD
) 
ca1 

[COLOR=blue]on[/color] co.ContactID = ca1.ContactID

[COLOR=blue]WHERE[/color] co.BranchCode = [COLOR=red]'233'[/color] 

[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] co.Surname

Hope it helps,
Alex

Ignorance of certain subjects is a great part of wisdom

AlexCuse · Apr 17, 2007

>>>I think I got all the kinks worked out of this

Famous last words!

Of course, the second instance of

Code:

where CompletedFlag = 0

Should be

Code:

where CompletedFlag = 1

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

JohnnyLong · Apr 18, 2007

Thank you so much Alex and George you are life savers, I would never have worked this out myself. I have tweaked the code to include counts and a few more joins, but it appears to be doing exactly want I want.

SELECT co.BranchCode, co.Representative, cl.ClientName, co.DecisionMaker, co.ContactSurname, co.ContactFirstname,
co.SalesType, co.SalesStatus, cl.ClientTerms, cl.TermsReturned, ca.MCD AS LastCallDate, ca.Subject,
ca1.MCD AS NextCallDate, ca1.Subject, me1.MDT AS LastMeetingDate, me.MDT AS NextMeetingDate, s.OpenOrders, p.OpenPlacements

FROM rc_Contacts co

INNER JOIN rc_Clients cl ON co.ClientID = cl.ClientID

INNER JOIN
(
select a.ContactID, a.Subject, b.MCD
from rc_Calls a
inner join
(
select ContactID, Max(CallDate) as MCD
from rc_Calls
where CompletedFlag = 0
group by ContactID
) b
on a.ContactID = b.ContactID
and a.CallDate = b.MCD
)
ca
ON co.ContactID = ca.ContactID

LEFT OUTER JOIN
(
select z.ContactID, z.Subject, y.MCD
from rc_Calls z
inner join
(
select ContactID, Max(CallDate) as MCD
from rc_Calls
where CompletedFlag = 1
group by ContactID
) y
on z.ContactID = y.ContactID
and z.CallDate = y.MCD
)
ca1

on co.ContactID = ca1.ContactID

LEFT OUTER JOIN
(
select m.ContactID, m.Subject, c.MDT
from rc_Meetings m
inner join
(
select ContactID, Max(MeetingDateTime) as MDT
from rc_Meetings
where CompletedFlag = 0
group by ContactID
) c
ON m.ContactID = c.ContactID
and m.MeetingDateTime = c.MDT
)
me
ON co.ContactID = me.ContactID

LEFT OUTER JOIN
(
select n.ContactID, n.Subject, o.MDT
from rc_Meetings n
inner join
(
select ContactID, Max(MeetingDateTime) as MDT
from rc_Meetings
where CompletedFlag = 1
group by ContactID
) o
ON n.ContactID = o.ContactID
and n.MeetingDateTime = o.MDT
)
me1
ON co.ContactID = me1.ContactID

LEFT OUTER JOIN
(
select ContactID, Count(ContactID) AS OpenOrders
from rc_Orders
where completedFlag = 0
group by ContactID
) s
ON co.ContactID = s.ContactID

LEFT OUTER JOIN
(
select ContactID, Count(ContactID) AS OpenPlacements
from rc_Placements
where completedFlag = 0
group by ContactID
) p
ON co.ContactID = p.ContactID

WHERE co.BranchCode Like '233%'

ORDER BY co.BranchCode, co.ContactSurname

Any suggestions appreciated.

Thanks again,

John

AlexCuse · Apr 18, 2007

Wow, you sure did add a lot! It looks OK to me, but its' kind of hard to read. Luckily, there is a remedy you can use to make your posts look nicer. If you click the 'process tgml' link below the area where you type your post you will see how to add 'code tags' to your post (so that code is formatted better). Also, you might want to check out this cool application that does it automatically for you (and adds colors):

http://www.tek-tips.com/viewthread.cfm?qid=1350140&page=1

Glad you got it working

Alex

Ignorance of certain subjects is a great part of wisdom

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Select Max into one row 2

JohnnyLong

Programmer

AlexCuse

Programmer

JohnnyLong

Programmer

AlexCuse

Programmer

JohnnyLong

Programmer

gmmastros

Programmer

AlexCuse

Programmer

AlexCuse

Programmer

JohnnyLong

Programmer

AlexCuse

Programmer

Similar threads

Part and Inventory Search

Sponsor