delete a record

Question

Good Morning All,

I need to delete the record "A" only when there is "A" and "U"
for the same employee , same date

table

CREATE TABLE [dbo].pay_local ON [PRIMARY]

data
SET IDENTITY_INSERT [dbo].[pay_local] ON

INSERT [dbo].[pay_local] ([shift_id], [emp_id], [action_type], [action_group], [shift_date], [shift_time]) VALUES (2, N'00010', N'A', N'Time In ', N'20200810', N'15:43:41')
INSERT [dbo].[pay_local] ([shift_id], [emp_id], [action_type], [action_group], [shift_date], [shift_time]) VALUES (3, N'00015', N'A', N'Time In ', N'20200810', N'15:57:52')
INSERT [dbo].[pay_local] ([shift_id], [emp_id], [action_type], [action_group], [shift_date], [shift_time]) VALUES (4, N'00024', N'A', N'Time In ', N'20200810', N'07:18:01')
INSERT [dbo].[pay_local] ([shift_id], [emp_id], [action_type], [action_group], [shift_date], [shift_time]) VALUES (5, N'00030', N'A', N'Time In ', N'20200810', N'06:16:07')
INSERT [dbo].[pay_local] ([shift_id], [emp_id], [action_type], [action_group], [shift_date], [shift_time]) VALUES (6, N'00031', N'A', N'Time In ', N'20200810', N'04:19:41')
INSERT [dbo].[pay_local] ([shift_id], [emp_id], [action_type], [action_group], [shift_date], [shift_time]) VALUES (7, N'00031', N'U', N'Time In ', N'20200810', N'04:21:00')
INSERT [dbo].[pay_local] ([shift_id], [emp_id], [action_type], [action_group], [shift_date], [shift_time]) VALUES (8, N'00052', N'A', N'Time In ', N'20200810', N'07:16:13')
SET IDENTITY_INSERT [dbo].[pay_local] OFF

Answer 1

Hi Cholotron,

Please try the following T-SQL statement:

DELETE FROM A
FROM dbo.pay_local AS A
WHERE A.action_type = 'A'
 AND EXISTS(
 SELECT 1 FROM dbo.pay_local AS B
 WHERE B.emp_id = A.emp_id
 AND B.shift_date = A.shift_date
 AND B.action_type = 'U');

-- test
SELECT * FROM dbo.pay_local;

Answer 2

CREATE TABLE [dbo].pay_local ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[pay_local] ON

INSERT [dbo].[pay_local] ([shift_id], [emp_id], [action_type], [action_group], [shift_date], [shift_time]) VALUES (2, N'00010', N'A', N'Time In ', N'20200810', N'15:43:41')
, (3, N'00015', N'A', N'Time In ', N'20200810', N'15:57:52')
, (4, N'00024', N'A', N'Time In ', N'20200810', N'07:18:01')
, (5, N'00030', N'A', N'Time In ', N'20200810', N'06:16:07')
,(7, N'00031', N'U', N'Time In ', N'20200810', N'04:21:00')
,(8, N'00052', N'A', N'Time In ', N'20200810', N'07:16:13')
SET IDENTITY_INSERT [dbo].[pay_local] OFF

--select * from [dbo].[pay_local]

delete from pl1
from [dbo].[pay_local] pl1
where pl1.action_type='A' and
exists( select 1 from [dbo].[pay_local] pl2
where pl2.action_type='U'
and pl1.emp_id=pl2.emp_id and pl1.[shift_date]=pl2.[shift_date] )

--select * from [dbo].[pay_local]

drop TABLE [dbo].[pay_local]

Answer 3

;WITH CTE_Action_Type_A AS (
    SELECT * FROM [pay_local] WHERE [action_type] = 'A'
),
CTE_Action_Type_U AS (
    SELECT * FROM [pay_local] WHERE [action_type] = 'U'
)

DELETE a
FROM CTE_Action_Type_A AS a
INNER JOIN CTE_Action_Type_U AS u ON a.[emp_id] = u.[emp_id] AND a.[shift_date] = u.[shift_date];

SELECT * FROM [pay_local];

Answer 4

You have huge problems with your design. Your action group is metadata that defines the date and time columns. You seem to think that the proprietary table property IDENTITY can ever be, a key area valid relational model. You don't know that we have temporal data types in SQL. The use of two columns where you have it is a design flaw so bad that it has a name – attribute splitting. Using those strings for date and time is so, so COBOL. It's also weird that a shift never ends; where is that terminal value that would make an ISO half open interval model of time? I think you might want to read the book on temporal queries in SQL by Rick Snodgrass. It's available as a free PDF download from the University of Arizona website.

Finally, you do not know the difference between a row and a record. Let's go ahead and fix your table and see that your problem will probably go away with the correct DDL.

CREATE TABLE Pay_Local
(emp_id CHAR (5) NOT NULL,
action_type CHAR (1) NOT NULL
CHECK (action_type IN ('A', 'U', NULL),
shift_start_timestamp DATETIME2(0) NOT NULL,
shift_end_timestamp DATETIME2(0), -- null means still in processing
CHECK (shift_start_timestamp < shift_end_timestamp),
PRIMARY KEY (emp_id, shift_start_timestamp)
);

INSERT INTO Pay_Local
VALUES
('00010', 'A', '2020-08-10 15:43:41', NULL),
('00015', 'A', '2020-08-10 15:57:52', NULL),
('00024', 'A', '2020-08-10 07:18:01', NULL),
('00030', 'A', '2020-08-10 06:16:07', NULL),
('00031', 'A', '2020-08-10 04:19:41', NULL),
('00031', 'U', '2020-08-10 04:21:00', NULL),
('00052', 'A', '2020-08-10 07:16:13', NULL);

> I need to delete the record [sic] "A" only when there is "A" and "U"

for the same employee, same date <<

DELETE FROM Pay_Local AS PL
WHERE action_type = 'A"
AND EXISTS
(SELECT *
FROM Pay_Local AS PL1
WHERE PL1.emp_id = PL.emp_id
AND CAST (PL1.shift_start_timestamp AS DATE)
= CAST (PL.shift_start_timestamp AS DATE)
AND PL1.action_type = 'U');