Hi @K M ,
Welcome to the microsoft TSQL Q&A fourm!
For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data.
Please check:
CREATE TABLE #test
(TicketID VARCHAR(25), TicketDate DATE,
[TicketDate-time] DATETIME, TicketStatus VARCHAR(25),
TicketType VARCHAR(15),TheFlag INT)
INSERT INTO #test VALUES
('T123','2020-08-17','2020-08-17 17:30:00','OPEN','HZ',0),
('T134','2020-08-17','2020-08-17 18:30:00','OPEN','HZ',0),
('T456','2020-08-17','2020-08-17 18:45:00','OPEN','INC',1),
('T789','2020-08-17','2020-08-17 19:45:00','OPEN' ,'INC',1),
('T900','2020-08-17','2020-08-17 21:45:05','OPEN' ,'INC',1),
('T624','2020-08-18','2020-08-18 17:30:00','OPEN','HZ',0),
('T735','2020-08-18','2020-08-18 18:30:00','OPEN','HZ',0),
('T856','2020-08-18','2020-08-18 18:45:00','OPEN','INC',1),
('T986','2020-08-18','2020-08-18 19:45:00','FTI','INC',0),
('T222','2020-08-18','2020-08-18 19:47:00','FTI','INC',0),
('T110','2020-08-18','2020-08-18 21:45:05','OPEN','INC',1)
;WITH cte as
(SELECT *,
MIN([TicketDate-time]) OVER(PARTITION BY TicketType,TicketDate) mintime,
MAX(TheFlag) OVER(PARTITION BY TicketType,TicketDate) maxFlag
FROM #test)
SELECT TicketID,TicketDate,[TicketDate-time],
TicketStatus,TicketType,TheFlag,
CASE WHEN [TicketDate-time]=mintime THEN maxFlag
END MaxFlagValueInDate
FROM cte
ORDER BY TicketDate,[TicketDate-time]
Output:
If you have any question, please feel free to let me know.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.