CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
GEMI
GEMI

Reputation: 2279

SELECT last row if USER is found

I have a log table in SQL Server. Table is structured this way:

Unique  ProblemID  ResponsibleID  AssignedToID  ProblemCode

155     155        0282                         4
156     155                       0900
157     155                                     3
158     155                       0147          1
159     159        0111                         2
160     159                       0333          4
161     159        0900                         1

So basically we log all problems and who was responsible/had to deal with the problem. I need a query that would find which problems one person was involved in:

For example:

  1. Person with ID 0900 was involved in both 155 and 159.
  2. Person with ID 0282 was involved in 155 only.
  3. Person with ID 0333 was involved in 159 only.

Also I forgot to mention that I need to filter the last row of ProblemID by the ProblemCode. For example find problemID where person is involved, but there the ProblemCode in the last log line of that problem is 1 (Which means the problem is now closed).

Furthermore, I was working with query:

    select ProblemID, EntryTime, ResponsibleID, ProblemCode, AssignedToID
    from (select ProblemID, EntryTime, ResponsibleID, ProblemCode,  AssignedToID,
    row_number() over(partition by ProblemID order by EntryTime desc) as rn
    from myTable) as T
    where rn = 1 and ResponsibleID = '00282' OR AssignedToID = '00282' 
    and veiksmoid <> 4

However, it ONLY matches the last rows.

Upvotes: 3

Views: 306

Answers (4)

phil
phil

Reputation: 707

This gives you all the problems that a person 0900 dealed with and that have problemCode=1 in their last line. I can't test it so there might be some errors.

SELECT problemID FROM <table> t1
WHERE problemID IN  (
      SELECT problemID FROM <table>
      WHERE (ResponsibleID = 0900 OR AssignedToID = 0900))
AND problemCode = 1
AND unique = (SELECT MAX(unique) FROM <table> WHERE problemID = t1.problemID)

Upvotes: 1

DRapp
DRapp

Reputation: 48159

SELECT distinct problemid
   from YourTable
   where 
         ( Responsible = 0900
      OR AssignedToID = 0900 )
      AND ProblemCode <> 1

Upvotes: 0

sll
sll

Reputation: 62544

SELECT ag.UserId, ag.ProblemID
FROM(
   SELECT ProblemID, ResponsibleID as UserId
   FROM Table 
   WHERE ResponsibleID IS NOT NULL

   UNION ALL

   SELECT ProblemID, AssignedToID  as UserId
   FROM Table 
   WHERE AssignedToID IS NOT NULL
) ag
GROUP BY ag.UserId, ag.ProblemID

Upvotes: 1

Paul Filmer
Paul Filmer

Reputation: 123

Perhaps this query could help:

SELECT ProblemID
FROM LOGTABLE
WHERE (ResponsibleID = x) OR (AssignedToID = x)

where LOGTABLE is the name of the table, and x is the Person's ID.

Upvotes: 0

Related Questions