January 21, 2021 at 3:16 pm
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 gives error
declare @FromDate varchar(10) ='21/01/2021'
declare @ToDate varchar(10) ='21/01/2021'
SELECTdbo.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 JOINdbo.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
January 21, 2021 at 3:22 pm
You have >1000 points and therefore should know by now – please provide DDL and sample data to match your desired results.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 21, 2021 at 10:50 pm
Answered on SQL Team: https://forums.sqlteam.com/t/summary-query-for-number-of-attendees-out-of-all/18890/6
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply