T-SQL 2016 get max data row without group by

K M 1 Reputation point
2021-09-14T04:36:02.427+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,581 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 42,561 Reputation points
    2021-09-14T06:21:50.447+00:00

    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.

    0 comments No comments

  2. EchoLiu-MSFT 14,576 Reputation points
    2021-09-14T06:36:29.037+00:00

    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:
    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.

    0 comments No comments

  3. Viorel 113.9K Reputation points
    2021-09-14T08:07:54.937+00:00

    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]
    
    0 comments No comments