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)
