Returning 0's in a SUM (SQL Spackle)

Question

Post reply

Returning 0's in a SUM (SQL Spackle)

Patrick Cahill

SSCertifiable

Points: 6470
More actions
June 12, 2011 at 10:43 pm

#237723

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

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

berzat.museski SSC Journeyman Points: 99 More actions · Answer 1

berzat.museski

SSC Journeyman

Points: 99

June 13, 2011 at 1:45 am

#1337603

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

DavidBridgeTechnology.com SSChasing Mays Points: 649 More actions · Answer 2

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

harry9katz SSC Enthusiast Points: 110 More actions · Answer 3

Hi

Can use cross-join with hour table

This is easier solution by more costly on the execution plan

Matjaz Justin Mr or Mrs. 500 Points: 598 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 5

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

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 6

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? 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 7

Folks, the GetNums function that Matjaz reference is Itzik 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. Itzik's article can be found at the following URL:

~~http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers~~

UPDATE! The link above died because of the name change and system move many years back. Here's the latest correct working link.

https://www.itprotoday.com/server-virtualization/virtual-auxiliary-table-of-numbers

This reply was modified 5 years, 8 months ago by Jeff Moden.
This reply was modified 1 month ago by Jeff Moden. Reason: Updated URL

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tlewis-993411 SSC Veteran Points: 263 More actions · Answer 8

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.

berzat.museski SSC Journeyman Points: 99 More actions · Answer 9

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

WHEREm.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 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Nice article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 12

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

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

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

harry9katz SSC Enthusiast Points: 110 More actions · Answer 13

Hi

You have to add hour to both tables

SELECT 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 with

U get salesum but not on every hour

Then U add to the #Clendertest for every day all the hours

an now it is simple

SELECT dbo.CalendarTest.hourid, dbo.CalendarTest.StartDate, ISNULL(SUM(dbo.saleoneday.sumsales), 0) AS sumsales

FROM dbo.saleoneday RIGHT OUTER JOIN

dbo.CalendarTest ON dbo.saleoneday.Saledate = dbo.CalendarTest.StartDate AND dbo.saleoneday.saleshour = dbo.CalendarTest.hourid

GROUP BY dbo.CalendarTest.hourid, dbo.CalendarTest.StartDate

ORDER BY dbo.CalendarTest.StartDate, dbo.CalendarTest.hourid

Patrick Cahill SSCertifiable Points: 6470 More actions · Answer 14

Ninja's_RGR'us (6/13/2011)
Congrats on the longest and thoroughest Sparkle ever! :w00t:

Thank you