Hi muhammad12,
Use ISNULL function for checking he condition in CASE statement.
SELECT
ActionID,
eventname,
starttime,
CASE
WHEN ISNULL(endtime, '') = '' THEN
CONVERT(varchar(50), DATEADD(MINUTE, 1, CONVERT(time, starttime)))
ELSE endtime
END AS endtime
FROM events
INNER JOIN Assign ON Assign.eventid = events.eventid
INNER JOIN Department ON Department.DeptID = Assign.DeptID
INNER JOIN Action ON Assign.AssignID = Action.AssignID
INNER JOIN flights ON flights.flightid = Action.flightid
WHERE flights.flightno = 'PA-210'
AND flights.flightdate = '18-Sep-2023'
AND events.eventtype = 'Departure'
AND events.eventgroup = 'Time';
Example
DECLARE @events AS TABLE
(
ActionID INT,
eventname VARCHAR(50),
starttime VARCHAR(50),
endtime VARCHAR(50)
)
INSERT INTO @events VALUES (1,'Event 1','10:30','11:30')
INSERT INTO @events VALUES (2,'Event 2','12:30','')
SELECT
ActionID,
eventname,
starttime,
CASE
WHEN ISNULL(endtime, '') = '' THEN
CONVERT(VARCHAR(5), DATEADD(MINUTE, 1, CONVERT(TIME, starttime)))
ELSE endtime
END AS endtime
FROM @events
Output
| ActionID |
eventname |
starttime |
endtime |
| 1 |
Event 1 |
10:30 |
11:30 |
| 2 |
Event 2 |
12:30 |
12:31 |
