Better way to write subquery? - 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 2012 Forums

Transact-SQL (2012)

Better way to write subquery?

Author

Topic

NewSQL11
Starting Member

6 Posts

Posted - 2014-10-06 : 13:32:20

Looking to see if there is a more efficient way to write this sub query -


declare @STartDate datetime;
declare @SelectedCompany varchar(100);

set @StartDate = '7/1/2014';
set @SelectedCompany = 'MyCompany'

SELECT max([Month_Year__Date_]) AS MONTH,
       avg(CASE WHEN [Month_Year__Date_] between dateadd(month, -2, @StartDate)  and @StartDate  THEN [Phone___Time_To_Live_Agent___Post_IVR__Avg_] else null end) as TTLA, max(b.[PEER_TTLA]) PEER_TTLA
FROM [PhoneMetricsMonthly] a, (select avg(case when [Month_Year__Date_] between dateadd(month, -2, @StartDate)  and @StartDate  THEN [Phone___Time_To_Live_Agent___Post_IVR__Avg_] ELSE NULL END) AS PEER_TTLA
FROM [PhoneMetricsMonthly] a, [PeerMapping] b
WHERE a.[Month_Year__Date_] BETWEEN dateadd(MONTH, -3, dateadd(MONTH, -3,@StartDate)) AND dateadd(MONTH, -3,@StartDate)
  AND b.[subjectcompany] = @SelectedCompany
  AND a.[company] = b.[competitor]) b WHERE a.[Month_Year__Date_] BETWEEN dateadd(MONTH, -3, dateadd(MONTH, -3,@StartDate)) AND dateadd(MONTH, -3,@StartDate)
  AND [company] = @SelectedCompany

Basically what I am doing is passing a company and a start date. The sub query calculates PEER_TTLA from a subquery which is the average of all the companies competitors linked off a mapping file. I just don't know if this is the most efficient way to write/invoke this subquery to perform that calculation for the subquery. The average is a rolling 3 month average based off the Start Date. I am taking a max off the date to use as a dynamic header.

Thanks

NewSQL11
Starting Member

6 Posts

Posted - 2014-10-06 : 13:49:10

In case its not clear the sub query is -

(select avg(case when [Month_Year__Date_] between dateadd(month, -2, @StartDate)  and @StartDate  THEN [Phone___Time_To_Live_Agent___Post_IVR__Avg_] ELSE NULL END) AS PEER_TTLA
FROM [PhoneMetricsMonthly] a, [PeerMapping] b
WHERE a.[Month_Year__Date_] BETWEEN dateadd(MONTH, -3, dateadd(MONTH, -3,@StartDate)) AND dateadd(MONTH, -3,@StartDate)
  AND b.[subjectcompany] = @SelectedCompany
  AND a.[company] = b.[competitor]) b

Which I am using to get 1 value for the main SQL.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-07 : 07:15:42

First of all, your query will never give any result. Your WHERE filter is between -6 and -3 months back BUT your average aggregation is between -2 and 0 months back.

I believe this is something like this you are after.

SELECT		MAX(a.[Month_Year__Date_]) AS [Month],
		AVG(a.[Phone___Time_To_Live_Agent___Post_IVR__Avg_]) AS TTLA,
		AVG(CASE WHEN b.Competitor IS NULL THEN NULL ELSE a.[Phone___Time_To_Live_Agent___Post_IVR__Avg_]) AS PEER_TTLA
FROM		dbo.[PhoneMetricsMonthly] AS a
LEFT JOIN	dbo.[PeerMapping] AS b ON b.Competitor = a.Company
			AND b.[SubjectCompany] = @SelectedCompany
WHERE		a.[Month_Year__Date_] >= DATEADD(MONTH, -2, @StartDate)
		AND a.[Month_Year__Date_] < @StartDate;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

NewSQL11
Starting Member

6 Posts

Posted - 2014-10-09 : 11:20:52

Thanks ... I had to change it up a little for some other changes but this is what I came out with -


SELECT	max(a.[Month_Year__Date_]) AS [Month],		
		sum(CASE WHEN a.company <> @SelectedCompany THEN NULL ELSE ([Phone___Total_Time_To_Live_Agent__Avg_]*[Phone_____of_Successful_Interactions]) end) 	
		/sum(CASE WHEN a.company <> @SelectedCompany  THEN NULL ELSE [Phone_____of_Successful_Interactions] END) AS TTLA,		
		sum(CASE WHEN b.Competitor IS NULL THEN NULL ELSE ([Phone___Total_Time_To_Live_Agent__Avg_]*[Phone_____of_Successful_Interactions]) end) 	
		/sum(CASE WHEN b.Competitor IS NULL THEN NULL ELSE [Phone_____of_Successful_Interactions] END) AS PEER_TTLA	
FROM		[SeismicPhoneMetricsMonthly] AS a	
INNER JOIN [SeismicPhoneCustomMetricsMonthly] as C on a.company = c.company and a.[Month_Year__Date_] = c.[Month_Year__Date_]			
LEFT JOIN	[SeismicPeerMapping] AS b ON b.Competitor = a.Company		
			AND b.[SubjectCompany] = @SelectedCompany
WHERE		a.[Month_Year__Date_] >= DATEADD(MONTH, -2, @StartDate)	
		AND a.[Month_Year__Date_] <= @StartDate

Now say I wanted this to be dynamic so that i could calculate June rolling 3 months can I do that without having to use a UNION just to change the month where clause stuff?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources