How to get first login per day of users in a date range

I have a table in an RDBMS which logs the user logins. A user can login to the system many times during the day and my application logs these logins.

The problem is, I need to write a query which gets the first login per day of the users between desired dates like 2011-04-21 to 2011-07-08

Login Log Table

userID  Login Date 
guess   2011-04-21 16:46:46.000
Admin   2011-05-03 17:32:15.000
guess   2011-05-05 15:48:54.000
guess   2011-06-01 14:14:50.000
guess   2011-06-01 14:23:18.000
my_user 2011-07-08 15:42:20.000
guess   2011-07-08 16:15:08.000
guess   2011-07-08 16:19:14.000
my_user 2011-07-08 16:36:45.000
my_user 2011-07-08 16:37:41.000
my_user 2011-07-08 16:42:21.000

Query Should give result like below (The first login date/time for each day)

UserID  First_Login_D开发者_运维知识库ate
guess   2011-04-21 16:46:46.000
Admin   2011-05-03 17:32:15.000
guess   2011-05-05 15:48:54.000
guess   2011-06-01 14:14:50.000
my_user 2011-07-08 15:42:20.000
guess   2011-07-08 16:15:08.000

---

SELECT UserID, MIN(Login_Date) as First_Login_Date
FROM Login
WHERE Login_Date BETWEEN inputStartDate AND inputEndDate
GROUP BY UserID, YEAR(Login_Date), MONTH(Login_Date), DAY(Login_Date)

Edited to group BY user AND day. Your question was not specific that you wanted the first login of each user for each day they logged in. I'm positive this solution works for MS SQL Server 2005+, if you need a different RDBMS, comment.

If you'd like more options for grouping by a certain date, feel free to look at this article.

---

Since I don't know your exact database system, for MS SQL 2008 the following should work (I also assume that you are using paameters for your query):

declare @startDate datetime = '2011-04-21'
declare @endDate datetime = '2011-07-08'
SELECT UserId, MIN([Login Date]) as First_Login_date FROM LoginTable WHERE [Login date] between @startDate  and @endDate group by UserId 

---

It seems that you need to find first user login per day. For oracle this should work:

select UserID, min(Login_Date) from your_table
WHERE Login_Date between  to_date('YYYY-MM-DD', '2011-04-21') 
          and  to_date('YYYY-MM-DD', '2011-07-08') 
group by UserID, to_char(Login_Date, 'YYYYMMDD');

---

try this

SELECT HOUR(LOGIN_TIME), COUNT(*) FROM SESSION_DETAILS
GROUP BY HOUR(LOGIN_TIME)