SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Patrick Cahill / Returning 0's in a SUM (SQL Spackle) / Latest Posts

RE: Returning 0's in a SUM (SQL Spackle)

Gatekeeper — Tue, 14 Jun 2011 10:18:13 GMT

[quote][b]Jeff Moden (6/14/2011)[/b][hr]COALESCE can change the datatype of the result based on which non-null element is selected (which can cause a major performance problem, BTW). ISNULL will keep the datatype of the return the same as the datatype of the first operand[/quote]I've never run into that but it's good to learn something new! Thanks for the clarification.

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Tue, 14 Jun 2011 10:11:18 GMT

[quote][b]berzat.museski (6/14/2011)[/b][hr]Jeff, I must tell you that your solution is briliant as well![/quote]Thanks for the compliment but, nah... I just used a variation of what is in the article. :-) That was the whole point, really. The code in the article is some of the fastest code you can find to do this and you don't need a custom column (frequently disallowed on real tables) nor an extra index to support it.

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Tue, 14 Jun 2011 10:06:57 GMT

[quote][b]Gatekeeper (6/14/2011)[/b][hr][quote][b]Alexey Voronin (6/14/2011)[/b][hr]What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?You do not know ISNULL ?ISNULL(SUM(SalesTestData.SaleQty),0)[/quote]They are interchangeable. Both are the same thing except COALESCE() allows for more than one evaluation.[/quote]Actually, they're not interchangeable at the semantic level. COALESCE is slower than ISNULL (although it takes a substantial number of rows to tell). COALESCE is in the ANSI standard where ISNULL is proprietary. COALESCE can change the datatype of the result based on which non-null element is selected (which can cause a major performance problem, BTW). ISNULL will keep the datatype of the return the same as the datatype of the first operand. COALESCE takes more keystrokes to type than ISNULL (BIG selling point for me. :-P)Heh... other than that, they're interchangeable when two operands are necessary. :-D

RE: Returning 0's in a SUM (SQL Spackle)

Gatekeeper — Tue, 14 Jun 2011 07:29:18 GMT

[quote][b]Alexey Voronin (6/14/2011)[/b][hr]What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?You do not know ISNULL ?ISNULL(SUM(SalesTestData.SaleQty),0)[/quote]They are interchangeable. Both are the same thing except COALESCE() allows for more than one evaluation.

RE: Returning 0's in a SUM (SQL Spackle)

Alexey Voronin — Tue, 14 Jun 2011 03:26:44 GMT

What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?You do not know ISNULL ?ISNULL(SUM(SalesTestData.SaleQty),0)

RE: Returning 0's in a SUM (SQL Spackle)

berzat.museski — Tue, 14 Jun 2011 01:08:17 GMT

Jeff, I must tell you that your solution is briliant as well!

RE: Returning 0's in a SUM (SQL Spackle)

berzat.museski — Tue, 14 Jun 2011 01:07:50 GMT

Jeff, thank you for your effort measuring the two methods!It is true, my solution is a way slower as it is now. That is so because there is no index over the computed coumn in the example (how can it be SARGable without any indexes).After adding it, your query was only twice faster, which is ok I guess having the code optimized as it is :-)

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Mon, 13 Jun 2011 22:09:14 GMT

[quote][b]berzat.museski (6/13/2011)[/b][hr]No need for uncle Google in this case, spt_values is quite enough, unless you are querying some large data warehouse.However, I agree that GetNums is a cool trick to generate large order numbers.I was thinking of the following solution:I first add one computed column in the fact table (I assume that it is ok to do that) in order to keep the query SARGable. The new column will have the sales datetime rounded up to hour percision.ALTER TABLE #SalesTestDataADD SaleTimeHour AS CONVERT(DATETIME, CONVERT(VARCHAR,SaleTime,112)+' '+CAST(DATEPART(HOUR,SaleTime) AS VARCHAR(2))+':00:00.000',112) PERSISTEDThe report will then be formed by this single query:DECLARE @StartDate DATETIME, @EndDate DATETIMESET @StartDate='2011-01-01 00:00:00.000'SET @EndDate='2011-12-01 00:00:00.000'SELECT CAST(AllDates.ReportDayHour AS DATE) AS ReportDay, CAST(AllDates.ReportDayHour AS TIME) AS ReportHour, ISNULL(SUM(s.SaleQty),0) AS TotalSaleQtyFROM ( SELECT DATEADD(hh,h.number,DATEADD(dd,d.number,DATEADD(mm,m.number,@StartDate))) AS ReportDayHour FROM master..spt_values m CROSS JOIN master..spt_values d CROSS JOIN master..spt_values h WHERE m.type='p' AND m.number BETWEEN 0 AND DATEDIFF(mm,@StartDate,@EndDate) AND d.type='p' AND d.number BETWEEN 0 AND 30 AND h.type='p' AND h.number BETWEEN 0 AND 23) AS AllDatesLEFT JOIN #SalesTestData s ON s.SaleTimeHour=AllDates.ReportDayHourGROUP BY AllDates.ReportDayHourORDER BY AllDates.ReportDayHourThe parameters are the start and end moments of the reports, as any manager would want :)[/quote]Hi, Berzat,That's a very clever solution but, comparatively speaking, it's a bit slow compared to the methods offered in the article. After adding your computed column to the test table (as you did before), run the following code and see the difference in the time it takes...[code="sql"]--===== Identify the run SET STATISTICS TIME OFF; PRINT REPLICATE('=',80); PRINT 'Method similar to article'; PRINT REPLICATE('=',80); SET STATISTICS TIME ON;--===== Declare some obviously named variables. The ones with the @p -- prefix could be parameters in a stored procedure or functionDECLARE @pStartMonth DATETIME, @pEndMonth DATETIME;--===== Assign values to the input parameters SELECT @pStartMonth = '2011-01-15', @pEndMonth = 'Dec 2011';--===== "Normalize" the dates SELECT @pStartMonth = DATEADD(mm,DATEDIFF(mm,0,@pStartMonth),0), @pEndMonth = DATEADD(mm,DATEDIFF(mm,0,@pEndMonth)+1,0);--===== Create and populate the "hours" tableDECLARE @Hours TABLE (HourStart DATETIME, HourNext DATETIME); INSERT INTO @Hours (HourStart, HourNext) SELECT TOP (DATEDIFF(hh, @pStartMonth, @pEndMonth)) HourStart = DATEADD(hh,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@pStartMonth), HourNext = DATEADD(hh,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ,@pStartMonth) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2; SELECT ReportDay = CONVERT(CHAR(10),MAX(bin.HourStart),120), ReportHour = DATEPART(hh,MAX(bin.HourStart)), TotalSales = SUM(ISNULL(sale.SaleQty,0)) FROM @Hours bin LEFT OUTER JOIN #SalesTestData sale ON sale.SaleTime >= bin.HourStart AND sale.SaleTime < bin.HourNext GROUP BY bin.HourStart ORDER BY bin.HourStart;GO---------------------------------------------------------------------------------===== Identify the run SET STATISTICS TIME OFF; PRINT REPLICATE('=',80); PRINT 'Berzat''s method'; PRINT REPLICATE('=',80); SET STATISTICS TIME ON;DECLARE @StartDate DATETIME, @EndDate DATETIMESET @StartDate='2011-01-01 00:00:00.000'SET @EndDate='2011-12-01 00:00:00.000'SELECT CAST(AllDates.ReportDayHour AS DATE) AS ReportDay, CAST(AllDates.ReportDayHour AS TIME) AS ReportHour, ISNULL(SUM(s.SaleQty),0) AS TotalSaleQtyFROM (SELECT DATEADD(hh,h.number,DATEADD(dd,d.number,DATEADD(mm,m.number,@StartDate))) AS ReportDayHourFROM master..spt_values mCROSS JOIN master..spt_values dCROSS JOIN master..spt_values hWHERE m.type='p' AND m.number BETWEEN 0 AND DATEDIFF(mm,@StartDate,@EndDate)AND d.type='p' AND d.number BETWEEN 0 AND 30AND h.type='p' AND h.number BETWEEN 0 AND 23) AS AllDatesLEFT JOIN #SalesTestData s ON s.SaleTimeHour=AllDates.ReportDayHourGROUP BY AllDates.ReportDayHourORDER BY AllDates.ReportDayHour; SET STATISTICS TIME OFF;[/code]Here's the output on my ol' 9 year old, single CPU box. The method from the article is more than 12 times faster.[code="plain"]================================================================================Method similar to article================================================================================SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 94 ms, elapsed time = 106 ms.(8760 row(s) affected)(8760 row(s) affected)SQL Server Execution Times:[font="Arial Black"] CPU time = 390 ms, elapsed time = 731 ms.[/font]SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.================================================================================Berzat's method================================================================================SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Warning: Null value is eliminated by an aggregate or other SET operation.(8760 row(s) affected)SQL Server Execution Times:[font="Arial Black"] CPU time = 9328 ms, elapsed time = 12354 ms.[/font][/code]

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Mon, 13 Jun 2011 18:35:19 GMT

[quote][b]harry9katz (6/13/2011)[/b][hr]HiYou have to add hour to both tablesSELECT cast(floor(cast(SaleTime as float)) as datetime)Saledate,DATEPART(hour,SaleTime) saleshour ,sum([SaleQty]) sumsales into #salessum FROM #SalesTestData group by cast(floor(cast(SaleTime as float)) as datetime),DATEPART(hour,SaleTime) order by cast(floor(cast(SaleTime as float)) as datetime),DATEPART(hour,SaleTime)here U get the first table that the article started withU get salesum but not on every hourThen U add to the #Clendertest for every day all the hoursan now it is simpleSELECT dbo.CalendarTest.hourid, dbo.CalendarTest.StartDate, ISNULL(SUM(dbo.saleoneday.sumsales), 0) AS sumsalesFROM dbo.saleoneday RIGHT OUTER JOIN dbo.CalendarTest ON dbo.saleoneday.Saledate = dbo.CalendarTest.StartDate AND dbo.saleoneday.saleshour = dbo.CalendarTest.houridGROUP BY dbo.CalendarTest.hourid, dbo.CalendarTest.StartDateORDER BY dbo.CalendarTest.StartDate, dbo.CalendarTest.hourid[/quote]Is the dbo.CalendarTest table the same as the #CalendarTest table in the article? Also, what's the dbo.saleoneday table because I can find anything named even close to that in the article and you don't build it anywhere in your code example.

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Mon, 13 Jun 2011 18:19:42 GMT

[quote][b]DavidBridgeTechnology.com (6/13/2011)[/b][hr]I have a number table called Numbers on my system with a single column called Number and indexed values from 0 to 99,999. I use this table for all sorts of string and date manipulations. I also have a permanent and fully indexed calendar table too. These two tables are used all over the place and are highly effective. [/quote]You're a man after my own heart. See the following link and you'll see why I certainly don't take offense to what you posted. :-)[url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

RE: Returning 0's in a SUM (SQL Spackle)

Patrick Cahill — Mon, 13 Jun 2011 10:37:17 GMT

David,I also have a calendar table in our production system and it performs very well and it makes our code consistent across the board. I agree there are many ways to accomplish this and what works best in one system might not be the best for another system.

RE: Returning 0's in a SUM (SQL Spackle)

DavidBridgeTechnology.com — Mon, 13 Jun 2011 09:59:52 GMT

I feel the need to add a [u]personal opinion[/u] here but please don't think I am saying there is a right or a wrong way. In the words of Dirty Harry "opinions are like Ar*eho les - everyone has one"As I stated earlier, I prefer the method of creating a calendar table and then linking the data in to populate it, which results in NULL's for the unmatched data. What I did not state was why. So here's why...It is readable and does not contain functions. I have been managing teams of sql and application developers for years and I have seen many brilliant and highly effective but totally un-maintainable chunks of sql. Breaking the problem down in to smaller, more manageable and sometime bleedin' obvious parts is generally a good thing as the next developer to work on the code may not have the same level of coding competence or ability to understand complexities quickly. Sure the clever code might run slightly faster and if performance is a problem then perhaps it is the way forward but ease of change should always be the top goal (IMHO)I also hate the use of functions where they can be avoided. Adding a function means that people often have to lookup what the function does and how it works (sometimes having to ask "Uncle Google"). Don't get me wrong here, I love functions but only where required. Functions can make a large chunk of SQL much easier to read and use in some cases. [u]Personally[/u] I don't think they do here.I have a number table called Numbers on my system with a single column called Number and indexed values from 0 to 99,999. I use this table for all sorts of string and date manipulations. I also have a permanent and fully indexed calendar table too. These two tables are used all over the place and are highly effective. The other great feature of a permanent calendar table is that you can add special dates and slowly changing dimension tables to them too.So there are my reasons.Hope this doesn't offend. Please remember this is only an opinion.Dave

RE: Returning 0's in a SUM (SQL Spackle)

Patrick Cahill — Mon, 13 Jun 2011 08:59:30 GMT

[quote][b]Ninja's_RGR'us (6/13/2011)[/b][hr]Congrats on the longest and thoroughest Sparkle ever! :w00t:[/quote]Thank you

RE: Returning 0's in a SUM (SQL Spackle)

harry9katz — Mon, 13 Jun 2011 08:07:12 GMT

