Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
More records returned on left outer join using FilteredIncident and FilteredIncidentResolution RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I need to understand these filtered views. I've done a really simple left outer join to get all incidents logged and I want info from the resolved incident filtered view too. When I analyse the results the numbers varied so I took it down to basics.

    select COUNT(fi.incidentid) NumberOfRecords
from dbo.FilteredIncident fi
	left outer join dbo.FilteredIncidentResolution res
		on fi.incidentid = res.incidentid
where fi.createdon between '2013-01-01' and '2013-12-31'
GO
select COUNT(fi.incidentid) NumberOfRecords
from dbo.FilteredIncident fi
where fi.createdon between '2013-01-01' and '2013-12-31'

    When I run this I get these results:

    NumberOfRecords
    ---------------
    7928

    (1 row(s) affected)

    NumberOfRecords
    ---------------
    7728

    (1 row(s) affected)

    I must be being really dense here but I get an extra 200 records. I've tested my SQL JOIN knowledge on data I know really well by joining to a table with a join that will never be satisfied and I always get the same number of records returned from the left table so I really don't think it's my understanding of left outer join

    Can someone tell me why my results are different?

    Thanks

    Rich

    Wednesday, February 12, 2014 1:55 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Could it be that some of the cases were Closed, Reopened and then closed again which would result in more case resolution records than cases.

    Try a count of resolution records to cases where count > 1 and see if you get a total of 200.

    • Proposed as answer by munerz_95 Wednesday, February 12, 2014 2:02 PM
    • Marked as answer by Rich Whight Wednesday, February 12, 2014 3:03 PM
    Wednesday, February 12, 2014 2:01 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Could it be that some of the cases were Closed, Reopened and then closed again which would result in more case resolution records than cases.

    Try a count of resolution records to cases where count > 1 and see if you get a total of 200.

    • Proposed as answer by munerz_95 Wednesday, February 12, 2014 2:02 PM
    • Marked as answer by Rich Whight Wednesday, February 12, 2014 3:03 PM
    Wednesday, February 12, 2014 2:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    That did it. Managed to get what I wanted like this (without the full select list of fields)

    With IncidentsResolved as
(
select	res.incidentid
		, res.createdon
		, res.owneridname
		, res.createdbyname
		, res.owneridyominame 	
		, dense_RANK() Over(partition by res.incidentid Order by res.createdon desc) as Latest	
from dbo.FilteredIncidentResolution res
where res.createdon between '2013-01-01' and '2013-12-31'
)
select fi.*
from dbo.FilteredIncident fi
	left outer join IncidentsResolved ir
		on fi.incidentid = ir.incidentid and ir.Latest = 1
where fi.createdon between '2013-01-01' and '2013-12-31'
group by fi.createdbyname
		,fi.statecodename

    I was only interested in the last resolved information so this worked

    Thanks

    Rich


    • Edited by Rich Whight Wednesday, February 12, 2014 3:11 PM Clarity
    Wednesday, February 12, 2014 3:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Glad it helped
    Wednesday, February 12, 2014 4:27 PM