Query to get the fully present employees from a table

Transact-SQL
1

Hi Members,

I need an help on a TSQL query. I have table where the employees attendance are being recorded. there are two category of shift one is a single shift and other one is a double shift (where the employee's required to work in two splits [morning and evening on the same day]). my requirement is to get all the employees who are fully present for the selected period.

my table is as below
CREATE TABLE [dbo].[TA_Records](
[SeqNo] [bigint] IDENTITY(1,1) NOT NULL,
[AttnDate] [datetime] NOT NULL,
[EmployeeId] nvarchar NULL,
[AttnStatus] nchar NULL,
CONSTRAINT [PK_TA_Records] PRIMARY KEY CLUSTERED
(
[SeqNo] ASC
)) ON [PRIMARY]
GO

Records
INSERT INTO [dbo].[TA_Records] ([AttnDate],[EmployeeCode], [AttnStatus]) VALUES ('01-DEC-2021', '10001', 'PR')
INSERT INTO [dbo].[TA_Records] ([AttnDate],[EmployeeCode], [AttnStatus]) VALUES ('01-DEC-2021', '10001', 'AB')
INSERT INTO [dbo].[TA_Records] ([AttnDate],[EmployeeCode], [AttnStatus]) VALUES ('01-DEC-2021', '10002', 'PR')
INSERT INTO [dbo].[TA_Records] ([AttnDate],[EmployeeCode], [AttnStatus]) VALUES ('02-DEC-2021', '10001', 'PR')
INSERT INTO [dbo].[TA_Records] ([AttnDate],[EmployeeCode], [AttnStatus]) VALUES ('02-DEC-2021', '10001', 'PR')
INSERT INTO [dbo].[TA_Records] ([AttnDate],[EmployeeCode], [AttnStatus]) VALUES ('02-DEC-2021', '10002', 'PR')
INSERT INTO [dbo].[TA_Records] ([AttnDate],[EmployeeCode], [AttnStatus]) VALUES ('03-DEC-2021', '10002', 'AB')

Expected Output

'01-Dec-2021', '10002'
'02-Dec-2021', '10001'
'02-Dec-2021', '10002'

Please help

2

hope this helps

your sample data is not correct .. your create table statement is not correct

select  
     [EmployeeId]
   , [AttnDate]
   , sum(case when [AttnStatus] = 'PR'then 1 else 0 end)  
from 
    dbo.TA_Records 
group by 
    [EmployeeId],[AttnDate]
having 
  sum(case when [AttnStatus] = 'PR'then 1 else 0 end)  > 1
3 
SELECT AttnDate, EmployeeId 
FROM dbo.TA_Records
GROUP BY AttnDate, EmployeeId 
HAVING MAX(CASE WHEN AttnStatus = 'AB' THEN 1 ELSE 0 END) = 0
ORDER BY AttnDate, EmployeeId