CODEWITHSUNDEEP
sqlsql-server
Sameep Baxi
Sameep Baxi

Reputation: 111

Selecting the second (middle) row between MIN & MAX values in SQL Server

I have the following table:

TicketNumber           CallDate
--------------------------------------------
101                    10/09/2015 3:15:43 PM
101                    10/09/2015 3:45:43 PM
101                    11/19/2015 2:23:09 PM

I want to select the min date, the middle date and the max date. It is easy to get the first and last dates using MIN and MAX. But how to SELECT (get) the second date?

SELECT 
    TicketNumber
    , MIN(CallDate) CallDate1
    , MAX(CallDate) CallDate3
    , COUNT(TicketNumber) [Count]
FROM Table1
WHERE -(conditions)-
GROUP BY TicketNumber
HAVING COUNT(TicketNumber)=3

Between MIN & MAX dates in the SELECT statement I want the second row date.

The expected output should be:

TicketNumber   CallDate1              CallDate2             CallDate3                Count
------------------------------------------------------------------------------------------
101            10/9/2015 3:15:43 PM  10/9/2015 3:45:43 PM   11/19/2015 2:23:09 PM    3

Upvotes: 1

Views: 831

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Here is one possible variant. At first number and count all rows, then filter only those TicketNumbers that have three tickets and PIVOT result.

SQL Fiddle

Sample data

DECLARE @Tickets TABLE (TicketNumber int, CallDate datetime2(0));

INSERT INTO @Tickets (TicketNumber, CallDate) VALUES
(101, '2015-10-09 03:15:43'),
(101, '2015-10-09 03:45:43'),
(101, '2015-11-19 02:23:09'),

(102, '2015-11-20 02:23:09'),
(102, '2015-11-19 02:23:09'),
(102, '2015-11-21 02:23:09'),

(103, '2015-11-10 02:23:09'),
(103, '2015-11-19 02:23:09'),

(104, '2015-11-11 02:23:09'),
(104, '2015-11-01 02:23:09'),
(104, '2015-11-21 02:23:09'),
(104, '2015-11-30 02:23:09');

Query

WITH
CTE
AS
(
    SELECT
        TicketNumber
        ,CallDate
        ,ROW_NUMBER() OVER (PARTITION BY TicketNumber ORDER BY CallDate) AS rn
        ,COUNT(*) OVER (PARTITION BY TicketNumber) AS cnt
    FROM
        @Tickets AS T
)
SELECT
    P.TicketNumber
    ,[1] AS CallDate1
    ,[2] AS CallDate2
    ,[3] AS CallDate3
    ,cnt
FROM
    CTE
    PIVOT (MIN(CTE.CallDate) FOR rn IN ([1], [2], [3])) AS P
WHERE cnt = 3
ORDER BY P.TicketNumber;

Result

+--------------+---------------------+---------------------+---------------------+-----+
| TicketNumber |      CallDate1      |      CallDate2      |      CallDate3      | cnt |
+--------------+---------------------+---------------------+---------------------+-----+
|          101 | 2015-10-09 03:15:43 | 2015-10-09 03:45:43 | 2015-11-19 02:23:09 |   3 |
|          102 | 2015-11-19 02:23:09 | 2015-11-20 02:23:09 | 2015-11-21 02:23:09 |   3 |
+--------------+---------------------+---------------------+---------------------+-----+

Upvotes: 2

Samir Selia
Samir Selia

Reputation: 7065

This can be achieved using table JOINS.

SELECT t1.TicketNumber, t2.CallDate1, t1.CallDate AS CallDate2, t2.CallDate3, t2.Count
FROM tickets AS t1 
JOIN (
     SELECT TicketNumber, MIN(CallDate) AS CallDate1, MAX(CallDate) AS CallDate3,
     COUNT(TicketNumber) AS Count
     FROM tickets
     GROUP BY TicketNumber
     HAVING COUNT(TicketNumber)=3
    ) AS t2
ON t1.TicketNumber = t2.TicketNumber
WHERE t1.CallDate > t2.CallDate1
AND t1.CallDate < t2.CallDate3

Working Fiddle

Upvotes: 0

Related Questions