SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Report
(Id INT, Reference VARCHAR(30));

INSERT INTO Report
VALUES
(1, 'Location (11/8/22)'),
(2, 'Timesheet (11/10/22)'),
(3, 'TESTING (12/09/22)'),
(4, 'Incorrect Payment (9/10/22)'),
(5, 'Employee (11/9/22)'),
(6, 'Authorization'),
(7, 'Inactive Client'),
(8, 'Active Client (07/22/2022)'),
(9, 'TESTING (09/09/22)'),
(10, 'Timesheet')
10 rows affected

WITH split_ref As(
  SELECT R.Id, R.Reference, SP.value, SP.ordinal
  FROM Report R
  CROSS APPLY STRING_SPLIT(Reference, '(', 1) AS SP
)
SELECT Id, Reference,
       MAX(CASE ordinal WHEN 1 THEN value END) AS Type,
       MAX(CASE ordinal WHEN 2 THEN TRY_CAST(REPLACE(value, ')', '') AS DATE) END) AS [Date] 
FROM split_ref
GROUP BY Id, Reference
ORDER BY Id


Id
Reference
Type
Date


1
Location (11/8/22)
Location
2022-11-08

2
Timesheet (11/10/22)
Timesheet
2022-11-10

3
TESTING (12/09/22)
TESTING
2022-12-09

4
Incorrect Payment (9/10/22)
Incorrect Payment
2022-09-10

5
Employee (11/9/22)
Employee
2022-11-09

6
Authorization
Authorization
null

7
Inactive Client
Inactive Client
null

8
Active Client (07/22/2022)
Active Client
2022-07-22

9
TESTING (09/09/22)
TESTING
2022-09-09

10
Timesheet
Timesheet
null


Warning: Null value is eliminated by an aggregate or other SET operation.


WITH split_ref As(
  SELECT R.Id, R.Reference, SP.value, 
    CASE WHEN SP.value LIKE '%)' THEN 2 ELSE 1 END AS ordinal
  FROM Report R
  CROSS APPLY STRING_SPLIT(Reference, '(') AS SP
)
SELECT Id, Reference,
       MAX(CASE ordinal WHEN 1 THEN value END) AS Type,
       MAX(CASE ordinal WHEN 2 THEN TRY_CAST(REPLACE(value, ')', '') AS DATE) END) AS [Date] 
FROM split_ref
GROUP BY Id, Reference
ORDER BY Id


Id
Reference
Type
Date


1
Location (11/8/22)
Location
2022-11-08

2
Timesheet (11/10/22)
Timesheet
2022-11-10

3
TESTING (12/09/22)
TESTING
2022-12-09

4
Incorrect Payment (9/10/22)
Incorrect Payment
2022-09-10

5
Employee (11/9/22)
Employee
2022-11-09

6
Authorization
Authorization
null

7
Inactive Client
Inactive Client
null

8
Active Client (07/22/2022)
Active Client
2022-07-22

9
TESTING (09/09/22)
TESTING
2022-09-09

10
Timesheet
Timesheet
null


Warning: Null value is eliminated by an aggregate or other SET operation.

Id	Reference	Type	Date
1	Location (11/8/22)	Location	2022-11-08
2	Timesheet (11/10/22)	Timesheet	2022-11-10
3	TESTING (12/09/22)	TESTING	2022-12-09
4	Incorrect Payment (9/10/22)	Incorrect Payment	2022-09-10
5	Employee (11/9/22)	Employee	2022-11-09
6	Authorization	Authorization	null
7	Inactive Client	Inactive Client	null
8	Active Client (07/22/2022)	Active Client	2022-07-22
9	TESTING (09/09/22)	TESTING	2022-09-09
10	Timesheet	Timesheet	null