quote:

---

Originally posted by RAVSKINS

I'm adding some functionality to an already existing SQL - the current SQL is the following:

SELECT
REQ.Job_Number,
REQ.Request_Title AS Task_Title,
CONVERT(char(10),REQ.Date_Created,120) AS Date_Created,
MAX(CONVERT(char(10),Action_Time,120)) AS 'Date Sent for Release'
FROM
Request REQ
INNER JOIN Response R ON R.Request_ID = REQ.Request_ID
INNER JOIN Response_Action_History RAH ON RAH.Response_ID = R.Response_ID
INNER JOIN PersonnelS P ON P.ID = RAH.User_ID
WHERE
(R.Is_Released = 1 AND R.Date_Released BETWEEN '10/1/2005' AND '09/30/2009'
AND RAH.Response_Action_ID = 1
AND P.Level_Type_ID = '5A950A08-E023-4238-84C3-C27E281A7EDF'
AND P.Level_No_ID = 'B1BF80F1-E9DA-4D89-AD0E-4C223E5919D3')
AND RAH.Sent_to_ID = '911FE4CA-BC39-11D4-87EA-00B0D0785B6D'
GROUP BY
REQ.Job_Number,
REQ.Date_Created,
REQ.Request_Title
ORDER BY 1

1) I need to filter by more than one ID from my RESPONSE_ACTION_HISTORY table in my WHERE clause (in the red). So "WHERE RAH.Sent_To_ID IN ('ID', 'ID')." However, I'd rather filter it by Level_No_ID and Level_Type_ID from the PERSONNELS table, so that I don't have to update the query with additional ID's down the road, as new ones are added, or old ones replaced, but I'm already hitting those earlier in why WHERE clause. How would I search my RESPONSE_ACTION_HISTORY table for the Level_No_ID and Level_Type_ID of the sender, and well as the same thing for the Recipient?

2) How do I make a new column of returned values in my SELECT clause, for the first instance of an event. For instance, there will be multiple times that person A sends something to person B, but how do I only capture the first time that it occurs?

Not sure if I'm making total sense, but I'll be happy to try to clarify if possible. I appreciate the help!

---

quote:

---

Originally posted by webfred

I really would like to help you but I can't understand your needs.
You should give us:
CREATE statements of
Request
Response
Response_Action_History
PersonnelS

INSERT statements with sample data for each table

Wanted result in relation to the sample data.

I'm sure then someone here can give you a solution.

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---