Unique rows per day

Question

I need to get a list of users email addresses that logged into my website over the course of a day.

The list may contain duplicate email addresses but not more than one per day.

I need to obtain this data for a week.

I have a table that contains records for each successful login as follows..

[ID], [LOGIN_EMAIL], [LOGIN_TIME]

The following query gets me the whole data set for the week but I need to filter it to one email address per day and get the entire list for the week?

SELECT LOGIN_EMAIL 
FROM USER_LOGINS 
WHERE LOGIN_TIME IS BETWEEN @STARTDATE AND @ENDDATE

Is this related: http://stackoverflow.com/questions/192924/sql-distinct-for-2-fields-in-a-database — Ozair Kafray, Jun 20 '11 at 08:40
Do you need the actual email addresses, or just a count of the number of distinct ones? — Damien_The_Unbeliever, Jun 20 '11 at 08:45

score 2 · Answer 1 · answered Jun 20 '11 at 13:58

2

SELECT   LOGIN_EMAIL,
         DATEADD(DAY, DATEDIFF(DAY, 0, LOGIN_TIME), 0) AS LOGIN_DATE
FROM     USER_LOGINS 
WHERE    LOGIN_TIME BETWEEN @STARTDATE AND @ENDDATE
GROUP BY LOGIN_EMAIL, 
         DATEADD(DAY, DATEDIFF(DAY, 0, LOGIN_TIME), 0)

answered Jun 20 '11 at 13:58

Mikael Eriksson

136,425
22
210
281

score 0 · Answer 2 · answered Jun 20 '11 at 08:43

0

Use group by Day([LOGIN_TIME]), [LOGIN_EMAIL]

answered Jun 20 '11 at 08:43

Piotr Auguscik

3,651
1
22
30

score 0 · Answer 3 · answered Jun 20 '11 at 08:46

0

SELECT DISTINCT LOGIN_EMAIL,LOGIN_TIME FROM USER_LOGINS WHERE LOGIN_TIME IS BETWEEN @STARTDATE AND @ENDDATE

you may need to chnge LOGIN_TIME to Day format

answered Jun 20 '11 at 08:46

Tassadaque

8,129
13
57
89

score 0 · Answer 4 · answered Jun 20 '11 at 15:19

0

   select DISTINCT CONVERT(VARCHAR(10), LOGIN_TIME, 102) as LogDate, login_email 
   from user_logins WHERE ...

answered Jun 20 '11 at 15:19

iDevlop

24,841
11
90
149

Unique rows per day

4 Answers4