question

KM-3481 avatar image
0 Votes"
KM-3481 asked EchoLiu-msft commented

T-SQL 2016 get max data row without group by

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @KM-3481

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:
131830-image.png


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.



image.png (25.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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]


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.