Returning 0's in a SUM (SQL Spackle)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Patrick Cahill » Returning 0's in a SUM (SQL Spackle)

27 posts, Page 1 of 3

1 2 3 »»»

Returning 0's in a SUM (SQL Spackle)

Rate Topic

Display Mode

Topic Options

Author

Message

Patrick Cahill

Posted Sunday, June 12, 2011 10:43 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:40 AM
Points: 2,412, Visits: 578

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

Post #1124040

berzat.museski

Posted Monday, June 13, 2011 1:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:00 AM
Points: 5, Visits: 92

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

Post #1124077

DavidBridgeTechnology.com

Posted Monday, June 13, 2011 2:13 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:19 AM
Points: 58, Visits: 184

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

David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com

Post #1124085

harry9katz

Posted Monday, June 13, 2011 2:38 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:51 AM
Points: 36, Visits: 188

Hi
Can use cross-join with hour table
This is easier solution by more costly on the execution plan

Post #1124098

Matjaz Justin

Posted Monday, June 13, 2011 5:18 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, May 12, 2013 3:47 PM
Points: 76, Visits: 457

This should be a simpler solution.

select N2.n as dan, N.n - 1 as Ura, isnull(sum(SaleQTY), 0) as D
from 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.n
order by 1, 2

Uncle Goole will tell you more about function GetNums ( = Virtual Auxiliary Table of Numbers).

Post #1124165

Jeff Moden

Posted Monday, June 13, 2011 6:29 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

Matjaz Justin (6/13/2011)

This should be a simpler solution.

select N2.n as dan, N.n - 1 as Ura, isnull(sum(SaleQTY), 0) as D
from 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.n
order by 1, 2

Uncle Goole will tell you more about function GetNums ( = Virtual Auxiliary Table of Numbers).

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. Wink

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

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/

Post #1124197

Jeff Moden

Posted Monday, June 13, 2011 6:31 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

harry9katz (6/13/2011)

Hi
Can use cross-join with hour table
This is easier solution by more costly on the execution plan

Cool! Got code? Wink

--Jeff Moden

Post #1124201

Jeff Moden

Posted Monday, June 13, 2011 6:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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:
http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers

--Jeff Moden

Post #1124211

tlewis-993411

Posted Monday, June 13, 2011 6:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, December 15, 2012 9:45 AM
Points: 35, Visits: 77

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.

Post #1124215

berzat.museski

Posted Monday, June 13, 2011 6:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:00 AM
Points: 5, Visits: 92

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 #SalesTestData
ADD SaleTimeHour AS CONVERT(DATETIME, CONVERT(VARCHAR,SaleTime,112)+' '+CAST(DATEPART(HOUR,SaleTime) AS VARCHAR(2))+':00:00.000',112) PERSISTED

The report will then be formed by this single query:

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @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 TotalSaleQty
FROM (
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 AllDates
LEFT JOIN #SalesTestData s ON s.SaleTimeHour=AllDates.ReportDayHour
GROUP BY AllDates.ReportDayHour
ORDER BY AllDates.ReportDayHour

The parameters are the start and end moments of the reports, as any manager would want :)

Post #1124217

« Prev Topic | Next Topic »

27 posts, Page 1 of 3

1 2 3 »»»

Permissions