Query To Create View / Report?

I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.

we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.

ABSENCES
AWAY = Start of an absence from the building
RETURN = End of an absence from the building
A person during their reisdency will have multiple Absences
An absence could start on the same day a previous absence ends
Absences vary in length
CS
CS0-CS10 (each is a different TYPE) of communicatio

[code]....

Query in DESIGN View

SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM CRM_CSLDB.dbo.vCommunication

[code]....

Results from QUERY in DESIGN VIEW

TYPE DATECOMPIDCOMMIDSEQ
AWAY2011-02-24 00:00:00.00051747531
RETURN2011-03-31 00:00:00.00051747542
AWAY2011-03-28 00:00:00.00064740681
RETURN2011-04-30 00:00:00.00064752972

[code]....

RESULTS FROM QUERY in SELECT TOP 11

TypeDateCompIDCommIDSeqAwayDaysReturnDate
AWAY2011-06-20 00:00:00.00016977564182011-06-28 00:00:00.000
AWAY2011-12-23 00:00:00.00015059050441122012-04-13 00:00:00.000
AWAY2011-10-09 00:00:00.0003534839461162011-10-25 00:00:00.000
AWAY2012-01-27 00:00:00.0003983890363942012-04-30 00:00:00.000
AWAY2012-03-09 00:00:00.0004064900615202012-03-29 00:00:00.000

[code]....

QUERY in DESIGN VIEW

SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM dbo.vCommunication INNER JOIN

[Code] .....

QUERY RESULTS IN SELECT

csTranscsTranDateCompanyIDComm_IDNameCommIDSeq
CS12009-07-27 00:00:00.0001176147Harrison Bailey 761471
CS32007-08-27 00:00:00.0002673777Dorothy Wheeler 737771
CS42011-12-02 00:00:00.0002685087Dorothy Wheeler 850872
CS52012-01-01 00:00:00.0002685446Dorothy Wheeler 854463

[Code] ....