Can't isolate the records that I need 1

[LMGroup]

I'm using SQL Server 2005

I need to gather data from 3 tables, but 1 table has a 1:many relationship with the main table. From that table, I only want the first record for that case and stage. This is what I was trying (and failing):

SELECT Cases.Case_ReferenceID, Company.Comp_Name, Cases.Case_Description, Cases.Case_CreatedDate, c1.Case_Stage, c1.Case_Timestamp

FROM CaseProgress c1 
INNER JOIN (SELECT DISTINCT CaseProgress.Case_CaseID, CaseProgress.Case_Stage from CaseProgress) c2 ON c1.Case_CaseID = c2.Case_CaseID -- AND c1.Case_Stage = c2.Case_Stage
INNER JOIN Cases ON Cases.Case_CaseID=c1.Case_CaseID
INNER JOIN Company ON Cases.Case_PrimaryCompanyID=Company.Comp_CompanyID

WHERE c1.Case_Stage IN ('Responded', 'Solved')
AND Cases.Case_CreatedDate > '9/20/2013 12:00:00 AM'
ORDER BY Cases.Case_ReferenceID, c1.Case_Timestamp

Ultimately, I need to calculate the Responded & the Solved time (from the created date) and get an average for each by Company. Because each case can have multiple Responded and Solved stages, I need to get the first occurence of each per case. If I can't do it by using Distinct, maybe I could also create a new column to defines when each is the first for that case and then filter on it?

Any suggestions would be greatly appreciated.

 

[gmmastros]

try this:

SELECT	Cases.Case_ReferenceID, 
        Company.Comp_Name, 
        Cases.Case_Description, 
        Cases.Case_CreatedDate, 
        c1.Case_Stage, 
        c1.Case_Timestamp
FROM    (
          Select  Case_CaseId,
                  Case_Stage,
                  Case_TimeStamp,
                  Row_Number() Over (Partition By Case_CaseId Order By Case_TimeStamp) As RowId
          From    CaseProgress
        ) As c1
        INNER JOIN Cases 
           ON Cases.Case_CaseID=c1.Case_CaseID
           And c1.RowId = 1
        INNER JOIN Company 
           ON Cases.Case_PrimaryCompanyID=Company.Comp_CompanyID
WHERE   c1.Case_Stage IN ('Responded', 'Solved')
        AND Cases.Case_CreatedDate > '9/20/2013 12:00:00 AM'
ORDER BY Cases.Case_ReferenceID, c1.Case_Timestamp

If this works and you want me to explain it, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LMGroup]

It's definitely an improvement. Everything I was trying was doubling and tripling the number of records. This one went the extreme opposite. There are 299 records that have either Responded or Solved in the Stage field. Of those, I need about 200 of them (first occurrence of Responded and first occurrence of Solved for either case). Most of the time there is only one (or none) occurrence of each stage. Instead of getting 800 records (my code), I got 34 from the code you supplied. I'll have to figure out what you did and see if I can tweak it to get the correct record set. Thank you so much. So nice to have a decent starting point.

 

[LMGroup]

Thank you once again, gmmastros! Once I added the Case_Stage to the CaseID in the partition, I got exactly what I needed!