This is a follow-up to #1644748 where I successfully answered my own question, but Quassnoi helped me to realize that it was the wrong question. He gave me a solution that worked for my sample data, but I couldn't plug it back into the parent stored procedure because I fail at SQL 2005 syntax. So here is an attempt to paint the broader picture and ask what I actually need.

This is part of a stored procedure that returns a list of items in a bug tracking application I've inherited. There are are over 100 fields and 26 joins so I'm pulling out only the mostly relevant bits.

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

The area of interest is the daysinqueue subquery mess. The tickets_history_assignment table looks roughly as follows

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')

Whenever a ticket is saved, the current values for sponsor, dev and qa are stored in the tickets_history_assignment table with the ticketid and a timestamp. So it is possible for someone to change the value for qa, but leave sponsor alone.

What I want to know, based on all of these conditions, is the historyid of the record in the tickets_history_assignment table where the sponsor value was last changed so that I can calculate the value for daysinqueue. If a record is inserted into the history table, and only the qa value has changed, I don't want that record. So simply relying on MAX(historyid) won't work for me.

Quassnoi came up with the following which seemed to work with my sample data, but I can't plug it into the larger query, SQL Manager bitches about the WITH statement.

;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

I played with it yesterday afternoon and got nowhere because I don't fundamentally understand what is going on here and how it should fit into the larger context.

So, any takers?

UPDATE

Ok, here's the whole thing. I've been switching some of the table and column names in an attempt to simplify things so here's the full unedited mess.

snip - old bad code

Here are the errors:

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 ')'.

Line numbers are of course not correct but refer to

;WITH    rows AS

And the ')' char after the WHERE rl.rn = 1

)

Respectively

Is there a tag for extra super long question?

UPDATE #2

Here is the finished query for anyone who may need this:

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)