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
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?
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.
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).
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
@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.
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
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.
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 )
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
