delete a record

Cholotron 161 Reputation points
2020-08-17T15:37:13.55+00:00

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 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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 24,936 Reputation points
    2020-08-17T16:45:23.537+00:00

    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;

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jingyang Li 5,891 Reputation points
    2020-08-17T16:41:05.277+00:00

    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]

    0 comments
  2. Guoxiong 8,126 Reputation points
    2020-08-17T18:53:05.89+00:00 
    ;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];
  3. Joe Celko 16 Reputation points
    2020-12-24T17:18:59.217+00:00

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

    0 comments