T-SQL 2016 get max data row without group by

Question

Hi there

I need help with T-SQL 2016 I have below table.

MyTable
Ticket ID Ticket Date Ticket Date-time Ticket Status Ticket Type TheFlag
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

I am after something below without GROUP BY. New column 'MaxFlagValueInDate' should show MAX value on the TheFalg from Ticket Date or Ticket Date-Time so that one-day can have 0 or 1

MyTable
Ticket ID Ticket Date Ticket Date-time Ticket Status Ticket Type TheFlag MaxFlagValueInDate
T123 2020-08-17 2020-08-17 17:30:00 OPEN HZ 0 0
T134 2020-08-17 2020-08-17 18:30:00 OPEN HZ 0 NULL
T456 2020-08-17 2020-08-17 18:45:00 OPEN INC 1 1
T789 2020-08-17 2020-08-17 19:45:00 OPEN INC 1 NULL
T900 2020-08-17 2020-08-17 21:45:05 OPEN INC 1 NULL
T624 2020-08-18 2020-08-18 17:30:00 OPEN HZ 0 0
T735 2020-08-18 2020-08-18 18:30:00 OPEN HZ 0 NULL
T856 2020-08-18 2020-08-18 18:45:00 OPEN INC 1 1
T986 2020-08-18 2020-08-18 19:45:00 FTI INC 0 NULL
T222 2020-08-18 2020-08-18 19:47:00 FTI INC 0 NULL
T110 2020-08-18 2020-08-18 21:45:05 OPEN INC 1 NULL

Thanks

Answer 1

Your sample data is not really readable.
Please post table design as DDL, some sample data as DML statement and the expected result / the logic for it.

Answer 2

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.

Answer 3

If you want to have the corresponding NULLs, then check this query:

select *,
    case row_number() over (partition by [Ticket Date], TheFlag order by [Ticket Date-time])
    when 1 then max(TheFlag) over (partition by [Ticket Date] order by [Ticket Date-time]) end as MaxFlagValueInDate
from MyTable
order by [Ticket Date-time]