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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21105 Visits: 18259
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

Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20939 Visits: 9671
Congrats on the longest and thoroughest Sparkle ever! w00t
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39931
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.
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
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
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
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: 3712 Visits: 987
Ninja's_RGR'us (6/13/2011)
Congrats on the longest and thoroughest Sparkle ever! w00t


Thank you
DavidBridgeTechnology.com
DavidBridgeTechnology.com
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 234
I feel the need to add a personal opinion 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. Personally 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

David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
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: 3712 Visits: 987
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39931
DavidBridgeTechnology.com (6/13/2011)
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.



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. :-)
http://www.sqlservercentral.com/articles/T-SQL/62867/

--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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39931
harry9katz (6/13/2011)
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


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.

--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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45248 Visits: 39931
berzat.museski (6/13/2011)
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


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...
--===== 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 function
DECLARE @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" table
DECLARE @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 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
;
SET STATISTICS TIME OFF;



Here's the output on my ol' 9 year old, single CPU box. The method from the article is more than 12 times faster.

================================================================================
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:
CPU time = 390 ms, elapsed time = 731 ms.
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:
CPU time = 9328 ms, elapsed time = 12354 ms.


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