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