Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning 0's in a SUM (SQL Spackle)


Returning 0's in a SUM (SQL Spackle)

Author
Message
Patrick Cahill
Patrick Cahill
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3733 Visits: 990
Comments posted to this topic are about the item Returning 0's in a SUM (SQL Spackle)
berzat.museski
berzat.museski
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 102
Looks too much complicated for the goal, isn't it ?
DavidBridgeTechnology.com
DavidBridgeTechnology.com
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 234
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
harry9katz
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 234
Hi
Can use cross-join with hour table
This is easier solution by more costly on the execution plan
Matjaz Justin
Matjaz Justin
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 539
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
Jeff Moden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52460 Visits: 40327
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52460 Visits: 40327
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52460 Visits: 40327
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

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tlewis-993411
tlewis-993411
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 80
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
berzat.museski
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 102
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search