August 22, 2002 at 8:16 am
I have the following stored procedure which works fine but it's pulling every occurrence of the TaskDate condition and I only want to see the first occurrence for each Request_ID. I just can't figure out how to do it. I tried using MIN but it didn't work. Any help would be appreciated. Thank you!
SELECT DISTINCT R.Request_ID AS 'Request ID',
R.Request_Type AS 'Request Type',
(CASE R.Request_Type WHEN 'S' THEN ReqScripting.Product_Name + ' ' + ReqScripting.Product_Version
WHEN 'T' THEN ReqTesting.Product_Name + ' ' + ReqTesting.Product_Version
WHEN 'W' THEN ReqWTS.Product_Name + ' ' + ReqWTS.Product_Version
WHEN 'O' THEN ReqOther.Project_Description
WHEN 'Q' THEN ReqQA.Application_Name END) AS 'Description',
R.Request_Date AS 'Request Date',
(CASE WHEN RCT.TaskDescription LIKE 'Email sent to Expert Tester (%' THEN RCT.TaskDate ELSE NULL END)
AS 'Task Date',
DATEDIFF(day, R.Request_Date, (CASE WHEN RCT.TaskDescription LIKE 'Email sent to Expert Tester (%'
THEN RCT.TaskDate ELSE NULL END)) AS 'Elapsed Days',
RSD.Scripter_Name AS 'Scripter'
FROM ReqCommon R LEFT JOIN
ReqChargeTime RCT ON R.Request_ID = RCT.Request_ID LEFT JOIN
ReqScripting ON R.Request_ID = ReqScripting.Request_ID LEFT JOIN
ReqTesting ON R.Request_ID = ReqTesting.Request_ID LEFT JOIN
ReqOther ON R.Request_ID = ReqOther.Request_ID LEFT JOIN
ReqWTS ON R.Request_ID = ReqWTS.Request_ID LEFT JOIN
ReqQA ON R.Request_ID = ReqQA.Request_ID LEFT JOIN
ReqScripterDef RSD ON R.Scripter_ID = RSD.Scripter_ID
WHERE RCT.TaskDescription LIKE 'Email sent to Expert Tester (%'
GROUP BY R.Request_ID, R.Request_Type, ReqScripting.Product_Name, ReqScripting.Product_Version, ReqTesting.Product_Name,
ReqTesting.Product_Version, ReqWTS.Product_Name, ReqWTS.Product_Version, ReqOther.Project_Description, ReqQA.Application_Name,
RCT.TaskDescription, RCT.TaskDate, RSD.Scripter_Name, R.Request_Date
Lisa Kirkeby
BP
SQL 7.00.961, NT 4.0(1381)
Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.00.961, NT 4.0(1381)
August 22, 2002 at 8:45 am
Here is something that might help. This example select one order per store. The order selected happens to be the newest/most recient order. Possibly can can use something like this to get the first occurance of each request_id:
use pubs
go
select a.ord_date, a.qty, a.ord_num, a.stor_id
from sales a
where a.ord_num = (select top 1 ord_num from sales b
where a.stor_id=b.stor_id
order by b.ord_date desc)
order by a.stor_id desc
-------------------------
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply