Need a Complex SQL Query

Question

Hey,

I need to make a rather complex query, and I need help bad. Below is an example I made.

Basically, I need a query that will return one row for each case_id where the type is support, status start, and date meaning the very first one created (so that in the example below, only the 2/1/2009 John's case gets returned, not the 3/1/2009). The search needs to be dynamic to the point of being able to return all similar rows with different case_id's etc from a table with thousands of rows.

There's more after that but I don't know all the details yet, and I think I can figure it out if you guys (an gals) can help me out here. :)

ID   |  Case_ID   |   Name   |   Date   |  Status  |  Type   

48   |    450     |   John   | 6/1/2009 |  Fixed   | Support   
47   |    450     |   John   | 4/1/2009 |  Moved   | Support   
46   |    451     |   Sarah  | 3/1/2009 |          |           
45   |    432     |   John   | 3/1/2009 |  Fixed   | Critical  
44   |    450     |   John   | 3/1/2009 |  Start   | Support 
42   |    450     |   John   | 2/1/2009 |  Start   | Support   
41   |    440     |   Ben    | 2/1/2009 |          |           
40   |    432     |   John   | 1/1/2009 |  Start   | Critical  
...

Thanks a bunch!

Edit:

To answer some people's questions, I'm using SQL Server 2005. And the date is just plain date, not string.

Ok so now I got further in the problem. I ended up with Bliek's solution which worked like a charm. But now I ran into the problem that sometimes the status never starts, as it's solved immediately. I need to include this in as well. But only for a certain time period.

I imagine I'm going to have to check for the case table referenced by FK Case_ID here. So I'd need a way to check for each Case_ID created in the CaseTable within the past month, and then run a search for these in the same table and same manner as posted above, returning only the first result as before. How can I use the other table like that?

As usual I'll try to find the answer myself while waiting, thanks again!

Edit 2:

Seems this is the answer. I don't have access to the full DB yet so I can't fully test it, but it seems to be working with the dummy tables I created, to continue from Bliek's code's WHERE clause:

WHERE RowNumber = 1 AND Case_ID IN (SELECT Case_ID FROM CaseTable
      WHERE (Date BETWEEN '2007/11/1' AND '2007/11/30'))

The date's screwed again but you get the idea I'm sure. Thanks for the help everyone! I'll get back if there're more problems, but I think with this info I can improvise my way through most of the SQL problems I currently have to deal with. :)

Answer 1

Maybe something like:

select Case_ID, Name, MIN(date), Status, Type 
from table
where type = 'Support'
 and status = 'Start'
group by Case_ID, Name, Status, Type

EDIT: You haven't provided a lot of details about what you really want, so I'd suggest that you read all the answers and choose one that suits your problem best. So far I'd say that Tomalak's answer is closest to what you're looking for...

Answer 2

SELECT
  c.ID,
  c.Case_ID,
  c.Name,
  c.Date,
  c.Status,
  c.Type
FROM
  CaseTable c
WHERE
  c.Type = 'Support'
  AND c.Status = 'Start'
  AND c.Date = (
    SELECT MIN(Date) 
    FROM CaseTable
    WHERE Case_ID = c.Case_ID AND Type = c.Type AND Status = c.Status)
/* GROUP BY only needed when for a given Case_ID several rows 
   exist that fulfill the WHERE clause */
GROUP BY
  c.ID,
  c.Case_ID,
  c.Name,
  c.Date,
  c.Status,
  c.Type

This query benefits greatly from indexes on the Case_ID, Date, Status and Type columns.

Added value though the fact that the filter on Support and Status only needs to be set in one place.

As an alternative to the GROUP BY clause, you can do SELECT DISTINCT, which would increase readability (this may or may not affect overall performance, I suggest you measure both variants against each other). If you are sure that for no Case_ID in your table two rows exist that have the same Date, you won't need GROUP BY or SELECT DISTINCT at all.

Answer 3

Don't apologize for your date formatting, it makes more sense that way.

    SELECT ID, Case_ID, Name, MIN(Date), Status, Type
    FROM caseTable
    WHERE Type = 'Support'
       AND status = 'Start'
    GROUP BY ID, Case_ID, Name, Status, Type

Answer 4

In SQL Server 2005 and beyond I would use Common Table Expressions (CTE). This offers lots of possibilities like so:

With ResultTable (RowNumber
                 ,ID
                 ,Case_ID
                 ,Name
                 ,Date
                 ,Status
                 ,Type)
AS
(
    SELECT Row_Number() OVER (PARTITION BY Case_ID
                                  ORDER BY Date ASC)

          ,ID
          ,Case_ID
          ,Name
          ,Date
          ,Status
          ,Type
      FROM CaseTable
     WHERE Type   = 'Support'
       AND Status = 'Start'
)
    SELECT ID
          ,Case_ID
          ,Name
          ,Date
          ,Status
          ,Type
      FROM ResultTable
     WHERE RowNumber = 1

Answer 5

Deleted