Summary query for number of attendees out of all

[tanehome]

hi all
i'm trying to write a query to summarize attendance counts per department
i've two tables one for event log (login/logout) transactions and the other for users details
i need to show the result as
dept. attend users all users
dept1 5 20
dept2 8 10

my query is: but it givs error

declare @FromDate varchar(10) ='21/01/2021'
declare @ToDate varchar(10) ='21/01/2021'
SELECT
dbo.USERS.Department,
count(distinct [sUserName]) AS [attnd users],
(select count(sUserName) from users group by location, department) as [All users]

FROM dbo.EVENT_LOG RIGHT OUTER JOIN
dbo.USERS ON dbo.EVENT_LOG.nUserID = dbo.USERS.sUserID
WHERE
convert(date,dtDateTime,103) BETWEEN convert(date,@FromDate,103) AND convert(date,@ToDate,103)
and convert(char(8),dtDateTime,108) > '0'

Group By dbo.USERS.Department
ORDER BY dbo.USERS.Department

[jeffw8713]

There are several issues with this query...you should not use a string data type to compare dates, nor should you then convert the column data to a date for comparison.

Declare the variables as the same data type as the column - which appears to be datetime. Then - use an open half-interval check instead of between for the date check. Also - no reason to check that the column is greater than 0 if you are already checking that the column falls within a specified range.

Declare @FromDate datetime = '20210121'
      , @ToDate datetime = '20210121';  --the same day????


 Select dbo.USERS.Department
      , count(Distinct [sUserName]) As [attnd users]
      , (Select count(sUserName)
           From users
          Group By
                location
              , department
        ) As [All users]
   From dbo.EVENT_LOG
  Right Join dbo.USERS                  On dbo.EVENT_LOG.nUserID = dbo.USERS.sUserID
  Where dtDateTime >= @FromDate
    And dtDateTime <  dateadd(day, 1, @ToDate)
  Group By
        dbo.USERS.Department
  Order By
        dbo.USERS.Department

Additionally - you really need to use table aliases - especially when using sub-queries. It is possible to reference columns from the outer query in the sub-query that don't exist in the sub-queries table(s) which can cause inconsistent results.

One more thing - what is the error you are getting?

[tanehome]

thank you for ur reply
i'm getting the same error with both queries

Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

sorry, my knowledge in SQL is not so good.

[jeffw8713]

Oh - that is because your sub-query is returning more than one count because of your group by statement. You will get a count of 'all users' based on each location and each department in each location.

You probably want to remove the group by - and make the sub-query a correlated sub-query (e.g. add a where clause to filter by department).

[mike01]

Right joints are ugly and uncommon. Can you do something like this?

Declare @FromDate datetime = '20210121'
      , @ToDate datetime = '20210121';  --the same day????


 Select u.Department
      , count(distinct e.nUserID) as AttendUsers
      , count(distinct u.sUserID) as AllUsers
   From dbo.USERS u
	Left Join dbo.EVENT_LOG e
	  On e.nUserID = u.sUserID
	  and  e.dtDateTime >= @FromDate
	  And e.dtDateTime <  dateadd(day, 1, @ToDate)
  Group By
        u.Department
  Order By
        u.Department

[tanehome]

thank u mike01 for your reply

i've tried it but gives me another error for the second "as"
i tried to remove or modify with no luck

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'as'.

[jeffw8713]

@mike01 - there is a typo after distinct, remove the extra ( and it should work. Here is a solution using your original right outer join:

Declare @FromDate datetime = '20210121'
      , @ToDate datetime = '20210121';  --the same day????


 Select u.Department
      , count(Distinct el.nUserID) As [attnd users]
      , count(Distinct u.sUserID) As [All users]
   From dbo.EVENT_LOG                   el
  Right Join dbo.USERS                   u On u.sUserID = dl.nUserID
  Where el.dtDateTime >= @FromDate
    And el.dtDateTime <  dateadd(day, 1, @ToDate)
  Group By
        u.Department
  Order By
        u.Department

Now - this probably will not result in the answer you are expecting because you are filtering on the non-preserved table (dbo.EVENT_LOG) which eliminates the users that did not attend an event. To fix that - use @mike01's solution.

[tanehome]

thanks a lot for both of you it works great now
I didn't saw the extra "(" at all, I've to check my eyes

[tanehome]

@jeffw8713

  Where el.dtDateTime >= @FromDate
    And el.dtDateTime <  dateadd(day, 1, @ToDate)

sorry, but just i need to understand that condition and why it put with "join" and not in "where" clause
cause I tried to change the declaration to show the current date (today) as below but it gives zero result
Declare @FromDate datetime = Getdate()
, @ToDate datetime = Getdate();

it gives the correct result for current date when I changed it to Getdate()-1

[jeffw8713]

Okay - a couple of things here...

If you remove the filtering altogether - you would have rows from dbo.USERS where the values in the columns from dbo.EVENT_LOG are NULL. The reason for that is because those users have not attended an event.

When you add the filtering in the WHERE clause - the value el.dtDateTime for those rows where the user did not attend an event and thus are NULL are eliminated from the results because NULL is not greater than or less than your date range.

Moving the filtering to the JOIN says - find all events is the specified range - and associate those rows with the users. Now - any user that attended an event in that range will have a value and any user that did not attend an event will not have a value.

With that - we can count all users...and count only those users that attended an event.

Using GETDATE() returns the current date and time. Essentially you are asking the system to return data on events that have not occurred yet, that is from current time forward. Changing to GETDATE() - 1 gives you yesterday at this time - and if run at 8:00am will pull everything from 8am yesterday to today at 8am. When run at 5pm - it will pull everything from 5pm yesterday to 5pm today.

[tanehome]

thanks a lot for clarification, I got the point
I'll made a small modification to remove the "-1" and returns only date, it works for now and
will try it tomorrow also to be sure.

CONVERT(DATE, GETDATE())
or
CAST( GETDATE() AS Date )

[jeffw8713]

If you are using something like this:

Declare @FromDate datetime = cast(getdate() as date)
      , @ToDate datetime = cast(getdate() as date);

What you end up with is:

el.dtDateTime >= '2021-01-24 00:00:00.000'
And el.dtDateTime < '2021-01-25 00:00:00.000'

This will pull everything from the beginning of 'today' to current time (or end of today for future dates - which doesn't make sense because the events haven't occurred yet).

To get the start of yesterday - and to filter on only yesterday you can do this:

Declare @FromDate datetime = dateadd(day, datediff(day, 0, getdate()) - 1, 0);  --start of yesterday

el.dtDateTime >= @FromDate
And el.dtDateTime < dateadd(day, 1, @FromDate)  --start of today, not inclusive

[harishgg1]

---

Your Comments
thanks a lot for both of you it works great now
I didn't saw the extra "(" at all, I've to check my eyes

happens to everyone all the time .

. how to make sure it does not happen

lot of people for whom things are critical (very important ) will be finding ways for that not to happen

there is a concept called ..
Learning how to see .. by Leonardo Da Vinci