CODEWITHSUNDEEP
sqlsql-server
Beems
Beems

Reputation: 810

SQL - Collect Oldest and Latest Records, and Get Days Between

Using: SQL Server 2008 R2

Is it possible, without using a CTE, to get the oldest and latest records, and then get the days between (DATEDIFF) within a single SELECT statement?

To clarify, here is my current SELECT statement:

SELECT 
COUNT(*) as 'Sessions', 
MAX(f_sessiondate) AS 'Latest', 
MIN(f_sessiondate) as 'Oldest'

So, say I get the following result

Sessions          Latest                         Oldest  
2610              2014-10-28 00:00:00.000        2013-12-23 00:00:00.000

What I want is to get the "Average Sessions Per Day" by doing a DATEDIFF between the 'Latest' and 'Oldest' entries. So the 'days' would be 309, and "Average Sessions Per Day" would be 8.45 (rounded).

EDIT:

By some stroke of luck, I got it to work to some degree, though I'm missing my decimals. Here's what I added:

CAST(COUNT(*) / DATEDIFF(day, MIN(f_sessiondate),MAX(f_sessiondate)) as DECIMAL(18,2)) as 'Average Sessions Fixed',

Based on the (updated) numbers above, I should receive a result of 8.44 (309 days). However, SQL is giving me the result of 8.00. How can I get the proper decimals?

Thanks in advance,

Beems

Upvotes: 0

Views: 158

Answers (1)

Will
Will

Reputation: 928

By default counts are integers and any mathematical operations will result in an integer. To produce a decimal, first cast the count(*) to a decimal before performing operations.

CAST(CAST(COUNT(*) as Decimal) / DATEDIFF(day, MIN(f_sessiondate),MAX(f_sessiondate)) as DECIMAL(18,2)) as 'Average Sessions Fixed',

Upvotes: 1

Related Questions