CODEWITHSUNDEEP
sqlsql-serversql-server-2008
GEMI
GEMI

Reputation: 2279

SQL Server: SELECT from multiple lines if values exist

I am trying to write a query that would select data from multiple lines depending if there is a newer value. This has been giving me headaches for a long time already. The table is structured this way:

UniqID ProblemID  DateTime   Status PersonID ResponsibleID ActionID Comment  Deadline    PartID
15589   15589    01/16 12:11   0       25         48          12      bla1   01/16 12:11  T9865
15592   15589    01/16 12:25   1       48         48          105     bla2   null         null
15601   15589    01/16 13:08   3       56         null        195     bla3   null         N2654
15641   15589    01/17 18:02   3       11         23          35      null   01/18 15:00  null
15705   15589    01/18 10:24   5       23         null        255     bla4   null         null

This is a mistake log and I need to perform several slightly different queries on this data.

This query would have to return 

    ProblemID (first)DateTime (last)Status (first)Person (last)Responsible (last)action (first)Comment (last)deadline (last)PartID        
      15589      01/16 12:11       5            25            23                255         bla1        01/18 15:00      N2654

I have made several attempts at this query, but the best that came out was a query that selects all stuff from the last line or the first one. I am actually struggling to figure out how to select the (last)ResponsibleID if it is not contained in the last line. The same for other columns obviously. The only solution I can think of right now is a simple select that would return me all the rows and just filter that necessary data in a foreach loop with PHP. Because selecting different values in multiple lines is just beyond my skill :)

Thanks in advance

Upvotes: 2

Views: 2755

Answers (3)

Callie J
Callie J

Reputation: 31296

TBH this probably isn't a problem for SQL to solve, but a problem for the business logic code. So whilst you will undoubtedly come up with a query that will do what you need, it may be simpler to just return all records to the client application, and have the client code deal with the problem of determining the pertinent records to display.

Upvotes: 1

gngolakia
gngolakia

Reputation: 2206

Following code might give you solution

SELECT  ABC.PROBLEMID,
        ABC.DATETIME,--(first)DateTime 
        ABC.STATUS,--(last)Status
        ABC.PERSON,-- (first)Person
        ABC.RESPONSIBLE,-- (last)Responsible
        ABC.ACTION,-- (last)action
        ABC.COMMENT,-- (first)Comment
        ABC.DEADLINE,-- (last)deadline
        ABC.PARTID -- (last)PartID
(
SELECT TOP 1 PROBLEMID,DATETIME,PERSON,COMMENT 
FROM YOURTABLE
ORDER BY DATETIME ASC
OR
SELECT TOP 1 STATUS,RESPONSIBLE,ACTION,DEADLINE,PARTID
FROM YOURTABLE
ORDER BY DATETIME DESC
) ABC
WHERE ABC.PROBLEMID IN (SELECT  DISTINCT ProblemID
                        FROM    Mistakes 
                        WHERE   PersonID = 48 
                           OR   ResponsibleID = 48)

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

The only solution I can think of is to repeat the conditions in subselects. I see no other way to get to the first/last column values that are NOT NULL.

SQL Statement

;WITH CurrentProblemID AS (
  SELECT  DISTINCT ProblemID
  FROM    Mistakes 
  WHERE   PersonID = 48 
          OR ResponsibleID = 48
)

SELECT  [ProblemID] = cpi.ProblemID
        , [(first) DateTime] = (SELECT MIN(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND DateTime IS NOT NULL)
        , [(last) Status] = (SELECT Status FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND Status IS NOT NULL))
        , [(first) Person] = (SELECT PersonID FROM Mistakes WHERE DateTime = (SELECT MIN(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND PersonID IS NOT NULL))
        , [(last) Responsible] = (SELECT ResponsibleID FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND ResponsibleID IS NOT NULL))
        , [(last) Action] = (SELECT ActionID FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND ActionID IS NOT NULL))
        , [(first) Comment] = (SELECT Comment FROM Mistakes WHERE DateTime = (SELECT MIN(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND Comment IS NOT NULL))
        , [(last) Deadline] = (SELECT Deadline FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND Deadline IS NOT NULL))
        , [(last) PartID] = (SELECT PartID FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND PartID IS NOT NULL))
FROM    CurrentProblemID cpi

Test script

;WITH Mistakes AS (
  SELECT  *
  FROM    ( 
            VALUES
              (15589, 15589, '01/16 12:11', 0, 25, 48, 12, 'bla1', '01/16 12:11', 'T9865')
              , (15592, 15589, '01/16 12:25', 1, 48, 48, 105, 'bla2', null, null)
              , (15601, 15589, '01/16 13:08', 3, 56, null, 195, 'bla3', null, 'N2654')
              , (15641, 15589, '01/17 18:02', 3, 11, 23, 35, null, '01/18 15:00', null)
              , (15705, 15589, '01/18 10:24', 5, 23, null, 255, 'bla4', null, null)
          ) AS v (UniqID, ProblemID, DateTime, Status, PersonID, ResponsibleID, ActionID, Comment, Deadline, PartID)
)          
, CurrentProblemID AS (
  SELECT  DISTINCT ProblemID
  FROM    Mistakes 
  WHERE   PersonID = 48 
          OR ResponsibleID = 48
)

SELECT  [ProblemID] = cpi.ProblemID
        , [(first) DateTime] = (SELECT MIN(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND DateTime IS NOT NULL)
        , [(last) Status] = (SELECT Status FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND Status IS NOT NULL))
        , [(first) Person] = (SELECT PersonID FROM Mistakes WHERE DateTime = (SELECT MIN(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND PersonID IS NOT NULL))
        , [(last) Responsible] = (SELECT ResponsibleID FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND ResponsibleID IS NOT NULL))
        , [(last) Action] = (SELECT ActionID FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND ActionID IS NOT NULL))
        , [(first) Comment] = (SELECT Comment FROM Mistakes WHERE DateTime = (SELECT MIN(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND Comment IS NOT NULL))
        , [(last) Deadline] = (SELECT Deadline FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND Deadline IS NOT NULL))
        , [(last) PartID] = (SELECT PartID FROM Mistakes WHERE DateTime = (SELECT MAX(DateTime) FROM Mistakes WHERE ProblemID = cpi.ProblemID AND PartID IS NOT NULL))
FROM    CurrentProblemID cpi

Upvotes: 2

Related Questions