November 23, 2011 at 6:34 am
Dear SQL Experts,
I have a lot of code that uses CASE Statements.
I'm trying to wean myself off of them and use newer methods that have been introduced starting in SQL Server 2005.
Could someone please help me on how to perform the following using a PIVOT Statement as opposed to the CASE Statements used in the code listed below?
WITH CTE
AS
(
SELECT AgcyType.AgencyTypeDescription AS [Marketing Group],
MktRep.FirstName + ' ' + MktRep.LastName AS [Select RSM],
CONVERT(VARCHAR(10), Quote.SalesTransferredTS, 120) AS [Date Issued],
Quote.SalesNumber,
Comm.CommissionAmount AS Commission,
CASE FormName
WHEN 'HO-A' THEN 1
ELSE 0
END AS [HO-A],
CASE FormName
WHEN 'HO-BT' THEN 1
ELSE 0
END AS [HO-BT],
CASE FormName
WHEN 'HO-CON-B' THEN 1
ELSE 0
END AS [HO-CON-B],
CASE FormName
WHEN 'TDP-1' THEN 1
WHEN 'TDP-2' THEN 1
WHEN 'TDP-3' THEN 1
ELSE 0
END AS [TDP-1],
CASE FormName
WHEN 'HO-3' THEN 1
ELSE 0
END AS [HO-3]
FROMQuote AS Quote WITH (NOLOCK)
INNER JOINAgency AS Agcy WITH (NOLOCK) ON Quote.AgencyID = Agcy.AgencyID
INNER JOIN FormType AS FrmType ON Quote.FormTypeID = FrmType.FormTypeID
INNER JOINAgencyType AS AgcyType WITH (NOLOCK)ON Agcy.AgencyTypeID = AgcyType.AgencyTypeID
INNER JOINAgencyMarketingRep AS AgcyMktRep WITH (NOLOCK) ON Agcy.AgencyID = AgcyMktRep.AgencyID
INNER JOINMarketingRep AS MktRep ON AgcyMktRep.MarketingRepID = MktRep.MarketingRepID
INNER JOINCommission AS Comm ON Comm.QuoteID = Quote.QuoteID
WHEREQuote.SalesTransferredTS >= CAST('01 jan' + CAST(DATEPART(year, GETDATE()) - 1 AS varchar) AS DATETIME)
ANDComm.ArchiveFlag = 0
ANDComm.CommissionTypeID IN('GPP','Grand Total Commission')
ANDAgcy.AgencyID <> 1
ANDAgcy.AgencyID NOT IN (5693,5694)
ANDQuote.FirstName IS NOT NULL
ANDQuote.CreateTS >= CAST('01 jan' + CAST(DATEPART(year, GETDATE()) - 1 AS varchar) AS DATETIME)
)
SELECT[Marketing Group],[Select RSM],
[Date Issued],COUNT(SalesNumber) AS [No Of Sales],
SUM(Commission) AS [Written Commission],
ISNULL(SUM([HO-A]),0) AS [HO-A],
ISNULL(SUM([HO-BT]),0) AS [HO-BT],
ISNULL(SUM([HO-CON-B]),0) AS [HO-CON-B],
ISNULL(SUM([TDP-1]),0) AS [TDP-1],
ISNULL(SUM([HO-3]),0) AS [HO-3]
FROM CTE
GROUP BY [Marketing Group],[Select RSM],
[Date Issued]
ORDER BY [Date Issued] DESC
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 23, 2011 at 7:45 am
New does not necessarily mean better. (In fact with MS it often means buggy.) Jeff Moden shows in his article
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url] that the crosstab outperforms the pivot in most cases. He also mentions that it's much easier to do multiple aggregates with a crosstab than with a pivot.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 23, 2011 at 8:22 am
Thanks for the article.: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/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy