Selecting a max(date field) in an outer join

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

Selecting a max(date field) in an outer join

Author

Topic

balmainboy
Starting Member

3 Posts

Posted - 2013-05-23 : 03:38:56

The pseudo-code of what I want to write is:
select c.*, max(d.last_date)
into #client_last_contact
from #client_list c
left outer join #all_previous_contacts d
on c.uid = d.uid
and c.area_code = d.area_code

The primary key of #client_list is uid and area_code.
The table was created for a specific date range.
Now I want to find the most recent contact prior to the date range, but there will be UIDs who have not been contacted before. Hence the outer join.
Running the query above, iSQL asks me to group all the columns in #client_list.
Is there a way to restructure the query so a GROUP section is not needed?

Using Sybase SQL, but any suggestions from other platforms I will be able to convert.
Thanks! Nigel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 03:45:59

these are how we do it in t-sql


select c.*,d.last_date
into #client_last_contact
from #client_list c
outer apply (select max(last_date) as last_date
             from #all_previous_contacts 
             where c.uid = uid
             and c.area_code = area_code
             and last_date < c.daterangefield
            )d

daterangefield is field in #client_list containing daterange values

ANSI generic way would be like this


SELECT c.*,
(SELECT MAX(last_date)
 FROM #all_previous_contacts 
 WHERE c.uid = uid
 and c.area_code = area_code
 and c.daterange > last_date
) AS last_date
into #client_last_contact
from #client_list c

you can try both and see if it works in Sybase

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

balmainboy
Starting Member

3 Posts

Posted - 2013-05-23 : 21:50:52

Oh, to have OUTER APPLY in Sybase SQL - it looks quite useful.

The ANSI generic code works, except it doesn't pick up #client_list records that can't be linked to #all_previous_contacts records - i.e. no OUTER JOIN function happening.

I tried an ugly hybrid of the two examples, to try and get a left join, but received the error message "Column 'uid' not found" :

SELECT c.*, acs.previous_date
into #client_last_contact
from #client_list c

left join (SELECT MAX(d.last_date) as previous_date
FROM #all_comms_seps d
WHERE c.uid = d.uid
and c.area_code = d.area_code
and c.first_ctc_date > d.last_date
) acs
on c.uid = acs.uid and c.area_code = acs.area_code ;

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 00:23:54

you've not selected uid in inside query


SELECT c.*, acs.previous_date
into #client_last_contact
from #client_list c 

left join (SELECT d.uid,MAX(d.last_date) as previous_date
FROM #all_comms_seps d 
WHERE c.uid = d.uid
and c.area_code = d.area_code
and c.first_ctc_date > d.last_date
GROUP BY d.uid 
) acs
on c.uid = acs.uid and c.area_code = acs.area_code ;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

balmainboy
Starting Member

3 Posts

Posted - 2013-05-24 : 00:58:23

Yes, success!

I put area_code in the SELECT command in line 6 and added it to the GROUP in line 11, and this now also selects non-matching records from #client_list:

SELECT c.*,
acs.previous_date as previous_date
into #client_last_contact
from #client_list c

left join (SELECT d.uid, d.area_code, MAX(d.last_date) as previous_date
FROM #all_comms_seps d, #client_list c
WHERE c.uid = d.uid
and c.area_code = d.area_code
and c.first_ctc_date > d.last_date
GROUP BY d.uid, d.area_code
) acs
on c.uid = acs.uid and c.area_code = acs.area_code ;

Thanks so much for your help Visakh!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 00:59:45

No problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources