SQL syntax (part 2)

[Doug Howell]

I accidentally posted before I was done on the last one.....

I have a query that I would like to modify as follows, but am not
quite sure how to do it:

SELECT [GenEvents EventLog].EventTime, [GenEvents EventLog].ActorID
FROM [GenEvents EventLog]
WHERE ((([GenEvents EventLog].ActorID) Is Not Null) AND (([GenEvents
EventLog].Message) Like "*logged in*"))
ORDER BY [GenEvents EventLog].EventTime DESC;

Yields something like this:

EventTime ActorID
11/12/2009 7:09:36 AM DHOWELL (Doug Howell)
11/12/2009 6:23:16 AM WSMITH (Will Smith)
11/11/2009 4:20:52 PM WSMITH (Will Smith)
11/11/2009 3:35:30 PM RBRAMWELL (Richard Bramwell)
11/11/2009 10:30:09 AM SBANSE (Steve Banse)
11/11/2009 7:56:47 AM RBRAMWELL (Richard Bramwell)
11/11/2009 7:47:17 AM BBELL (Brian Bell)
11/11/2009 6:06:53 AM JMEZA (Jose Meza)
11/10/2009 4:26:36 PM DHOWELL (Doug Howell)
11/10/2009 7:44:56 AM DHOWELL (Doug Howell)
11/10/2009 6:45:53 AM DHOWELL (Doug Howell)
11/9/2009 12:51:07 PM RBRAMWELL (Richard Bramwell)
11/9/2009 12:44:12 PM MPALMER (Mark Palmer)
11/9/2009 8:00:40 AM DHOWELL (Doug Howell)

What I'd really like it to yield is:

1) A "unique" count on a per day basis.
IE above data would give:

11/12/2009 2
11/11/2009 6
11/10/2009 3
11/9/2009 3

2) A "total" count on a per day basis.
IE above data would give:

11/12/2009 2
11/11/2009 5
11/10/2009 1
11/9/2009 3

Thoughts?

[Uri Dimant]

Is it SQL Server 2005/2008?

SELECT COUNT(*) OVER (PARTITION BY DATEADD(d,DATEDIFF(d,0,EventTime),0)) AS
perday

FROM tbl

Can you provide sample data to be testing + an epxected result?

"Doug Howell" <douglas...@gmail.com> wrote in message
news:093aa2d5-4a3d-4ef4...@v25g2000yqk.googlegroups.com...

[Doug Howell]

Thanks for the reply.
SQL Server 2008

Sample data and desired results were in the original post.
Did you see it there?

[Plamen Ratchev]

This should do it:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, EventTime), 0) AS event_date,
COUNT(DISTINCT ActorID) AS unique_count,
COUNT(ActorID) AS total_count
FROM [GenEvents EventLog]
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, EventTime), 0);

--
Plamen Ratchev
http://www.SQLStudio.com

[Doug Howell]

I had to add a WHERE and correct the database/table name......

This works perfectly:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, EventTime), 0) AS login_date,

COUNT(DISTINCT ActorID) AS unique_count,
COUNT(ActorID) AS total_count

FROM [GenEvents].[dbo].[EventLog]
WHERE [Message] Like '%logged in%'

GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, EventTime), 0)

ORDER BY login_date DESC;

Thanks for the help!