HiYou have to add hour to both tablesSELECT cast(floor(cast(SaleTime as float)) as datetime)Saledate,DATEPART(hour,SaleTime) saleshour ,sum([SaleQty]) sumsales into #salessum FROM #SalesTestData group by cast(floor(cast(SaleTime as float)) as datetime),DATEPART(hour,SaleTime) order by cast(floor(cast(SaleTime as float)) as datetime),DATEPART(hour,SaleTime)here U get the first table that the article started withU get salesum but not on every hourThen U add to the #Clendertest for every day all the hoursan now it is simpleSELECT dbo.CalendarTest.hourid, dbo.CalendarTest.StartDate, ISNULL(SUM(dbo.saleoneday.sumsales), 0) AS sumsalesFROM dbo.saleoneday RIGHT OUTER JOIN dbo.CalendarTest ON dbo.saleoneday.Saledate = dbo.CalendarTest.StartDate AND dbo.saleoneday.saleshour = dbo.CalendarTest.houridGROUP BY dbo.CalendarTest.hourid, dbo.CalendarTest.StartDateORDER BY dbo.CalendarTest.StartDate, dbo.CalendarTest.hourid

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Mon, 13 Jun 2011 07:01:55 GMT

[quote][b]Matjaz Justin (6/13/2011)[/b][hr]This should be a simpler solution.[code="sql"]select N2.n as dan, N.n - 1 as Ura, isnull(sum(SaleQTY), 0) as Dfrom dbo.GetNums(24) as N cross join dbo.GetNums(31) as N2 left join (select datepart(day, SaleTime) as Dan, datepart(hh, SaleTime) as Ura, sum(SaleQTY) as SaleQTY from dbo.#SalesTestData group by datepart(day, SaleTime), datepart(hour, SaleTime))as D on D.Dan = N2.n and D.Ura = (N.n - 1)group by N2.n, N.norder by 1, 2[/code]Uncle Goole will tell you more about function GetNums ( = Virtual Auxiliary Table of Numbers).[/quote]Hmmmm... yes, your code is simpler but your code is also calculating sums for the whole table instead of just a day or a month as was done in the article. Modify your code to handle a given month and then we'll see. ;-)

RE: Returning 0's in a SUM (SQL Spackle)

Ninja's_RGR'us — Mon, 13 Jun 2011 07:00:57 GMT

Congrats on the longest and thoroughest Sparkle ever! :w00t:

RE: Returning 0's in a SUM (SQL Spackle)

SQLRNNR — Mon, 13 Jun 2011 06:53:07 GMT

Nice article.

RE: Returning 0's in a SUM (SQL Spackle)

berzat.museski — Mon, 13 Jun 2011 06:44:30 GMT

No need for uncle Google in this case, spt_values is quite enough, unless you are querying some large data warehouse.However, I agree that GetNums is a cool trick to generate large order numbers.I was thinking of the following solution:I first add one computed column in the fact table (I assume that it is ok to do that) in order to keep the query SARGable. The new column will have the sales datetime rounded up to hour percision.ALTER TABLE #SalesTestDataADD SaleTimeHour AS CONVERT(DATETIME, CONVERT(VARCHAR,SaleTime,112)+' '+CAST(DATEPART(HOUR,SaleTime) AS VARCHAR(2))+':00:00.000',112) PERSISTEDThe report will then be formed by this single query:DECLARE @StartDate DATETIME, @EndDate DATETIMESET @StartDate='2011-01-01 00:00:00.000'SET @EndDate='2011-12-01 00:00:00.000'SELECT CAST(AllDates.ReportDayHour AS DATE) AS ReportDay, CAST(AllDates.ReportDayHour AS TIME) AS ReportHour, ISNULL(SUM(s.SaleQty),0) AS TotalSaleQtyFROM ( SELECT DATEADD(hh,h.number,DATEADD(dd,d.number,DATEADD(mm,m.number,@StartDate))) AS ReportDayHour FROM master..spt_values m CROSS JOIN master..spt_values d CROSS JOIN master..spt_values h WHERE m.type='p' AND m.number BETWEEN 0 AND DATEDIFF(mm,@StartDate,@EndDate) AND d.type='p' AND d.number BETWEEN 0 AND 30 AND h.type='p' AND h.number BETWEEN 0 AND 23) AS AllDatesLEFT JOIN #SalesTestData s ON s.SaleTimeHour=AllDates.ReportDayHourGROUP BY AllDates.ReportDayHourORDER BY AllDates.ReportDayHourThe parameters are the start and end moments of the reports, as any manager would want :)

RE: Returning 0's in a SUM (SQL Spackle)

tlewis-993411 — Mon, 13 Jun 2011 06:43:50 GMT

Although the "GetNums" function would indeed make this simpler in-line, it simply moves the complexity to another routine. Combined, the two parts have nearly the same code. That having been said, standardizing common functions like getnums() is a good practice. The article is probably best viewed as a general approach to reporting on "sparse data". Instead of hours or dates, consider regions, possible questionnaire responses, etc.I've found that using in-memory tables and "building" results incrementally to be much clearer and less error-prone than highly complex joins, and with very acceptable performance (consider the use case...) I've seen many cases where the added developer time in debugging and maintaining far exceeds the additional performance time over the life of the application.

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Mon, 13 Jun 2011 06:41:34 GMT

Folks, the GetNums function that Matjaz reference is Itzek Ben-Gan's cascading Cross Join code wrapped in a function. If you decide to use that instead of some other method, make sure you use the code that has "TOP" in it for the very reasons that Itzek mentions and the fact that I've also confirmed it's a bit faster than his previous rendition. Itsek's article can be found at the following URL:[url]http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers[/url]

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Mon, 13 Jun 2011 06:31:31 GMT

[quote][b]harry9katz (6/13/2011)[/b][hr]HiCan use cross-join with hour tableThis is easier solution by more costly on the execution plan[/quote]Cool! Got code? ;-)

