CODEWITHSUNDEEP
sqlsql-serverdatetime
sprocket12
sprocket12

Reputation: 5488

How to Count Days Present In Values of a Field

I have a field called CreatedOn with values like :

2012-06-19 08:42:48.000
2012-06-19 08:43:05.000
2012-06-19 08:43:27.000
2012-06-19 08:53:22.000
...
2012-06-20 09:20:54.000
2012-06-20 09:21:13.000
...
2012-06-21 10:02:17.000
2012-06-21 11:43:19.000
...
2012-06-22 12:00:40.000
2012-06-22 12:11:56.000

... (weekend)


2012-06-25 13:00:13.000
2012-06-25 13:12:24.000
...
2012-06-26 13:41:27.000
2012-06-26 13:41:44.000
2012-06-26 13:42:01.000

I would like the output to show -- 6 (days). But its not as simple as not counting weekends, as there may be other days that are not counted, best way to make sure is to do it based on the data.

Any ideas?

I was able to make : DATEDIFF(w, MIN(CreatedOn), MAX(CreatedOn)) but as stated its inaccurate, it shows dates that have no corresponding data,

Upvotes: 1

Views: 76

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

You want to count days that had any activity? The below is slightly more complex than needed:

select COUNT(*) from (
select DATEADD(day,DATEDIFF(day,0,CreatedOn),0) as dt,COUNT(*) as cnt
from Table
group by DATEADD(day,DATEDIFF(day,0,CreatedOn),0)
) dayCounts

But you can re-use the inner query if you need more detail about particular days.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453233

Assuming 2008+

SELECT COUNT(DISTINCT (CAST (CreatedOn AS DATE))) AS NumberOfDays
FROM YourTable

Upvotes: 1

Related Questions