harry9katz (6/13/2011)
HiYou 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
Change is inevitable... Change for the better is not.