Returning 0's in a SUM (SQL Spackle)

 Author Message Patrick Cahill SSCarpal Tunnel Group: General Forum Members Points: 4164 Visits: 1016 Comments posted to this topic are about the item Returning 0's in a SUM (SQL Spackle) berzat.museski SSC Rookie Group: General Forum Members Points: 37 Visits: 102 Looks too much complicated for the goal, isn't it ? DavidBridgeTechnology.com SSC Veteran Group: General Forum Members Points: 265 Visits: 238 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 BridgeDavid Bridge Technology Limitedwww.davidbridgetechnology.com harry9katz Valued Member Group: General Forum Members Points: 52 Visits: 234 HiCan use cross-join with hour tableThis is easier solution by more costly on the execution plan Matjaz Justin SSC Veteran Group: General Forum Members Points: 256 Visits: 543 This should be a simpler solution.`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`Uncle Goole will tell you more about function GetNums ( = Virtual Auxiliary Table of Numbers). Jeff Moden SSC Guru Group: General Forum Members Points: 340519 Visits: 42644 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 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`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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 340519 Visits: 42644 harry9katz (6/13/2011)HiCan use cross-join with hour tableThis is easier solution by more costly on the execution planCool! Got code? ;-) --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 340519 Visits: 42644 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs tlewis-993411 SSC-Enthusiastic Group: General Forum Members Points: 145 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 SSC Rookie Group: General Forum Members Points: 37 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 #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