user2211290 user2211290 - 7 months ago 13
SQL Question

SQL Query-Group by and filters

I have the table as shown in below image.

enter image description here

I am trying to get the latest

status_id
, grouped by
team_id
and based on max of
created_date
.

--Latest status for each team based on created date
SELECT *
FROM ProductionHistory
WHERE created_Date IN (
SELECT MAX(created_Date)
FROM ProductionHistory
GROUP BY TeamID
)


That's fine, here is the result:

enter image description here

My questions are:


  1. How to ignore the
    assigned_to
    null then find latest record for team?

  2. When there is only one record with
    assigned_to
    null for team, let's consider that.



For example: for Team_id 5 there is only one record with assigned_to null, we don't have any other record for team_id 5 with assigned_to set some value.
So we have consider this.

For team id 3 query result should ignore the record 4 (though max date), query result should contain record 2.

Here is the final result I am expecting:

enter image description here

Answer

Try this:

DECLARE @t TABLE
    (
      TicketID INT ,
      TeamID INT ,
      StatusID INT ,
      created_Date DATETIME ,
      Assigned_to NVARCHAR(MAX)
    )

INSERT  @t
VALUES  ( 10000, 2, 7, '2015-03-26 01:48:00.000', 'W3ER45' ),
        ( 10000, 3, 7, '2015-03-26 05:48:00.000', 'YU67IO' ),
        ( 10000, 2, 9, '2015-03-26 03:48:00.000', 'HJKO98' ),
        ( 10000, 3, 9, '2015-03-27 03:48:00.000', NULL ),
        ( 10000, 4, 11, '2015-03-23 03:48:00.000', 'GHR67' ),
        ( 10000, 5, 11, '2015-03-27 02:12:56.910', NULL );


;
WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY TicketID, TeamID ORDER BY CASE
                                                              WHEN Assigned_to IS NULL
                                                              THEN 1
                                                              ELSE 0
                                                              END, created_Date DESC ) AS rn
               FROM     @t
             )
    SELECT  TicketID ,
            TeamID ,
            StatusID ,
            created_Date ,
            Assigned_to
    FROM    cte
    WHERE   rn = 1

Output:

TicketID    TeamID  StatusID    created_Date            Assigned_to
10000       2       9           2015-03-26 03:48:00.000 HJKO98
10000       3       7           2015-03-26 05:48:00.000 YU67IO
10000       4       11          2015-03-23 03:48:00.000 GHR67
10000       5       11          2015-03-27 02:12:56.910 NULL