November 4, 2011 at 1:22 pm
How can I improve this query?
Any help would be greatly appreciated.
SELECT Comp.CompanyName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,
MIN(Comp.PhoneNumber) AS Phone, MIN(CoTyp.AgencyTypeDescription) AS Affinity,
MIN(MktTer.MarketingTerritoryID) AS Territory, MIN(Co.CountyName) AS County,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 1 THEN 1
ELSE 0
END AS PriorYear,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentYear,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentMonth,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousMonth,
CASE Datediff(week, SaleTransferredTSTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 2 THEN 1
ELSE 0
END AS TwoWeeksPrior,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 3 THEN 1
ELSE 0
END AS ThreeWeeksPrior
INTO #TXActivityTemp
FROM Quote AS Quote WITH (NOLOCK)
INNER JOIN Company AS Comp WITH (NOLOCK) ON Quote.CompanyID = Comp.CompanyID
FULL OUTER JOIN dbo.CompanyType AS CoTyp WITH (NOLOCK) ON Comp.CoTypeID = CoTyp.CoTypeID
FULL OUTER JOINCounty AS Co WITH (NOLOCK) ON Quote.CountyID = Co.CountyID
FULL OUTER JOIN COMarketingTerritory AS MktTer ON Quote.CompanyID = MktTer.COmpanyyID
WHERE (Quote.SaleTransferredTS >= '2010-01-01')
GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID,SaleTransferredTS
HAVING LEFT(Comp.CompanyNumber, 6) = '240002')
SELECT CompanyName, [Account Number], MIN(Phone) AS Phone,
MIN( Affinity) AS Affinity,MIN(Territory) AS Territory,
MIN(County) AS County,SUM(PriorYear)AS PriorYear, SUM(CurrentYear)AS CurrentYear,
SUM(CurrentMonth) AS CurrentMonth, SUM(PreviousMonth) AS PreviousMonth,SUM(CurrentWeek) AS CurrentWeek,
SUM(TwoWeeksPrior) AS TwoWeeksPrior, SUM(ThreeWeeksPrior) AS ThreeWeeksPrior
FROM #TXActivityTemp
GROUP BY [Account Number],CompanyName
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 4, 2011 at 1:29 pm
what do you believe to be the problem.?.....assume there is one hence your post?
have you any set up scripts and sample data?
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 4, 2011 at 1:36 pm
I'd love to see the actual plan.
From the first look this screams divide and conquer.
November 4, 2011 at 1:48 pm
J Livingston SQL (11/4/2011)
what do you believe to be the problem.?.....assume there is one hence your post?have you any set up scripts and sample data?
regards
I can understand why you want Setup Scripts Sample Data but I have a little problem in that there is propritary information.
I can't hit against the development environment because the data has not been refreshed in over a year.
I can't create a Stored Procedure, which is what I wanted to do so that I can use this in an SSIS package.
It is basically the last Sheet of many that I needed to populate.
Currently I have an OLEDB Data Source Where I insert into the temp table.
Then I created an OLEDB Command but I don't think that I'm doing the right thing.
I have a Data Conversion Task to convert the Columns to Unicode before the load into the OLEDB Excel Destination Object.
Regards.
Set RetainSameConnection=TRUE on the connection manager and set DelayValidation=TRUE
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 4, 2011 at 2:11 pm
I squeeze in some of the columns and abbreviated the column names to make this readable but this is what the result looks like from the output of the Temp Table.
I filtered on one record to make sure that the result matched up with a report that is currently being generated by an Excel macro.
CoName Account County priorYr CurYr CurMo PrevMo CurWk 2WksPR 3WkPr
Mom & Pops LLC 949992 Cambria 14 15 0 0 0 0 3
I have posted a question on how to do this in the SSIS forum:
http://www.sqlservercentral.com/Forums/Topic1200903-148-1.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 4, 2011 at 2:40 pm
Hi Welsh
sorry, probably being stupid (as usual) ,,,but is your issue the TSQL or SSIS populating Excel ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 4, 2011 at 2:46 pm
Good question.
I'm just trying to find a way to get it done.
It seems that I have two options, create an Inline View or CTE to get it done in T-SQL or figure out how to do this in SSIS using the Temp Table.
Create an SP on the Production Server is not an option.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 5, 2011 at 7:17 pm
Yes, there is a great way to improve to a certain extent.
Please read up on a section known as advance queries using common table expressions.
I personally have benefited quite a bit from.
There is no need for a temp table, nor, annoying aggregations mandated addition to the end of your query(s). You know what I am referring to!!
The ones that when you want to add additional fields then you are forced to add those columns to the GROUP BY part .. of which there is no need for worrying about that.
CTE it is for short.. common table expressions..
very useful
good luck.. and let me know what you think.
URL
http://msdn.microsoft.com/en-us/library/ms190766.aspx
Cheers,
John Esraelo
November 6, 2011 at 10:54 am
Based on your scenario and insufficient data given, I may not have the accurate solution for you but I have something herein that might give you and idea how to handle your situation.
I hear date-buckets, counters, sums, etc..
My sample could / stored procedure that uses the AdventureWorks can show you how to create those buckets, sums and counts.
here is the script and the sample output. (see attached JPG)
hope it helps.
-- ------------------------------------------------------------
USE [AdventureWorks]
GO
/****** Object: StoredProcedure [dbo].[USP_CalculateSalesAmounts] Script Date: 11/06/2011 09:39:33 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_CalculateSalesAmounts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_CalculateSalesAmounts]
GO
USE [AdventureWorks]
GO
/****** Object: StoredProcedure [dbo].[USP_CalculateSalesAmounts] Script Date: 11/06/2011 09:39:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,John Esraelo>
-- Create date: <Create Date,,201111052301>
-- Description:<Description,,USAGE: THIS STORED PROCEDURE READ THE SALES VALUES FROM ADVENTUREWORKS DB, TABLE SALESORDERHEADER >
-- THE PURPOSE IS TO SHOW HOW A COMMON TABLE EXPRESSION WORKS ALONG WITH THE PARTITIONING ANG AGGRAGATING. GOOD LUCK
-- =============================================
CREATE PROCEDURE [dbo].[USP_CalculateSalesAmounts]
AS
BEGIN
SET NOCOUNT ON;
;with MyQuery as
(
SELECT distinct
[SalesOrderID]
, convert(nvarchar(20), [OrderDate], 101) OrderDate
, year([OrderDate]) TheYear
, MONTH([OrderDate]) TheMonth
, DATEPART(week, [OrderDate]) TheWeek
, [Status]
, [SubTotal]
, [TaxAmt]
, [Freight]
, [TotalDue]
FROM [AdventureWorks].[Sales].[SalesOrderHeader]
where YEAR(OrderDate) = 2004 -- JUST GETTING ONE YEAR FOR SIMPLIFICATION
)
select distinct
TheYear
, TheMonth
, TheWeek
-- ------------------------------------------
, sum([SubTotal]) over (partition by TheYear, TheMonth, TheWeek) SubT_YrMonWk
, sum([TaxAmt]) over (partition by TheYear, TheMonth, TheWeek) Tax_YrMonWk
, COUNT([SalesOrderID]) over (partition by TheYear, TheMonth, TheWeek) Ord_YrMonWk
-- ------------------------------------------
, sum([SubTotal]) over (partition by TheYear, TheMonth) SubT_YrMon
, sum([TaxAmt]) over (partition by TheYear, TheMonth) Tax_YrMon
, COUNT([SalesOrderID]) over (partition by TheYear, TheMonth) Ord_YrMon
-- ------------------------------------------
, sum([SubTotal]) over (partition by TheYear) SubT_Yr
, sum([TaxAmt]) over (partition by TheYear) Tax_Yr
, COUNT([SalesOrderID]) over (partition by TheYear) Ord_Yr
from
MyQuery
Order by
TheYear, TheMonth, TheWeek
END
GO
-- ================================================
Cheers,
John Esraelo
November 6, 2011 at 12:39 pm
John Esraelo-498130 (11/6/2011)
Based on your scenario and insufficient data given, I may not have the accurate solution for you but I have something herein that might give you and idea how to handle your situation.
I don't particularly care when people post something but the do not provide scripts to create the objects, populate the tables and provide sample output.
If I did not have to keep everything confidential, rename all of the objects etc. it would relatively easy.
I'm going to revise what I have and go back to what I originally started with which was inline views.
Thanks for your responses.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 6, 2011 at 1:55 pm
What's wrong with a derived table?
SELECT CompanyName, [Account Number], MIN(Phone) AS Phone,
MIN( Affinity) AS Affinity,MIN(Territory) AS Territory,
MIN(County) AS County,SUM(PriorYear)AS PriorYear, SUM(CurrentYear)AS CurrentYear,
SUM(CurrentMonth) AS CurrentMonth, SUM(PreviousMonth) AS PreviousMonth,SUM(CurrentWeek) AS CurrentWeek,
SUM(TwoWeeksPrior) AS TwoWeeksPrior, SUM(ThreeWeeksPrior) AS ThreeWeeksPrior
FROM (
SELECT Comp.CompanyName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,
MIN(Comp.PhoneNumber) AS Phone, MIN(CoTyp.AgencyTypeDescription) AS Affinity,
MIN(MktTer.MarketingTerritoryID) AS Territory, MIN(Co.CountyName) AS County,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 1 THEN 1
ELSE 0
END AS PriorYear,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentYear,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentMonth,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousMonth,
CASE Datediff(week, SaleTransferredTSTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 2 THEN 1
ELSE 0
END AS TwoWeeksPrior,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 3 THEN 1
ELSE 0
END AS ThreeWeeksPrior
--INTO #TXActivityTemp
FROM Quote AS Quote WITH (NOLOCK)
INNER JOIN Company AS Comp WITH (NOLOCK) ON Quote.CompanyID = Comp.CompanyID
FULL OUTER JOIN dbo.CompanyType AS CoTyp WITH (NOLOCK) ON Comp.CoTypeID = CoTyp.CoTypeID
FULL OUTER JOIN County AS Co WITH (NOLOCK) ON Quote.CountyID = Co.CountyID
FULL OUTER JOIN COMarketingTerritory AS MktTer ON Quote.CompanyID = MktTer.COmpanyyID
WHERE (Quote.SaleTransferredTS >= '2010-01-01')
GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID, SaleTransferredTS
HAVING LEFT(Comp.CompanyNumber, 6) = '240002')
) d
GROUP BY [Account Number],CompanyName
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 6, 2011 at 1:59 pm
Do you see any difference if you change this...
WHERE (Quote.SaleTransferredTS >= '2010-01-01')
GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID,SaleTransferredTS
HAVING LEFT(Comp.CompanyNumber, 6) = '240002')
to this...
WHERE (Quote.SaleTransferredTS >= '2010-01-01') AND LEFT(Comp.CompanyNumber, 6) = '240002')
GROUP BY Comp.CompanyName, Quote.QuoteID,SaleTransferredTS
...you will need to change the output expression too
SELECT Comp.CompanyName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,
to
SELECT Comp.CompanyName, '240002' AS [Account Number], Quote.QuoteID,
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 7, 2011 at 5:10 am
ChrisM@home (11/6/2011)
What's wrong with a derived table?
Chris,
I had tried that earlier but I made a silly error.
Thanks!:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 7, 2011 at 5:46 am
ChrisM@home (11/6/2011)
What's wrong with a derived table?
SELECT CompanyName, [Account Number], MIN(Phone) AS Phone,MIN( Affinity) AS Affinity,MIN(Territory) AS Territory,
MIN(County) AS County,SUM(PriorYear)AS PriorYear, SUM(CurrentYear)AS CurrentYear,
SUM(CurrentMonth) AS CurrentMonth, SUM(PreviousMonth) AS PreviousMonth,SUM(CurrentWeek) AS CurrentWeek,
SUM(TwoWeeksPrior) AS TwoWeeksPrior, SUM(ThreeWeeksPrior) AS ThreeWeeksPrior
FROM (
SELECT Comp.CompanyName, LEFT(Comp.CompanyNumber, 6) AS [Account Number], Quote.QuoteID,
MIN(Comp.PhoneNumber) AS Phone, MIN(CoTyp.AgencyTypeDescription) AS Affinity,
MIN(MktTer.MarketingTerritoryID) AS Territory, MIN(Co.CountyName) AS County,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 1 THEN 1
ELSE 0
END AS PriorYear,
CASE Datediff(yy, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentYear,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentMonth,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousMonth,
CASE Datediff(week, SaleTransferredTSTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 2 THEN 1
ELSE 0
END AS TwoWeeksPrior,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 3 THEN 1
ELSE 0
END AS ThreeWeeksPrior
--INTO #TXActivityTemp
FROM Quote AS Quote WITH (NOLOCK)
INNER JOIN Company AS Comp WITH (NOLOCK) ON Quote.CompanyID = Comp.CompanyID
FULL OUTER JOIN dbo.CompanyType AS CoTyp WITH (NOLOCK) ON Comp.CoTypeID = CoTyp.CoTypeID
FULL OUTER JOIN County AS Co WITH (NOLOCK) ON Quote.CountyID = Co.CountyID
FULL OUTER JOIN COMarketingTerritory AS MktTer ON Quote.CompanyID = MktTer.COmpanyyID
WHERE (Quote.SaleTransferredTS >= '2010-01-01')
GROUP BY LEFT(Comp.CompanyNumber, 6), Comp.CompanyName, Quote.QuoteID, SaleTransferredTS
HAVING LEFT(Comp.CompanyNumber, 6) = '240002')
) d
GROUP BY [Account Number],CompanyName
Chris,
I tried this but it does not work.
You can get the PriorYear, CurrentYear, etc from the Quote Table.
All of the other tables are neened to get all of the other columns.
Don't I need to perform a JOIN on all of the other tables before the SELECT of the Subquery (InLine View)?
Also there needs to be a JOIN.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 7, 2011 at 6:28 am
Changing the query in the first post on this thread from using a temp table to a derived table is this straightforward:
1.Change FROM #TXActivityTemp to FROM () d
2.Copy the query which creates the temp table and paste it between the brackets
3.Delete (or comment out) the line INTO #TXActivityTemp
That’s it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply