January 24, 2012 at 12:02 pm
Normally I do too, but they were in the original example. I was more interested in people's thoughts on using CROSS APPLY instead of a CTE.
January 24, 2012 at 2:46 pm
zuber.patel (1/24/2012)
Hi Rookie,The Article is really helpful, just want to know how we can display data in horizontal form like
Jan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11
UserYTD6253339505564161274281
Two examples I just made. One using PIVOT and the other using conditional sum:
IF OBJECT_ID('tempdb.dbo.#TotalSales', 'U') IS NOT NULL
DROP TABLE dbo.#TotalSales;
GO
CREATE TABLE dbo.#TotalSales
(
SaleDate DATETIME PRIMARY KEY,
SaleProfit DECIMAL(19,2)
);
GO
INSERT INTO dbo.#TotalSales(SaleDate, SaleProfit)
VALUES ('20110105', 34000), ('20110820', 50045.60), ('20111003', 13650.32);
GO
/* =======================================
Example 1: USING PIVOT
---------------------------------------
*/
WITH PivotTable AS
(
SELECT (SELECT TOP(1) YEAR(#TotalSales.SaleDate)
FROM dbo.#TotalSales
WHERE #TotalSales.SaleDate IS NOT NULL) AS Year,
Months.M,
ISNULL(#TotalSales.SaleProfit, 0) AS SalesProfit
FROM (SELECT 'Jan' AS M UNION ALL SELECT 'Fev' UNION ALL SELECT 'Mar' UNION ALL SELECT
'Apr' UNION ALL SELECT 'May' UNION ALL SELECT 'Jun' UNION ALL SELECT
'Jul' UNION ALL SELECT 'Aug' UNION ALL SELECT 'Sep' UNION ALL SELECT
'Oct' UNION ALL SELECT 'Nov' UNION ALL SELECT 'Dez') AS Months
LEFT JOIN dbo.#TotalSales ON Months.M = LEFT(DATENAME(MONTH, #TotalSales.SaleDate), 3)
)
SELECT Year, Jan, Fev, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dez
FROM (SELECT Year, M, SalesProfit
FROM PivotTable) AS P
PIVOT(SUM(SalesProfit) FOR M IN (Jan, Fev, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dez)) AS X;
/* =======================================
Example 2: NOT USING PIVOT
---------------------------------------
*/
WITH NonPivotTable AS
(
SELECT (SELECT TOP(1) YEAR(#TotalSales.SaleDate)
FROM dbo.#TotalSales
WHERE #TotalSales.SaleDate IS NOT NULL) AS Year,
Months.M,
ISNULL(#TotalSales.SaleProfit, 0) AS SalesProfit
FROM (SELECT 'Jan' AS M UNION ALL SELECT 'Fev' UNION ALL SELECT 'Mar' UNION ALL SELECT
'Apr' UNION ALL SELECT 'May' UNION ALL SELECT 'Jun' UNION ALL SELECT
'Jun' UNION ALL SELECT 'Aug' UNION ALL SELECT 'Sep' UNION ALL SELECT
'Oct' UNION ALL SELECT 'Nov' UNION ALL SELECT 'Dez') AS Months
LEFT JOIN dbo.#TotalSales ON Months.M = LEFT(DATENAME(MONTH, #TotalSales.SaleDate), 3)
)
SELECT Year AS Year,
SUM(CASE M WHEN 'Jan' THEN SalesProfit ELSE 0 END) AS Jan,
SUM(CASE M WHEN 'Fev' THEN SalesProfit ELSE 0 END) AS Fev,
SUM(CASE M WHEN 'Mar' THEN SalesProfit ELSE 0 END) AS Mar,
SUM(CASE M WHEN 'Apr' THEN SalesProfit ELSE 0 END) AS Apr,
SUM(CASE M WHEN 'May' THEN SalesProfit ELSE 0 END) AS May,
SUM(CASE M WHEN 'Jun' THEN SalesProfit ELSE 0 END) AS Jun,
SUM(CASE M WHEN 'Jul' THEN SalesProfit ELSE 0 END) AS Jul,
SUM(CASE M WHEN 'Aug' THEN SalesProfit ELSE 0 END) AS Aug,
SUM(CASE M WHEN 'Sep' THEN SalesProfit ELSE 0 END) AS Sep,
SUM(CASE M WHEN 'Oct' THEN SalesProfit ELSE 0 END) AS Oct,
SUM(CASE M WHEN 'Nov' THEN SalesProfit ELSE 0 END) AS Nov,
SUM(CASE M WHEN 'Dez' THEN SalesProfit ELSE 0 END) AS Dez
FROM NonPivotTable
GROUP BY Year;
I'm not sure how it would deal with a huge table since I'm joining my table with the tally table using "Months.M = LEFT(DATENAME(MONTH, #TotalSales.SaleDate), 3)". And I'm pretty sure that that subquery to get the year will not be great for performance since the execution plan show it takes 21% of it.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 24, 2012 at 2:52 pm
And a more simplified version of Example 2 below: :blush:
SELECT YEAR(SaleDate) AS Year,
SUM(CASE MONTH(SaleDate) WHEN 1 THEN SaleProfit ELSE 0 END) AS Jan,
SUM(CASE MONTH(SaleDate) WHEN 2 THEN SaleProfit ELSE 0 END) AS Fev,
SUM(CASE MONTH(SaleDate) WHEN 3 THEN SaleProfit ELSE 0 END) AS Mar,
SUM(CASE MONTH(SaleDate) WHEN 4 THEN SaleProfit ELSE 0 END) AS Apr,
SUM(CASE MONTH(SaleDate) WHEN 5 THEN SaleProfit ELSE 0 END) AS May,
SUM(CASE MONTH(SaleDate) WHEN 6 THEN SaleProfit ELSE 0 END) AS Jun,
SUM(CASE MONTH(SaleDate) WHEN 7 THEN SaleProfit ELSE 0 END) AS Jul,
SUM(CASE MONTH(SaleDate) WHEN 8 THEN SaleProfit ELSE 0 END) AS Aug,
SUM(CASE MONTH(SaleDate) WHEN 9 THEN SaleProfit ELSE 0 END) AS Sep,
SUM(CASE MONTH(SaleDate) WHEN 10 THEN SaleProfit ELSE 0 END) AS Oct,
SUM(CASE MONTH(SaleDate) WHEN 11 THEN SaleProfit ELSE 0 END) AS Nov,
SUM(CASE MONTH(SaleDate) WHEN 12 THEN SaleProfit ELSE 0 END) AS Dez
FROM dbo.#TotalSales
GROUP BY YEAR(SaleDate);
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 24, 2012 at 3:00 pm
codebyo (1/24/2012)
And a more simplified version of Example 2 below: :blush:
SELECT YEAR(SaleDate) AS Year,
SUM(CASE MONTH(SaleDate) WHEN 1 THEN SaleProfit ELSE 0 END) AS Jan,
SUM(CASE MONTH(SaleDate) WHEN 2 THEN SaleProfit ELSE 0 END) AS Fev,
SUM(CASE MONTH(SaleDate) WHEN 3 THEN SaleProfit ELSE 0 END) AS Mar,
SUM(CASE MONTH(SaleDate) WHEN 4 THEN SaleProfit ELSE 0 END) AS Apr,
SUM(CASE MONTH(SaleDate) WHEN 5 THEN SaleProfit ELSE 0 END) AS May,
SUM(CASE MONTH(SaleDate) WHEN 6 THEN SaleProfit ELSE 0 END) AS Jun,
SUM(CASE MONTH(SaleDate) WHEN 7 THEN SaleProfit ELSE 0 END) AS Jul,
SUM(CASE MONTH(SaleDate) WHEN 8 THEN SaleProfit ELSE 0 END) AS Aug,
SUM(CASE MONTH(SaleDate) WHEN 9 THEN SaleProfit ELSE 0 END) AS Sep,
SUM(CASE MONTH(SaleDate) WHEN 10 THEN SaleProfit ELSE 0 END) AS Oct,
SUM(CASE MONTH(SaleDate) WHEN 11 THEN SaleProfit ELSE 0 END) AS Nov,
SUM(CASE MONTH(SaleDate) WHEN 12 THEN SaleProfit ELSE 0 END) AS Dez
FROM dbo.#TotalSales
GROUP BY YEAR(SaleDate);
I was just going to replay with that as a better version 🙂
January 24, 2012 at 3:07 pm
tfendt (1/24/2012)
I was just going to replay with that as a better version 🙂
I deserve to be punched for that.
I was trying to destroy a mosquito with a nuclear weapon. 😀
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 24, 2012 at 5:21 pm
TheSQLGuru (1/24/2012)
2) cursors are still the fastest supported way to do running totals in sql server. Jeff Moden and others have come up with the "quirky update" to do these as well, although not officially supported by microsoft.
Hugo Kornelis' set-based iteration method from the first MVP Deep Dives book is generally much faster than a T-SQL cursor. Running totals using SQLCLR (which some people refer to incorrectly as a cursor) are a fraction behind that. In the very specific cases Quirky Update is aimed at (persisting the running total in the same table exploiting an existing clustered index), I have never seen anything beat it. I have always meant to test parallel-SQLCLR versus QU on a very large set, but there are many things I would like to test 'one day'.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 24, 2012 at 6:46 pm
SQL Kiwi (1/24/2012)
TheSQLGuru (1/24/2012)
2) cursors are still the fastest supported way to do running totals in sql server. Jeff Moden and others have come up with the "quirky update" to do these as well, although not officially supported by microsoft.Hugo Kornelis' set-based iteration method from the first MVP Deep Dives book is generally much faster than a T-SQL cursor. Running totals using SQLCLR (which some people refer to incorrectly as a cursor) are a fraction behind that. In the very specific cases Quirky Update is aimed at (persisting the running total in the same table exploiting an existing clustered index), I have never seen anything beat it. I have always meant to test parallel-SQLCLR versus QU on a very large set, but there are many things I would like to test 'one day'.
I agree to those things Paul, but those other methods are also not very well known in the real world either. pCLR would be neat to trot out, but with 2012 coming I am holding out hope of better things. I feel I will be disappointed on the first iteration or two however until they get some real optimizations worked out in the engine. I doubt things are as fast as they can be on rev 1.0. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2012 at 7:10 pm
TheSQLGuru (1/24/2012)
. . . I feel I will be disappointed on the first iteration or two however until they get some real optimizations worked out in the engine. I doubt things are as fast as they can be on rev 1.0. 🙂
2012 can handle 64 cores of the Haswell chip, which will be able to be overclocked to 4.2 GHz. Make your call on whether that is "as fast as [they] can be."
January 24, 2012 at 7:36 pm
TheSQLGuru (1/24/2012)
I agree to those things Paul, but those other methods are also not very well known in the real world either.
This is why I mention them so much 😉
pCLR would be neat to trot out, but with 2012 coming I am holding out hope of better things. I feel I will be disappointed on the first iteration or two however until they get some real optimizations worked out in the engine. I doubt things are as fast as they can be on rev 1.0. 🙂
It'll certainly be faster than triangular joins and T-SQL cursors, yes. It's a shame the built-ins aren't faster than (serial) CLR or set-iteration from the word go, but there we are. The addition of e.g. SUM (ORDER BY) is a big win for 2012 nevertheless.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 24, 2012 at 10:31 pm
In Australia our financial year runs from July to June so in our case YTD means start counting from 1st July ...
Select SaleDate, right(Cast(100+Month(SaleDate) as char(3)),2)+'/'+Cast(Year(SaleDate) as Char(4)) as Period,
(Year(SaleDate)-2000)*12+Month(SaleDate) as Months,
(Case When Month(SaleDate)>6 then Year(SaleDate) else Year(SaleDate)-1 end) as FinYear,
(Case When Month(GetDate())>6 then Year(GetDate()) else Year(GetDate())-1 end) as CurFinYear
from SalesDetails
Where FinYear=CurFinYear
In this case
SaleDate is the Date of Sale eg 04/10/2011
Period is a text field to display for month and year eg 10/2011 (but would be better with the month is words as per the original example)
Months is the number of months since 01/01/2000 eg 11*12+10 = 154 - used for sorting the periods convering motre than one calendar year
Finyear is the financial year to which the sale date belongs
CurFinyear is the current financial year based on today's date
So if FinYear=CurFinyear then it's the current financial year ( eg 04/10/2011 is part of the year from 01/07/2011 to 30/06/2012 or what I'm calling the 2011 financial year)
If FinYear=CurFinyear-1 then it's the preivous financial year (so I can compare last YTD with trhis YTD)
Actually I store the Finyear, Months and CurFinyear as calculated fields in my salesdetails table so I don't have to calculate them in every report I generate from these details.
January 24, 2012 at 10:31 pm
Thanks Mike... 🙂
Anjali Gautam
January 25, 2012 at 2:06 am
to TheSQLGuru
1) Solution architect must have knowledge about SSAS, therefore there must be no such task for MS SQL Server db developer
2) who says we need to query raw data? - transform it (OLTP -> DW -> DM -> OLAP cube) and query a cube. There is standard Year-to-date MDX functions.
3) functions prevent use of indexes. Indexes' usage is necessary to speed up query.
4) Again Solution architect must have knowledge about SSIS, and SSIS doesnt perform any output - it s ETL tool. It is "arrow" on the route of data (see issue #2). Use any MDX client (ADOMD.Net,Excel,etc.) at front end to output data instead.
5)
a)Yet Again Solution architect must have knowledge about SSRS
b)it s part of SQL Server anyway, and it s the task of 5-15 minutes to create dataset on T-SQL/MDX query, put tablix at report, and perform some simple formatting. And again there is ADO.Net/ADOMD.Net /Excel to use query results in client applications at least.
c) which year has 5M months? or just report with 5M rows? It s kind of another example of bad design. SSRS shouldn't receive more data rows than it must visualize - there is GROUP BY clause in T-SQL for this at least. I did a swarm of such "report optimization" when I had just to add GROUP BY clause to datasets' queries.
Briefly,
Each component of MS SQL Server must do the job it was designed for
T-SQL - OLTP
SSIS - data preparing
SSAS - reporting, aggregation and analytical processing
SSRS - data visualization.
Actually I do good money for things I tell you here 😉
January 25, 2012 at 1:40 pm
This is the type of problem that MDX handles quite well, if the user has an OLAP cube to query from. A date dimension and a fact table is all you need.
WITH [measures].[year to date sales] AS
aggregate(
periodsToDate( [date].[calendar].[calendarYear], [date].[calendar].currentMember),
[measures].[salesAmount]
)
Select {[measures].[salesAmount], [measures].[year to date sales]} on columns,
{[date].[calendar].[months].memebers} on rows
From user_cube
There is even a YTD function in analysis services that can perform this function. My point here is that as a long term solution, an olap cube is worth exploring since this is the type of report that is quite insightful to a business (and not particularly difficult to create) .
----------------------------------------------------
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply