February 16, 2010 at 4:15 am
Hi all,
I have the following structure and data as a sample:
--Build the structures
if exists (select * from sysobjects where name = 'FilteredIncidentResolution')
drop table FilteredIncidentResolution
create table FilteredIncidentResolution(
activityid uniqueidentifier,
subject nvarchar(255),
incidentid uniqueidentifier,
createdon datetime,
timespent int)
if exists (select * from sysobjects where name = 'FilteredIncident')
drop table FilteredIncident
create table FilteredIncident(
incidentid uniqueidentifier,
title nvarchar(255),
subjectidname nvarchar(100),
statuscodename nvarchar(50),
responsiblecontactidname nvarchar(255),
customeridname nvarchar(255),
owneridname nvarchar(255))
--prepare variables
declare @rec1 uniqueidentifier
declare @rec2 uniqueidentifier
select @rec1 = NEWID()
select @rec2 = NEWID()
--insert sample data
insert into FilteredIncident (
incidentid,
title,
subjectidname,
statuscodename,
responsiblecontactidname,
customeridname,
owneridname)
select @rec1, 'Unable to sync laptop after allowing an update', 'Default Subject', 'Problem Solved', 'Joe Bloggs', 'Contoso Ltd', 'Brett Davis' union all
select @rec2, 'Problem with reading text when Quick Print option chosen', 'Default Subject', 'Problem Solved', 'Joe Bloggs', 'Contoso Ltd', 'Brett Davis'
insert into FilteredIncidentResolution(
activityid,
subject,
incidentid,
createdon,
timespent)
select NEWID(), 'Resolved by user - user error', @rec2, '2009-05-27 15:18:50.000', '15' union all
select NEWID(), 'Resolved by user - user error', @rec2, '2009-05-27 15:10:21.000', '10' union all
select NEWID(), 'Walked user through restarting services manually', @rec1, '2009-06-29 16:29:54.000', '5'
Now may challenge - the FilteredIncident table joins to the FilteredIncidentResolution table in the incidentid guid - I'm trying to write a select statement (for use in a report) that only joins the most recent FilteredIncidentResolution (based upon createdon) for the FilteredIncident record. As an example using the above data, I want my query to return 2 rows, one for incident 1 and one for incident 2. For incident 2 I want to see timespent = 15 etc... basically all of the info from the more recent of the 2 FilteredIncidentResolution records for that incident record.
Hope that makes sense!
Thanks in advance,
Brett
** Edited to clean up the sample SQL
February 16, 2010 at 4:31 am
here one method
with cteWithRown
as
(
select fi.*,ROW_NUMBER () over (partition by fi.incidentid order by createdon desc) as rown from FilteredIncident fi join FilteredIncidentResolution fir
on fi.incidentid = fir.incidentid
)
Select * from cteWithRown where rown =1
February 16, 2010 at 4:55 am
And an alternative:
SELECT CA.*
FROM dbo.FilteredIncident FI
CROSS
APPLY (
SELECT TOP (1)
*
FROM dbo.FilteredIncidentResolution FIR
WHERE FIR.incidentid = FI.incidentid
ORDER BY
FIR.createdon DESC
) CA;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply