Sql Server Server Query - Count Distinct Datetime Field

SQL Server Server query - Count distinct DateTime field

You need to do any grouping on a Date only version of your datefield, such as this.

SELECT
    CONVERT(VARCHAR(10), YourDateColumn, 101),
    COUNT(*)
FROM
    YourTable
GROUP BY
    CONVERT(VARCHAR(10), YourDateColumn, 101)

I usually do this though, as it avoids conversion to varchar.

SELECT
    DATEPART(yy, YourDateColumn),
    DATEPART(mm, YourDateColumn),
    DATEPART(dd, YourDateColumn),
    COUNT(*)
FROM
    YourTable
GROUP BY
    DATEPART(yy, YourDateColumn),
    DATEPART(mm, YourDateColumn),
    DATEPART(dd, YourDateColumn)

EDIT: Another way to get just the date part of a datetime

DATEADD(d, 0, DATEDIFF(d, 0, YourDateColumn))

How to get a distinct date count of Datetime in SQL Server

I think this would work.

SELECT CONVERT(DATE, postedDate) AS postedDate, COUNT(*)
FROM user
GROUP BY CONVERT(DATE, postedDate)

By converting to postedDate to DATE, it removes the time component and thus allows you to group by just the date component.

SQL how to count distinct id in changing time ranges

You can use conditional aggregation:

select user_id,
    count(distinct case when date >= current_date - 1 day and date < current_date then fd_id end) as cnt_1d,
    count(distinct case when date >= current_date - 3 day and date < current_date then fd_id end) as cnt_3d,
    ...
from mytable
goup by user_id

You can play around with the date expressions to set the ranges you want. The above works on entire days, and does not include the current day.

SQL Count Distinct Value Oldest DateTime

Below is for BigQuery Standard SQL

#standardSQL
SELECT F.Identifier, F.Videogame, F.Developer,
CASE WHEN S.String='1581' THEN 'Made by billy'
     WHEN S.String='1903' THEN 'Made by bob'
     WHEN S.String='5849' THEN 'Made by lilly'
     ELSE 'worked on by someone else' END AS Final_Name,
     COUNT(DISTINCT S.User_ID) AS COUNT
FROM `project.dataset.table1` AS F
JOIN (
  SELECT AS VALUE ARRAY_AGG(t ORDER BY datetime LIMIT 1)[OFFSET(0)]
  FROM `project.dataset.table2` t
  GROUP BY User_ID

) AS S
ON F.Identifier=S.Identifier
GROUP BY F.Identifier, F.Videogame, F.Developer, Final_Name 

If to apply to sample data from your question - result will be

Row Identifier  Videogame   Developer   Final_Name                  Count    
1   abcd        red dead    company1    worked on by someone else   1    
2   abcd        red dead    company1    Made by billy               2    
3   defg        halo 3      company2    worked on by someone else   1    
4   hijk        fortnite    company3    Made by lilly               1    

SQL Server : count distinct every 30 minutes or more

This is a two-level aggregation (GROUP BY) problem. You need to start with a subquery to get the first and last timestamp of each session.

             SELECT MIN(Time1) start_time,
                    MAX(Time1) end_time,
                    session_id, customer_id
               FROM table1
              GROUP BY session_id, customer_id

Next you need to use the subquery like this:

SELECT COUNT(session_id),
       COUNT(DISTINCT customer_id),
       CAST(start_time AS DATE)
  FROM (
             SELECT MIN(Time1) start_time,
                    MAX(Time1) end_time,
                    session_id, customer_id
               FROM table1
              GROUP BY session_id, customer_id
       ) a
 WHERE DATEDIFF(MINUTE, start_time, end_time) >= 30
 GROUP BY CAST(start_time AS DATE);

Count number of Distinct days in query

You can CAST as date

SELECT COUNT(DISTINCT CAST(StartDate AS DATE))
FROM   Stats
WHERE  StartDate >= '20130101' AND StartDate < '20140101' 

Also use an unambiguous date format such as yyyymmdd and >= < not BETWEEN.

Your current query would include the 31st December if there was a row with exactly the value 20131231 00:00:00 but not any with different times on that date. I doubt that is intentional.

Count distinct dates within timestamp by Month SQL

In SQL Server, I would recommend one of these approaches:

select year(timestamp), month(timestamp), count(distinct convert(date, timestamp)
from t
group by year(timestamp), month(timestamp);

Or:

select format(timestamp, 'yyyy-MM'), count(distinct convert(date, timestamp))
from t
group by format(timestamp, 'yyyy-MM');

I see no need for subqueries or CTEs.

How to quickly select DISTINCT dates from a Date/Time field, SQL Server

Every option that involves CAST or TRUNCATE or DATEPART manipulation on the datetime field has the same problem: the query has to scan the entire resultset (the 40k) in order to find the distinct dates. Performance may vary marginally between various implementaitons.

What you really need is to have an index that can produce the response in a blink. You can either have a persisted computed column with and index that (requires table structure changes) or an indexed view (requires Enterprise Edition for QO to consider the index out-of-the-box).

Persisted computed column:

alter table foo add date_only as convert(char(8), [datetimecolumn], 112) persisted;
create index idx_foo_date_only on foo(date_only);

Indexed view:

create view v_foo_with_date_only
with schemabinding as 
select id
    , convert(char(8), [datetimecolumn], 112) as date_only
from dbo.foo;   
create unique clustered index idx_v_foo on v_foo_with_date_only(date_only, id);

Update

To completely eliminate the scan one could use an GROUP BY tricked indexed view, like this:

create view v_foo_with_date_only
with schemabinding as 
select
    convert(char(8), [d], 112) as date_only
    , count_big(*) as [dummy]
from dbo.foo
group by convert(char(8), [d], 112)

create unique clustered index idx_v_foo on v_foo_with_date_only(date_only)

The query select distinct date_only from foo will use this indexed view instead. Is still a scan technically, but on an already 'distinct' index, so only the needed records are scanned. Its a hack, I reckon, I would not recommend it for live production code.

AFAIK SQL Server does not have the capability of scanning a true index with skipping repeats, ie. seek top, then seek greater than top, then succesively seek greater than last found.

get the weekly distinct users count within two dates

You can try this.

SET DATEFIRST 7
SELECT DISTINCT 
   'week '+ CAST(DATEPART(WEEK, Datetime)AS NVARCHAR(10)) AS weeknumber,
    COUNT(DISTINCT userID ) weeklogcount
FROM 
    dbo.PRTL_UserAccessLog
WHERE
    Datetime > '2017-12-01' AND Datetime < '2017-12-31'
    AND usertypeid=1
GROUP BY 'week '+ CAST(DATEPART(WEEK, Datetime)AS NVARCHAR(10))
ORDER BY weeknumber

---

Related Topics