Simplify given query - 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 2005 Forums

Transact-SQL (2005)

Simplify given query

Author

Topic

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-10-05 : 09:33:59

Hi,

select T.Company, T.Region, T.[Service Type], count(distinct T.Incident) AS IncidentCount
from(
SELECT P.COMPANY AS Company,
ltrim(rtrim(parsename(replace(replace(L.LOCATION_FULL_NAME, '.', ' '), '/', '.'), 2))) AS Region,
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.NUMBER AS Incident
FROM
(((INCIDENTSM1 AS I INNER JOIN (PROBSUMMARYM1 AS P INNER JOIN SCRELATIONM1 AS S ON P.NUMBER = S.SOURCE) 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)=DATEPART(mm, getdate())
) AS T group by T.Company, T.Region, T.[Service Type] order by T.Company, T.Region, T.[Service Type]

V.NAGARAJAN

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-05 : 10:30:04

Some formatting....


select
	T.Company
	, T.Region
	, T.[Service Type]
	, count(distinct T.Incident) AS IncidentCount
from
	(
	SELECT
		P.COMPANY AS Company
		, ltrim(rtrim(parsename(replace(replace(L.LOCATION_FULL_NAME, '.', ' '), '/', '.'), 2))) AS Region
		, 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.NUMBER AS Incident
	FROM
		(
			(
				(
				INCIDENTSM1 AS I
				INNER JOIN (
					PROBSUMMARYM1 AS P
					INNER JOIN SCRELATIONM1 AS S ON P.NUMBER = S.SOURCE
					)
					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) = DATEPART(mm, getdate())
	)
	AS T
group by
	T.Company
	, T.Region
	, T.[Service Type]
order by
	T.Company
	, T.Region
	, T.[Service Type]

Your FROM part is a horrible mess and I'm not sure what was intended there.

This part:


	WHERE
		DATEPART(mm, p.open_time) = DATEPART(mm, getdate())

Is a performance penalty waiting to happen. It can't use any index on p.open_time

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-05 : 10:33:12

The best way to rewrite that where is probably to have a calendar table (with a derived column for month number) and join to that, then use the month column to compare to DATEPART(mm, GETDATE())

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-10-05 : 10:51:59

Don't want outer query,with in sub query,how to implement it.

quote:

Originally posted by Transact Charlie

Some formatting....


select
	T.Company
	, T.Region
	, T.[Service Type]
	, count(distinct T.Incident) AS IncidentCount
from
	(
	SELECT
		P.COMPANY AS Company
		, ltrim(rtrim(parsename(replace(replace(L.LOCATION_FULL_NAME, '.', ' '), '/', '.'), 2))) AS Region
		, 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.NUMBER AS Incident
	FROM
		(
			(
				(
				INCIDENTSM1 AS I
				INNER JOIN (
					PROBSUMMARYM1 AS P
					INNER JOIN SCRELATIONM1 AS S ON P.NUMBER = S.SOURCE
					)
					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) = DATEPART(mm, getdate())
	)
	AS T
group by
	T.Company
	, T.Region
	, T.[Service Type]
order by
	T.Company
	, T.Region
	, T.[Service Type]

Your FROM part is a horrible mess and I'm not sure what was intended there.

This part:


	WHERE
		DATEPART(mm, p.open_time) = DATEPART(mm, getdate())

V.NAGARAJAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources