SELECT
 tickets.ticketid,
 tickets.tickettype,
 tickets_tickettype_lu.tickettypedesc,
 tickets.stage,
 tickets.position,
 tickets.sponsor,
 tickets.dev,
 tickets.qa,
 DATEDIFF(DAY, ticket_history_assignment.savedate, GETDATE()) as 'daysinqueue'
FROM
 dbo.tickets WITH (NOLOCK)
 LEFT OUTER JOIN dbo.tickets_tickettype_lu WITH (NOLOCK) ON tickets.tickettype = tickets_tickettype_lu.tickettypeid
 LEFT OUTER JOIN dbo.tickets_history_assignment WITH (NOLOCK) ON tickets_history_assignment.ticketid = tickets.ticketid
 AND tickets_history_assignment.historyid = (
 SELECT
  MAX(historyid)
 FROM
  dbo.tickets_history_assignment WITH (NOLOCK)
 WHERE
  tickets_history_assignment.ticketid = tickets.ticketid
 GROUP BY
  tickets_history_assignment.ticketid
 )
WHERE
 tickets.sponsor = @sponsor

declare @tickets_history_assignment table (
 historyid int,
 ticketid int,
 sponsor int,
 dev int,
 qa int,
 savedate datetime
)

insert into @tickets_history_assignment values (1521402, 92774,20,14, 20, '2009-10-27 09:17:59.527')
insert into @tickets_history_assignment values (1521399, 92774,20,14, 42, '2009-08-31 12:07:52.917')
insert into @tickets_history_assignment values (1521311, 92774,100,14, 42, '2008-12-08 16:15:49.887')
insert into @tickets_history_assignment values (1521336, 92774,100,14, 42, '2009-01-16 14:27:43.577')

;WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY savedate DESC) AS rn
        FROM    @Table
        )
SELECT  rl.sponsor, ro.savedate
FROM    rows rl
CROSS APPLY
        (
        SELECT  TOP 1 rc.savedate
        FROM    rows rc
        JOIN    rows rn
        ON      rn.ticketid = rc.ticketid
                AND rn.rn = rc.rn + 1
                AND rn.sponsor <> rc.sponsor
        WHERE   rc.ticketid = rl.ticketid
        ORDER BY
                rc.rn
        ) ro
WHERE   rl.rn = 1

Msg 102, Level 15, State 1, Procedure usp_GetProjectRecordsByAssignment, Line 159
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Procedure usp_GetProjectRecordsByAssignment, Line 179
Incorrect syntax near ')'.

;WITH    rows AS

)

CREATE PROCEDURE [dbo].[usp_GetProjectRecordsByAssignment]
(
    @assigned numeric(18,0),
    @assignedtype numeric(18,0)
)
AS

SET NOCOUNT ON

WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY recordid ORDER BY savedate DESC) AS rn
        FROM    projects_history_assignment
        )
SELECT  projects_records.recordid,
        projects_records.recordtype,
        projects_recordtype_lu.recordtypedesc,
    	projects_records.stage,
    	projects_stage_lu.stagedesc,
    	projects_records.position,
    	projects_position_lu.positiondesc,
    	CASE projects_records.clientrequested
    		WHEN '1' THEN 'Yes'
    		WHEN '0' THEN 'No'
    	END AS clientrequested,
    	projects_records.reportingmethod,
    	projects_reportingmethod_lu.reportingmethoddesc,
    	projects_records.clientaccess,
    	projects_clientaccess_lu.clientaccessdesc,
    	projects_records.clientnumber,
    	projects_records.project,
    	projects_lu.projectdesc,
    	projects_records.version,
    	projects_version_lu.versiondesc,
    	projects_records.projectedversion,
    	projects_version_lu_projected.versiondesc AS projectedversiondesc,
    	projects_records.sitetype,
    	projects_sitetype_lu.sitetypedesc,
    	projects_records.title,
    	projects_records.module,
    	projects_module_lu.moduledesc,
    	projects_records.component,
    	projects_component_lu.componentdesc,
    	projects_records.loginusername,
    	projects_records.loginpassword,
    	projects_records.assistedusername,
    	projects_records.browsername,
    	projects_browsername_lu.browsernamedesc,
    	projects_records.browserversion,
    	projects_records.osname,
    	projects_osname_lu.osnamedesc,
    	projects_records.osversion,
    	projects_records.errortype,
    	projects_errortype_lu.errortypedesc,
    	projects_records.gsipriority,
    	projects_gsipriority_lu.gsiprioritydesc,
    	projects_records.clientpriority,
    	projects_clientpriority_lu.clientprioritydesc,
    	projects_records.scheduledstartdate,
    	projects_records.scheduledcompletiondate,
    	projects_records.projectedhours,
    	projects_records.actualstartdate,
    	projects_records.actualcompletiondate,
    	projects_records.actualhours,
    	CASE projects_records.billclient
    		WHEN '1' THEN 'Yes'
    		WHEN '0' THEN 'No'
    	END AS billclient,
    	projects_records.billamount,
    	projects_records.status,
    	projects_status_lu.statusdesc,
    	CASE CAST(projects_records.assigned AS VARCHAR(5))
    		WHEN '0' THEN 'N/A'
    		WHEN '10000' THEN 'Unassigned'
    		WHEN '20000' THEN 'Client'
    		WHEN '30000' THEN 'Tech Support'
    		WHEN '40000' THEN 'LMI Tech Support'
    		WHEN '50000' THEN 'Upload'
    		WHEN '60000' THEN 'Spider'
    		WHEN '70000' THEN 'DB Admin'
    		ELSE rtrim(users_assigned.nickname) + ' ' + rtrim(users_assigned.lastname)
    	END AS assigned,
    	CASE CAST(projects_records.assigneddev AS VARCHAR(5))
    		WHEN '0' THEN 'N/A'
    		WHEN '10000' THEN 'Unassigned'
    		ELSE rtrim(users_assigneddev.nickname) + ' ' + rtrim(users_assigneddev.lastname)
    	END AS assigneddev,
    	CASE CAST(projects_records.assignedqa AS VARCHAR(5))
    		WHEN '0' THEN 'N/A'
    		WHEN '10000' THEN 'Unassigned'
    		ELSE rtrim(users_assignedqa.nickname) + ' ' + rtrim(users_assignedqa.lastname)
    	END AS assignedqa,
    	CASE CAST(projects_records.assignedsponsor AS VARCHAR(5))
    		WHEN '0' THEN 'N/A'
    		WHEN '10000' THEN 'Unassigned'
    		ELSE rtrim(users_assignedsponsor.nickname) + ' ' + rtrim(users_assignedsponsor.lastname)
    	END AS assignedsponsor,
    	projects_records.clientcreated,
    	CASE projects_records.clientcreated
    		WHEN '1' THEN 'Yes'
    		WHEN '0' THEN 'No'
    	END AS clientcreateddesc,
    	CASE projects_records.clientcreated
    		WHEN '1' THEN rtrim(clientusers_createuser.firstname) + ' ' + rtrim(clientusers_createuser.lastname) + ' (Client)'
    		ELSE rtrim(users_createuser.nickname) + ' ' + rtrim(users_createuser.lastname)
    	END AS createuser,
    	projects_records.createdate,
    	projects_records.savedate,
    	projects_resolution.sitesaffected,
    	projects_sitesaffected_lu.sitesaffecteddesc,
    	DATEDIFF(DAY, projects_history_assignment.savedate, GETDATE()) as 'daysinqueue',
    	projects_records.iOnHitList,
    	projects_records.changetype