RE: Returning 0's in a SUM (SQL Spackle)

Jeff Moden — Mon, 13 Jun 2011 06:29:58 GMT

[quote][b]Matjaz Justin (6/13/2011)[/b][hr]This should be a simpler solution.[code="sql"]select N2.n as dan, N.n - 1 as Ura, isnull(sum(SaleQTY), 0) as Dfrom dbo.GetNums(24) as N cross join dbo.GetNums(31) as N2 left join (select datepart(day, SaleTime) as Dan, datepart(hh, SaleTime) as Ura, sum(SaleQTY) as SaleQTY from dbo.#SalesTestData group by datepart(day, SaleTime), datepart(hour, SaleTime))as D on D.Dan = N2.n and D.Ura = (N.n - 1)group by N2.n, N.norder by 1, 2[/code]Uncle Goole will tell you more about function GetNums ( = Virtual Auxiliary Table of Numbers).[/quote]Although I'm sure that Uncle Google will tell us about the GetNums, it would be real handy if you'd simply provide the URL in the future since you're the one that brought up the function. ;-)

RE: Returning 0's in a SUM (SQL Spackle)

Matjaz Justin — Mon, 13 Jun 2011 05:18:33 GMT

This should be a simpler solution.[code="sql"]select N2.n as dan, N.n - 1 as Ura, isnull(sum(SaleQTY), 0) as Dfrom dbo.GetNums(24) as N cross join dbo.GetNums(31) as N2 left join (select datepart(day, SaleTime) as Dan, datepart(hh, SaleTime) as Ura, sum(SaleQTY) as SaleQTY from dbo.#SalesTestData group by datepart(day, SaleTime), datepart(hour, SaleTime))as D on D.Dan = N2.n and D.Ura = (N.n - 1)group by N2.n, N.norder by 1, 2[/code]Uncle Goole will tell you more about function GetNums ( = Virtual Auxiliary Table of Numbers).

RE: Returning 0's in a SUM (SQL Spackle)

harry9katz — Mon, 13 Jun 2011 02:38:52 GMT

HiCan use cross-join with hour tableThis is easier solution by more costly on the execution plan

RE: Returning 0's in a SUM (SQL Spackle)

DavidBridgeTechnology.com — Mon, 13 Jun 2011 02:13:45 GMT

There may be other ways but apart from using a dedicated number table rather than spt_values, this is exactly the way I do it.I am intrigued by the comment that this looks too complicated. I would like to see a simpler example.In short, create a calendar table that holds the date / time / base value range and then embellish it with the actual data. How can this be achieved any simpler? Please share.A good article in my opinion.Dave

RE: Returning 0's in a SUM (SQL Spackle)

berzat.museski — Mon, 13 Jun 2011 01:45:13 GMT

Looks too much complicated for the goal, isn't it ?

Returning 0's in a SUM (SQL Spackle)

Patrick Cahill — Sun, 12 Jun 2011 22:43:09 GMT

Comments posted to this topic are about the item [B]Returning 0's in a SUM (SQL Spackle)[/B]