10.06.2011 Update: I forgot to separate out the FieldInternalNames for the start and end date. See updated code.

This solution is an answer to a request from Colin Blake:

Hey Alexander,

I’ve been browsing through your blog (amazing!) and have not been able
to come up with anything yet using your posted solutions so I was
hoping you could help me or get me pointed in the right direction. I
have a calender that holds our “On-Call” information. I have added a
custom column to the calender that holds a text value(the name of the
on call person). What I would like to do is is on another Web Part
Page is have the text value from the custom “on-call” column for the
current week displayed in a CEWP Web Part. Is this something that
could be easily done?

Thanks,
Colin Blake

This solution is designed to be put directly into a CEWP and will insert the name of the person “on call” in a placeholder <div>.

Note to SharePoint 2010 users:
Add this code to a text file and put it in a document library, then use the content link option on the CEWP to link to this code. This is necessary to overcome a “bug” in the CEWP handling when editing a SP2010 page. If you put a script that generates HTML directly into a CEWP, the HTML is accumulated when editing the page.

CEWP code:

<div id="insertOnCallNameHere"></div>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.min.js"></script>
<script type="text/javascript">

// Define list name or GUID
var calendarName = "OnCallCalendar";
// Define the FieldInternalName to pull in the value from
var fieldToReturn = 'OnCallPerson';
// Define the start and end date fields
var startDateFIN = 'EventDate';
var endDateFIN = 'EndDate';
// Call the cunction
var onCallArr = getOnCallInfo(calendarName,fieldToReturn,startDateFIN,endDateFIN);

// Handle the result
var buffer = [];
$.each(onCallArr,function(i,obj){
	// obj.url = hyperlink to profile page
	// obj.name = name
	// obj.userId = user id
	// url and userid is available only when "fieldToReturn" is a people picker.
	buffer.push("<div>"+obj.url+"</div>");
});
$("#insertOnCallNameHere").html(buffer.join(''));

// ****************************************************************
//	Do not edit below this line
// ****************************************************************

function getOnCallInfo(listName,fieldNameToReturn,startDateFIN,endDateFIN){
	var result = [];
	var queryBuffer = [];
		queryBuffer.push("<Where>");
		queryBuffer.push("<And>");
		queryBuffer.push("<Leq><FieldRef Name='"+startDateFIN+"' /><Value Type='DateTime'><Today /></Value></Leq>");
		queryBuffer.push("<Geq><FieldRef Name='"+endDateFIN+"' /><Value Type='DateTime'><Today /></Value></Geq>");
		queryBuffer.push("</And>");
		queryBuffer.push("</Where>");
	var res = spjs_QueryItems({listName:listName,query:queryBuffer.join(''),viewFields:['ID',fieldNameToReturn]});
	$.each(res.items,function(i,item){
		if(item[fieldNameToReturn]!==null){
			var split = item[fieldNameToReturn].split(';#');
			var name = split[1];
			var userId = split[0];
			if(split.length===2&&!isNaN(parseInt(split[0],10))){
			result.push({url:"<a href='"+L_Menu_BaseUrl+"/_layouts/userdisp.aspx?Force=True&ID="+userId+"' target='_blank'>"+name+"</a>",
						 name:name,
						 userId:userId});
			}else{
				if(split.length===2){
					name = split[1];
				}
				result.push({url:'URL: This value is available using a people picker only.',
							 name:name,
							 userId:'userId: This value is available using a people picker only.'});
			}	
		}	
	});
	return result;		
}

function spjs_QueryItems(argObj){
	if(argObj.listBaseUrl==undefined)argObj.listBaseUrl=L_Menu_BaseUrl;
	if(argObj.listName==undefined || (argObj.query==undefined && argObj.viewName==undefined)){
		alert("Missing parameters!nnYou must provide a minimum of "listName", "query" or "viewName" and "viewFields".");
		return;
	}
	var content = spjs_wrapQueryContent({'listName':argObj.listName,'query':argObj.query,'viewName':argObj.viewName,'viewFields':argObj.viewFields,'rowLimit':argObj.rowLimit,'pagingInfo':argObj.pagingInfo});
	var result = {'count':-1,'nextPagingInfo':'',items:[]};	
	spjs_wrapSoapRequest(argObj.listBaseUrl + '/_vti_bin/lists.asmx', 'http://schemas.microsoft.com/sharepoint/soap/GetListItems', content, function(data){
		result.count = $(data).find("[nodeName='rs:data']").attr('ItemCount');
		result.nextPagingInfo = $(data).find("[nodeName='rs:data']").attr('ListItemCollectionPositionNext');
		$(data).find("[nodeName='z:row']").each(function(idx, itemData){
			var fieldValObj = {}
			$.each(argObj.viewFields,function(i,field){
				var value = $(itemData).attr('ows_' + field);
				if(value == undefined) value = null;
				fieldValObj[field]=value;
			});
			result.items.push(fieldValObj);
		});
	});
	return result;
}

function spjs_wrapQueryContent(paramObj){
	var result = [];
	result.push('<GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">');
	result.push('<listName>' + paramObj.listName + '</listName>');
	if(paramObj.viewName!=undefined && paramObj.viewName!=''){
		result.push('<viewName>' + paramObj.viewName + '</viewName>');
	}
	if(paramObj.query != null && paramObj.query != ''){
		result.push('<query><Query xmlns="">');
		result.push(paramObj.query);
		result.push('</Query></query>');
	}
	if(paramObj.viewFields!=undefined && paramObj.viewFields!='' && paramObj.viewFields.length > 0){
		result.push('<viewFields><ViewFields xmlns="">');
		$.each(paramObj.viewFields, function(idx, field){
			result.push('<FieldRef Name="' + field + '"/>');
		});
		result.push('</ViewFields></viewFields>');
	}
	// A view overrides the itemlimit
	if(paramObj.viewName==undefined){
		if(paramObj.rowLimit != undefined && paramObj.rowLimit > 0){
			result.push('<rowLimit>' + paramObj.rowLimit + '</rowLimit>');
		}else{
		    result.push('<rowLimit>100000</rowLimit>');
		}
	}
	result.push('<queryOptions><QueryOptions xmlns=""><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>');
	if(paramObj.pagingInfo != undefined && paramObj.pagingInfo != null && paramObj.pagingInfo != '')
		result.push('<Paging ListItemCollectionPositionNext="' + paramObj.pagingInfo.replace(/&/g, '&') + '" />');
	result.push('</QueryOptions></queryOptions>');
	result.push('</GetListItems>');
	return result.join('');
}

function spjs_wrapSoapRequest(webserviceUrl,requestHeader,soapBody,successFunc){
	var xmlWrap = [];
		xmlWrap.push("<?xml version='1.0' encoding='utf-8'?>");
		xmlWrap.push("<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>");
		xmlWrap.push("<soap:Body>");
		xmlWrap.push(soapBody);
		xmlWrap.push("</soap:Body>");
		xmlWrap.push("</soap:Envelope>");
		xmlWrap = xmlWrap.join('');
	$.ajax({
		async:false,
		type:"POST",
		url:webserviceUrl,
		contentType:"text/xml; charset=utf-8",
		processData:false,
		data:xmlWrap,
		dataType:"xml",
		beforeSend:function(xhr){
			xhr.setRequestHeader('SOAPAction',requestHeader);
		},
		success:successFunc,
		error:function(xhr){
			alert(xhr.status+"n"+xhr.responseText);
		}
	});
}
</script> 

The parameters

calendarName: The list name or list GUID of the list/calendar.
fieldToReturn: The FieldInternalName of the field to return the value from.
startDateFIN: The FieldInternalName of the start date field.
endDateFIN: The FieldInternalName of the end date field.

The returnvalue

The returnvalue from call to the function “getOnCallInfo” is an array of objects. The object has three properties:
url = a link to the SharePoint user info for the user.
userId = the userId from SharePoints user profile.
name = the name stored in the field.

The property “url” and “userId” is for use with people pickers only.

Hope someone can make use of this.
Alexander