Prev: SSIS date type casts truncating hour, minute
Next: thanks!!
From: Doug Howell on 12 Nov 2009 08:30
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?
From: Uri Dimant on 12 Nov 2009 08:59
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" <douglasehowell(a)gmail.com> wrote in message
news:093aa2d5-4a3d-4ef4-af80-8207fc9d2015(a)v25g2000yqk.googlegroups.com...
>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?


From: Doug Howell on 12 Nov 2009 09:20
Thanks for the reply.
SQL Server 2008

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

From: Plamen Ratchev on 12 Nov 2009 10:06
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
From: Doug Howell on 12 Nov 2009 11:00
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!
 | 
Pages: 1
Prev: SSIS date type casts truncating hour, minute
Next: thanks!!