Sql help
Sql help
This may be too complex to ask in this forum as I've never posted here before; Anyway,
Wanted to see if anyone could assist me as I have very basic SQL writing experience.
So i have a table in our database that is basically a user_history table with system time stamps anytime the user edits, saves, etc.
the id would be Incident, Case, Part_Sequence
I want to write a sql that selects and Incident, Case, Part_Sequence when a particular user has at least one "log" or time stamp on that Incident
So let's say my scenario is this:
Incident Case User_ID Time_Stamp
12345 1 ABC123 2/1/2010 13:54
12345 1 ABC123 2/1/2010 13:59
12345 1 ABC123 2/1/2010 14:09
12345 1 ZYX321 2/1/2010 14:19
12345 1 ZYX321 2/1/2010 14:20
12345 1 ZYX321 2/1/2010 14:24
12345 1 ZYX321 2/1/2010 14:25
12345 1 ZYX321 2/1/2010 14:26
12345 1 ZYX321 2/1/2010 15:00
I would want the SQL to return only the following
12345 1 ABC123 2/1/2010 14:09
12345 1 ZYX321 2/1/2010 14:19
here's what I have so far
SELECT H. incident || '-' || H.case, h.user_id,
H. Time_Stamp
FROM incident_user_history H
WHERE h.incident_number || '-' || h.case_number IN (
SELECT iuh.incident || '-' || iuh.case
FROM incident_user_history iuh
WHERE h.incident || '-' || h.case =
iuh.incident || '-' || iuh.case
AND iuh.user_id = 'ABC123'
GROUP BY iuh.incident || '-' || iuh.case
HAVING COUNT (*) >= 1)
any help ?
|