query date of a datetime format

G

Guest

I have two tables. One has the user_id and the sessions in a datetime format
(yyyy,mm,dd hh:mm:ss). The other one has the user_id and the users' company.

Example
table1
user_id time
t1 12-02-2005 11:51:00
t2 12-02-2005 12:02:00
t1 12-02-2005 12:30:30

table2
user_id company
t1 xpto1
t2 xpto2

If i want to know of many distinct users by company had connected by day how
can i do the query? It's easy if i want the number of sessions of each one!

result
date company #
12-02-2005 xpto1 1
12-02-2005 xpto2 1

Thanks in advance!
 
J

John Spencer (MVP)

Two queries may be the simplest and quickest.

QryOne:
SELECT Distinct User_ID, DateValue(Time) as ConnectDate
FROM Table1

Second query uses that
SELECT Company, ConnectDate, Count(Q.User_ID) as UserCount
FROM Table2 as T INNER JOIN QryOne as Q
On T.User_Id = Q.UserID
GROUP BY T.Company, ConnectDate

You can do this in one query.
SELECT Company, ConnectDate, Count(Q.User_ID) as UserCount
FROM Table2 as T INNER JOIN
(SELECT Distinct User_ID, DateValue(Time) as ConnectDate
FROM Table1) as Q
 
G

Guest

Great! It works just fine!
Thank You!

John Spencer (MVP) said:
Two queries may be the simplest and quickest.

QryOne:
SELECT Distinct User_ID, DateValue(Time) as ConnectDate
FROM Table1

Second query uses that
SELECT Company, ConnectDate, Count(Q.User_ID) as UserCount
FROM Table2 as T INNER JOIN QryOne as Q
On T.User_Id = Q.UserID
GROUP BY T.Company, ConnectDate

You can do this in one query.
SELECT Company, ConnectDate, Count(Q.User_ID) as UserCount
FROM Table2 as T INNER JOIN
(SELECT Distinct User_ID, DateValue(Time) as ConnectDate
FROM Table1) as Q
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top