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

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
FROM [audit].[Tracker]
)
-- // next, rollup all the loadcounts by uniform date // --
select startdate,
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


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
FROM
(
SELECT
COUNT(DISTINCT  CAST(StartDate as date)) TotalDayCount
FROM
dbo.Tracker
) x


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

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

• Brilliantly done! :) – plditallo Oct 31 '13 at 21:16

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?