Monday, 15 August 2011

SQL query calculate total number of occurrences with other conditions - SQL Server 2008 -


I have a table that tracks user login / logout activity; I am trying to create a query that is Returns the total number of people logged in for the day, and returns a line in the table for each line. Here's an example of the table with which I'm working:

Enter image details here

And for this, here is the SQLFiddle:

There is a timestamp of the time when the action is taken, which is userID of the person who has acted, and the type of action itself . "Light" means "log in", and "LO" means "logout".

Here is an example of the search result of the query:

image here Enter details

There are some things to note about this query:

  • The query is only for determining the number of people who are For "present".
  • This is counted only if unique users are logged for days
  • It does not matter if they log in and log out multiple times during the same day .
  • The timestamp field is automatically converted to zero hour / minute / seconds in the day.

I "There is some hard time finding the best way to contact this link. Here are some things I have tried, but I can not find it. P>

  SELECT COUNT (*) AS Total assets (selectmap), COUNT (*) presence present with presence where presence timestamp & gt; {Ts '2015-01-01 00:00:00'} and presence. Action = [User ID] by group [Timestamp] [Timestamp] ASC by group [UIID]] Group  

Also:

Select [Timestamp], COUNT (*) Total attendance from ASADAd presence where appearance. Timestamp & gt; {Ts '2015-01-01 00:00:00'} and presence. Action = 'UserID' by [UIIDID], [Timestamp] Order [Timestamp] ASC

And finally :

  SELECT [timestamp], COUNT (*) presence where ASD presence is in presence. Tempest & gt; {Ts '2015-01-01 00:00:00'} and presence.action = 'LE' Group [Timestamp], [Agent Login] Hogging COUNT (*) & Gt; 1 Order [Timestamp] ASC  

Where am I wrong here? I know that I have not addressed the timestamp conversion yet, but I thought the part of COUNT () should be easier than I am creating it. I'm not sure where I'm wrong. Can I get some information on this?

Thanks everyone!

  select cast (date as "timestamp"), calculate (different Case "attendance" = 'light' user id nd) as appearance by the presence group (according to the date of "timestamp") ORDER BY_date; Select  

  cast ("timestamp" as the date), count as date (different userID) as appearance in the form of presence "action "= 'Light' group by cast (date" timestamp "according to the order);  

No comments:

Post a Comment