See First Occurrence Only - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

See First Occurrence Only

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2002-08-21 : 17:11:16

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
kirkeblm@bp.com
SQL 7.0, NT 4.0 (1381), CP 1252

Teroman
Posting Yak Master

115 Posts

Posted - 2002-08-22 : 12:12:27

argh, my eyes!

i think you need to do a little sub query on this date table, is it the RCT one?

instead of

ReqChargeTime RCT ON R.Request_ID = RCT.Request_ID LEFT JOIN

try

(select Request_ID, min(TaskDate) as TaskDate
from ReqChargeTime
group by Request_ID) RCT ON R.Request_ID = RCT.Request_ID LEFT JOIN

you will also need to select any other columns you need to select and group by them, if there are any

col

here comes the PS

also, all those left joins worry me, do you really need to do outer joins all the time? maybe you do.

*squints*

i cant see from here.

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2002-08-22 : 14:19:53

I bow down to you! It worked! Thank you so much!

Lisa Kirkeby
BP
kirkeblm@bp.com
SQL 7.0, NT 4.0 (1381), CP 1252

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources