CODEWITHSUNDEEP
sql-servergroup-bydatepart
carrot_programmer_3
carrot_programmer_3

Reputation: 955

Unique rows per day

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

Upvotes: 3

Views: 1563

Answers (4)

iDevlop
iDevlop

Reputation: 25272

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

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)

Upvotes: 2

Tassadaque
Tassadaque

Reputation: 8199

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

Upvotes: 0

Piotr Auguscik
Piotr Auguscik

Reputation: 3681

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

Upvotes: 0

Related Questions