FROM
    dbo.projects_records WITH (NOLOCK)
    LEFT OUTER JOIN dbo.projects_recordtype_lu WITH (NOLOCK) ON projects_records.recordtype = projects_recordtype_lu.recordtypeid
    LEFT OUTER JOIN dbo.projects_stage_lu WITH (NOLOCK) ON projects_records.stage = projects_stage_lu.stageid
    LEFT OUTER JOIN dbo.projects_position_lu WITH (NOLOCK) ON projects_records.position = projects_position_lu.positionid
    LEFT OUTER JOIN dbo.projects_reportingmethod_lu WITH (NOLOCK) ON projects_records.reportingmethod = projects_reportingmethod_lu.reportingmethodid
    LEFT OUTER JOIN dbo.projects_lu WITH (NOLOCK) ON projects_records.project = projects_lu.projectid
    LEFT OUTER JOIN dbo.projects_version_lu WITH (NOLOCK) ON projects_records.version = projects_version_lu.versionid
    LEFT OUTER JOIN dbo.projects_version_lu projects_version_lu_projected WITH (NOLOCK) ON projects_records.projectedversion = projects_version_lu_projected.versionid
    LEFT OUTER JOIN dbo.projects_sitetype_lu WITH (NOLOCK) ON projects_records.sitetype = projects_sitetype_lu.sitetypeid
    LEFT OUTER JOIN dbo.projects_module_lu WITH (NOLOCK) ON projects_records.module = projects_module_lu.moduleid
    LEFT OUTER JOIN dbo.projects_component_lu WITH (NOLOCK) ON projects_records.component = projects_component_lu.componentid
    LEFT OUTER JOIN dbo.projects_browsername_lu WITH (NOLOCK) ON projects_records.browsername = projects_browsername_lu.browsernameid
    LEFT OUTER JOIN dbo.projects_osname_lu WITH (NOLOCK) ON projects_records.osname = projects_osname_lu.osnameid
    LEFT OUTER JOIN dbo.projects_errortype_lu WITH (NOLOCK) ON projects_records.errortype = projects_errortype_lu.errortypeid
    LEFT OUTER JOIN dbo.projects_resolution WITH (NOLOCK) ON projects_records.recordid = projects_resolution.recordid
    LEFT OUTER JOIN dbo.projects_sitesaffected_lu WITH (NOLOCK) ON projects_resolution.sitesaffected = projects_sitesaffected_lu.sitesaffectedid
    LEFT OUTER JOIN dbo.projects_gsipriority_lu WITH (NOLOCK) ON projects_records.gsipriority = projects_gsipriority_lu.gsipriorityid
    LEFT OUTER JOIN dbo.projects_clientpriority_lu WITH (NOLOCK) ON projects_records.clientpriority = projects_clientpriority_lu.clientpriorityid
    LEFT OUTER JOIN dbo.projects_status_lu WITH (NOLOCK) ON projects_records.status = projects_status_lu.statusid
    LEFT OUTER JOIN dbo.projects_clientaccess_lu WITH (NOLOCK) ON projects_records.clientaccess = projects_clientaccess_lu.clientaccessid
    LEFT OUTER JOIN dbo.users users_assigned WITH (NOLOCK) ON projects_records.assigned = users_assigned.userid
    LEFT OUTER JOIN dbo.users users_assigneddev WITH (NOLOCK) ON projects_records.assigneddev = users_assigneddev.userid
    LEFT OUTER JOIN dbo.users users_assignedqa WITH (NOLOCK) ON projects_records.assignedqa = users_assignedqa.userid
    LEFT OUTER JOIN dbo.users users_assignedsponsor WITH (NOLOCK) ON projects_records.assignedsponsor = users_assignedsponsor.userid
    LEFT OUTER JOIN dbo.users users_createuser WITH (NOLOCK) ON projects_records.createuser = users_createuser.userid
    LEFT OUTER JOIN dbo.clientusers clientusers_createuser WITH (NOLOCK) ON projects_records.createuser = clientusers_createuser.userid
    LEFT OUTER JOIN dbo.projects_history_assignment WITH (NOLOCK) ON projects_history_assignment.recordid = projects_records.recordid
    AND projects_history_assignment.historyid = (
    	SELECT  ro.historyid
    	FROM    rows rl
    	CROSS APPLY
    			(
    			SELECT  TOP 1 rc.historyid
    			FROM    rows rc
    			JOIN    rows rn
    			ON      rn.recordid = rc.recordid
    					AND rn.rn = rc.rn + 1
    					AND rn.assigned <> rc.assigned
    			WHERE   rc.recordid = rl.recordid
    			ORDER BY
    					rc.rn
    			) ro
    	WHERE   rl.rn = 1
    			AND rl.recordid = projects_records.recordid
    )

WHERE
    (@assignedtype='0' and projects_records.assigned = @assigned)
OR  (@assignedtype='1' and projects_records.assigneddev = @assigned)
OR  (@assignedtype='2' and projects_records.assignedqa = @assigned)
OR  (@assignedtype='3' and projects_records.assignedsponsor = @assigned)
OR  (@assignedtype='4' and projects_records.createuser = @assigned)

WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY savedate DESC) AS rn
        FROM    @Table
        )
SELECT  projects_records.recordid,
        projects_records.recordtype,
        /* skipped */
        AND projects_history_assignment.historyid = (
        SELECT  ro.historyid
        FROM    rows rl
        CROSS APPLY
                (
                SELECT  TOP 1 rc.savedate
                FROM    rows rc
                JOIN    rows rn
                ON      rn.recordid = rc.recordid
                        AND rn.rn = rc.rn + 1
                        AND rn.assigned <> rc.assigned
                WHERE   rc.recordid = rl.recordid
                ORDER BY
                        rc.rn
                ) ro
        WHERE   rl.rn = 1
                AND rl.recordid = projects_records.recordid
        )