SQL Server 2012: How do I eliminate multiple statements in this code to a more efficient, single statement?

Question

This code completes under a second, however its seems like I've taken the "Mr. Bean" way around getting there.

The goal is to get the average number of orders processed in a single day based on how many days exist in the current database. The loadcount column represents how many orders are processed in a single batch. The batch is driven by the date and time during the day.

 -- // ditch the time to get the start/end date ranges uniform by date //--
 ;with makeuniform as
 (SELECT
 cast(startrangedatetime as date) as startdate
 ,cast(endrangedatetime as date) as enddate
 ,[LoadCount]
 FROM [audit].[Tracker]
 )
 -- // next, rollup all the loadcounts by uniform date // --
 select startdate,
 sum(loadcount) TotalOrders
 into #sumthedays
 from makeuniform
 group by startdate;
 -- // since the counts have been rolled up by day, get the table count // --
 declare @howmanydays int 
 select @howmanydays = count(*) from #sumthedays;
 -- // next get the average traffic per day // --
 select sum(TotalOrders) / @howmanydays 
 from #sumthedays
 drop table #sumthedays

Answer 1

This could be simplified. You don't have to create a temporary table to get your unique day count. You can use the COUNT function on the StartDate column directly.

Try this:

SELECT
 TotalLoadCount / NULLIF(TotalDayCount, 0) AvgLoadCountByDay
FROM
(
SELECT
 COUNT(DISTINCT CAST(StartDate as date)) TotalDayCount
 ,SUM(LoadCount) TotalLoadCount
FROM
 dbo.Tracker
) x 

Obviously, you can write it directly like this as well:

SELECT
 SUM(LoadCount) / NULLIF(COUNT(DISTINCT CAST(StartDate AS date)), 0) AvgLoadCountByDay
FROM
 dbo.Tracker

If my understanding of your problem is correct, this query should give you the same result. Let me know if it doesn't.

I personally prefer the first version with derived table as it helps with the readability of the query. You are not paying any additional cost because SQL Server will generate the same execution plan for both the queries.

• plditallo: Brilliantly done! :)

Answer 2

this doesn't look bad to me at all,

• I had no trouble following along
• you said it is a Fast Query
• nothing looks over complicated

I would suggest testing it more, maybe see how it would run in a very large database, that is something that I always try to keep in mind, what happens when my tables become very large?