Merge two date columns in a webpart view

Question

Here's a little (maybe big actually) challenge i'd like to face, but I'm not sure which good start I should take so as to resolve it. I have two lists, each one has a day/time type column, and other columns.

Let's assume for beginning that these two date/time can't be the same site column (sorry).

My challenge is the following : I'd like to display the content of these two lists within a single webpart view and "merge" (visually) these two distinct "date/time" columns in one column.

It seems like DVWP and joins(union) actions may help me, but are there other ideas to start with ?

Thank you by advance for your help and ideas. :)

Answer 1

how I achieved this was to create a class for the fields I wanted from the two lists, then added my lists to my newly created list and sorted it by date.

The purpose for me doing this was recent news across 25 site collections, pulling the latest 6 from each, merging them to a single list and then displaying the latest 6 from all.

Create the class and list:

private class RecentActivity
{
    ....
}
...
...
List<RecentActivity> RecentNews = new List<RecentActivity>();

Add to the list:

RecentActivity recent = new RecentActivity(PostTitle, "News", TimePosted, PostUrl, Body, user, CommunityName);
RecentNews.Add(recent);

Sort the list:

var RecentNewsSorted = RecentNews.OrderByDescending(c => c.Time.Ticks);

Process the list:

q.Query = "...";
foreach (SPListItem item in olist.GetItems(q))
{ ... }

If you want to cache the list you can do easily or do whatever you need with the data.

The only technical part that is a bit difficult is the ordering by the time, but as you see by the above sort it is pretty straight forward.