question

Cholotron-7052 avatar image
0 Votes"
Cholotron-7052 asked JoeCelko-6699 Suspended answered

delete a record


Good Morning All,

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

18016-temp.png



table

CREATE TABLE [dbo].[pay_local](
[shift_id] [int] IDENTITY(1,1) NOT NULL,
[emp_id] [char](5) NOT NULL,
[action_type] [char](1) NOT NULL,
[action_group] [char](8) NOT NULL,
[shift_date] [char](8) NOT NULL,
[shift_time] [char](8) NOT NULL,
CONSTRAINT [PK_pay_local] PRIMARY KEY CLUSTERED
(
[shift_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) 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

sql-server-transact-sql
temp.png (8.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered Cholotron-7052 commented

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;


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jingyang Li,
Sorry for stepping on your toes. We provided basically the same answer.

0 Votes 0 ·
JingyangLi avatar image
0 Votes"
JingyangLi answered

CREATE TABLE [dbo].[pay_local](
[shift_id] [int] IDENTITY(1,1) NOT NULL,
[emp_id] [char](5) NOT NULL,
[action_type] [char](1) NOT NULL,
[action_group] [char](8) NOT NULL,
[shift_date] [char](8) NOT NULL,
[shift_time] [char](8) NOT NULL,
CONSTRAINT [PK_pay_local] PRIMARY KEY CLUSTERED
(
[shift_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) 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]

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered Cholotron-7052 commented
 ;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];
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoeCelko-6699 avatar image
0 Votes"
JoeCelko-6699 Suspended answered

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');



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.