My brain isn't functioning and I can't work out this problem I've got.

There is a table with 5 datetime fields for different actions on the site. In most cases a visitor won't go to all actions so only 1 of the datetime fields has a value and the rest are NULL. I need to go through each row, find the datetime field that has the MAX value between all the other datetime fields and insert it into another table. Each row has a unique session ID.

I'm stuck.. any ideas?

My brain isn't functioning and I can't work out this problem I've got.

There is a table with 5 datetime fields for different actions on the site. In most cases a visitor won't go to all actions so only 1 of the datetime fields has a value and the rest are NULL. I need to go through each row, find the datetime field that has the MAX value between all the other datetime fields and insert it into another table. Each row has a unique session ID.

I'm stuck.. any ideas?

You could select max from column 1, union to select max from column 2 etc etc then wrap a select max around that. Sloppy but it would probably work :)

Select Max(d_date) from(
Select Max(d_date1) from Table
Union
Select Max(d_date2) from Table
Union
Select Max(d_date3) from Table
Union
Select Max(d_date4) from Table
Union
Select Max(d_date5) from Table
)

Edit: Hehe beaten to the punch by Hogfather :)

Edit2: You will need to alias the columns to a single column name.

i like the union suggestion, more elegent than my solution, but if it helps the

SQL statement could be dealt with like so:

create temp table with sessionid & date.

loop through all rows and set x = sessionid of x (can't remember exact code to loop though, something to do with carrots)

insert into tmptable (sessionid, date) values (select sessionid, date1 as date from realtable where clientid = x)
insert into tmptable (sessionid, date) values (select sessionid, date2 as date from realtable where clientid = x)
insert into tmptable (sessionid, date) values (select sessionid, date3 as date from realtable where clientid = x)
insert into tmptable (sessionid, date) values (select sessionid, date4 as date from realtable where clientid = x)
insert into tmptable (sessionid, date) values (select sessionid, date5 as date from realtable where clientid = x)

select (max) from tmptable where clientid = x? (update table/ add to temporary table / output part as dataset, depending on what your trying to achieve)

end loop

Grouping by session_id ?
Basically what Hogfather said

declare @your_table table 

(   

    session_id int Primary Key,

    d1 datetime,

    d2 datetime,

    d3 datetime,

    d4 datetime,

    d5 datetime

)  



insert into @your_table values (1, '20110101', '20110201', null, null, null)

insert into @your_table values (2, '20110201', '20110101', null, null, null)

insert into @your_table values (3, null, null, null, null, null) 

insert into @your_table values (4, null, null, null, null, '20100501') 



select t1.session_id, max(dx)

  from @your_table t1

        inner join (select session_id, max(d1) [dx] from @your_table group by session_id union all

                    select session_id, max(d2) [dx] from @your_table group by session_id union all

                    select session_id, max(d3) [dx] from @your_table group by session_id union all

                    select session_id, max(d4) [dx] from @your_table group by session_id union all

                    select session_id, max(d5) [dx] from @your_table group by session_id) as t2 on t2.session_id = t1.session_id

 group by t1.session_id

are you sure this would work? wouldn't you need to specify the sessionid so that its not just a date reference but a date referenced against a session id? otherwise you would just get the very maximum date in the system instead of the maximum date for each user.

I could be wrong though and look forward to learning more :)

Dunno, I have quite limited knowledge of SQL, most of my stuff is pulling digital sales info from a DB and I only write it once because then I package it in to a VBA app. I posted what I would "try" but if it didn't work I would have it to our DBA and get him to sort it out for me ;)

End result is this.. nasty but it works;

SELECT a.Session_ID, MAX(a.DT) AS DT FROM
(
SELECT Session_ID, Landing1 AS DT FROM [2010_UserTracking]
UNION
SELECT Session_ID, Landing2 AS DT FROM [2010_UserTracking]
UNION
SELECT Session_ID, Landing3 AS DT FROM [2010_UserTracking]
UNION
SELECT Session_ID, Landing4 AS DT FROM [2010_UserTracking]
UNION
SELECT Session_ID, Landing5 AS DT FROM [2010_UserTracking]
) AS a
WHERE a.DT IS NOT NULL
GROUP BY a.Session_ID

Inline scalar function appropriate? Might optimise it a bit if lots of calls.

http://www.sqlmag.com/article/sql-server/inline-scalar-functions.aspx