I want small Change in my Query

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 2005 Forums

Transact-SQL (2005)

I want small Change in my Query

Author

Topic

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-09-20 : 06:51:35

Hi All,
This is the Query.

SELECT
ltrim(rtrim(parsename(replace(replace(L.LOCATION_FULL_NAME, '.', ' '), '/', '.'), 1))) AS [Client Name],
ltrim(rtrim(parsename(replace(replace(L.LOCATION_FULL_NAME, '.', ' '), '/', '.'), 2))) AS Region,
I.SUBCATEGORY AS Clasification,
I.PRODUCT_TYPE AS Symptom,
case when I.CATEGORY IN ('MSIL/Dealer Data Correction', 'User Guidance') then I.CATEGORY
When I.CATEGORY IN ('Network Management','Network Issue') then 'Network Issue' else 'Others' end AS [Service Type],
P.COMPANY AS Company,
P.PROBLEM_STATUS AS [Calls Status],
P.NUMBER AS Incident,
I.INCIDENT_ID AS Intraction,
P.ASSIGNMENT AS Assignment,
DATEADD(hh, 5, DATEADD(mi, 30,p.open_TIME)) as open_time,
DATEADD(hh, 5, DATEADD(mi, 30,p.update_TIME)) as Update_time,
DateDiff(mi, p.Open_time, A.datestamp) AS [ResponseTime],
CONVERT(varchar, DATEADD(hh, 5, DATEADD(mi, 30, P.OPEN_TIME)),101) AS [Open Date],
CONVERT(CHAR(8), DATEADD(hh, 5, DATEADD(mi, 30, P.OPEN_TIME)),108) AS [Open Time],
CONVERT(varchar, DATEADD(hh, 5, DATEADD(mi, 30, P.CLOSE_TIME)),101) AS [Close Date],
CONVERT(CHAR(8), DATEADD(hh, 5, DATEADD(mi, 30, P.CLOSE_TIME)),108) AS [Close Time],
A.TYPE as Status, A.DATESTAMP
FROM ((((((((((PROBSUMMARYM1 AS P
INNER JOIN ACTIVITYM1 AS A ON P.NUMBER = A.NUMBER))
INNER JOIN SCRELATIONM1 AS S ON P.NUMBER = S.SOURCE))
INNER JOIN INCIDENTSM1 AS I ON I.INCIDENT_ID = S.DEPEND))
INNER JOIN CONTCTSM1 AS C ON I.CALLBACK_CONTACT = C.CONTACT_NAME))
Left Outer JOIN LOCM1 AS L ON C.LOCATION = L.LOCATION))
WHERE DATEPART(mm, p.open_time)=7 Order By P.NUMBER

The Result is
-------------

Client Name Region Clasification Symptom Service Type Company Calls Status Incident Intraction Assignment open_time Update_time ResponseTime Open Date Open Time Close Date Close Time Status DATESTAMP
NULL NULL DMS Enhancement/ New requirement Enhancement Request Others Maruti Pending Vendor IM10134 SD10317 MSIL_DMS_SD 21:41.0 17:48.0 20276 7/19/2010 13:21:41 NULL NULL Update 47:49.0
NULL NULL DMS Enhancement/ New requirement Enhancement Request Others Maruti Pending Vendor IM10134 SD10317 MSIL_DMS_SD 21:41.0 17:48.0 20274 7/19/2010 13:21:41 NULL NULL External Vendor Assignment 45:27.0
NULL NULL DMS Enhancement/ New requirement Enhancement Request Others Maruti Pending Vendor IM10134 SD10317 MSIL_DMS_SD 21:41.0 17:48.0 20274 7/19/2010 13:21:41 NULL NULL Status Change 45:27.0
NULL NULL DMS Enhancement/ New requirement Enhancement Request Others Maruti Pending Vendor IM10134 SD10317 MSIL_DMS_SD 21:41.0 17:48.0 0 7/19/2010 13:21:41 NULL NULL Open 51:43.0
NULL NULL failure job failed Others MPhasis Open IM10135 SD10323 Mphasis ITO 30:51.0 30:51.0 0 7/20/2010 14:30:51 NULL NULL Open 00:51.0
NULL NULL DMS Finance/ Tally Unable to generate debit/credit note under Finance. Others Maruti Resolved IM10136 SD10327 MSIL_DMS_DBA 58:04.0 55:18.0 18837 7/20/2010 14:58:04 NULL NULL Resolved 25:19.0
NULL NULL DMS Finance/ Tally Unable to generate debit/credit note under Finance. Others Maruti Resolved IM10136 SD10327 MSIL_DMS_DBA 58:04.0 55:18.0 0 7/20/2010 14:58:04 NULL NULL Open 28:04.0
i want maximum repsponse time record only.here coming duplicate records.Primary field is Incident[P.Number].How can get the records only

V.NAGARAJAN

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-20 : 07:30:16

Format your query and result. It'd give anyone a headache trying to help you out.
To get the row with max value of reponse time, Put in an additional filter to pull only those ones.

Eg:


Select *
from
table a
where reponsetime=(select max(reponse time) from table b where a.Incident=b.Incident)

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-09-20 : 07:40:46

Response time is calculated at runtime

quote:
Originally posted by sakets_2000

Format your query and result. It'd give anyone a headache trying to help you out.
To get the row with max value of reponse time, Put in an additional filter to pull only those ones.

Eg:

Select *
from
table a
where reponsetime=(select max(reponse time) from table b where a.Incident=b.Incident)

V.NAGARAJAN

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-20 : 07:50:51

Yeah, So just put in the calc there. I gave you what your query would look like, You can now fit in whatever changes you think there are to it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources