Select new columns based on group order

Question

I went through many questions on this site but wasn’t able to find solution. I’ve got a table:

Date    GroupID CHANNEL
24/02/2015  1   A
26/02/2015  1   B
27/02/2015  1   C
21/03/2015  2   D
20/02/2015  3   E
25/02/2015  3   D
28/02/2015  4   C
04/03/2015  5   B
05/03/2015  5   E
10/03/2015  5   D
11/03/2015  5   A
14/03/2015  5   C
23/03/2015  5   F
28/03/2015  6   E

Channel are limited to ‘A’,’B’,’C’,’D’,’E’,’F’. There are quite a lot of rows in it with different GROUPIDs.

I need to get this table:

Date    GroupID Channel isFirst isLast  Channelsingroup Daysbeforelast
24/02/2015  1       A   TRUE    FALSE   3                   3
26/02/2015  1       B   FALSE   FALSE   3                   1
27/02/2015  1       C   FALSE   TRUE    3                   0
21/03/2015  2       D   TRUE    TRUE    1                   0
20/02/2015  3       E   TRUE    FALSE   2                   5
25/02/2015  3       D   FALSE   FALSE   2                   0
28/02/2015  4       C   TRUE    TRUE    1                   0
04/03/2015  5       B   TRUE    FALSE   6                   19
05/03/2015  5       E   FALSE   FALSE   6                   18
10/03/2015  5       D   FALSE   FALSE   6                   13
11/03/2015  5       A   FALSE   FALSE   6                   12
14/03/2015  5       C   FALSE   FALSE   6                   9
23/03/2015  5       F   FALSE   FALSE   6                   0
28/03/2015  6       E   TRUE    TRUE    1                   0

IsFirst = TRUE when the Channel is the first in the group of rows with the same ID sorted by Time; otherwise FALSE.

IsLast = TRUE when the Channel is the last in the group of rows with the same GroupID sorted by Time; otherwise FALSE.

Channelsingroup - number of rows in the same group (with the same GroupID)

Daysbeforelast - datediff in days between latest row in the group and the current one.

I have no access to create or update table, only to select.

I hope the above data make sense, any questions please let me know.

Answer 1

One solution could be to use windowed aggregate functions:

select 
    *, 
    case when date = MIN(date) over (partition by groupid order by groupid) then 'TRUE' else 'FALSE' end isFirst,
    case when date = MAX(date) over (partition by groupid order by groupid) then 'TRUE' else 'FALSE' end isLast,
    count(*) over (partition by groupid order by groupid) Channelsingroup,
    datediff(day,date,MAX(date) over (partition by groupid order by groupid)) Daysbeforelast
from your_table

Sample SQL Fiddle

Answer 2

You can use ROW_NUMBER to get the isFirst and isLast column and COUNT(*) OVER() for the ChannelsInGroup. Additionally, you can use CROSS APPLY to compute for the DaysBeforeLast:

SQL Fiddle

WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY GroupID ORDER BY [Date]),
        CC = COUNT(*) OVER(PARTITION BY GroupID)
    FROM TestData
)
SELECT
    c.[Date],
    c.GroupID,
    c.Channel,
    isFirst         = CASE WHEN c.RN = 1 THEN 'TRUE' ELSE 'FALSE' END,
    isLast          = CASE WHEN c.RN = c.CC THEN 'TRUE' ELSE 'FALSE' END,
    ChannelsInGroup = c.CC,
    DaysBeforeLast  = DATEDIFF(DAY, c.[Date], x.LastDate)
FROM Cte c
CROSS APPLY(
    SELECT TOP 1 [Date]
    FROM Cte
    WHERE 
        RN = CC
        AND GroupID = c.GroupID
    ORDER BY [Date] DESC
)x(LastDate)

Result

|       Date | GroupID | Channel | isFirst | isLast | ChannelsInGroup | DaysBeforeLast |
|------------|---------|---------|---------|--------|-----------------|----------------|
| 2015-02-24 |       1 |       A |    TRUE |  FALSE |               3 |              3 |
| 2015-02-26 |       1 |       B |   FALSE |  FALSE |               3 |              1 |
| 2015-02-27 |       1 |       C |   FALSE |   TRUE |               3 |              0 |
| 2015-03-21 |       2 |       D |    TRUE |   TRUE |               1 |              0 |
| 2015-02-20 |       3 |       E |    TRUE |  FALSE |               2 |              5 |
| 2015-02-25 |       3 |       D |   FALSE |   TRUE |               2 |              0 |
| 2015-02-28 |       4 |       C |    TRUE |   TRUE |               1 |              0 |
| 2015-03-04 |       5 |       B |    TRUE |  FALSE |               6 |             19 |
| 2015-03-05 |       5 |       E |   FALSE |  FALSE |               6 |             18 |
| 2015-03-10 |       5 |       D |   FALSE |  FALSE |               6 |             13 |
| 2015-03-11 |       5 |       A |   FALSE |  FALSE |               6 |             12 |
| 2015-03-14 |       5 |       C |   FALSE |  FALSE |               6 |              9 |
| 2015-03-23 |       5 |       F |   FALSE |   TRUE |               6 |              0 |
| 2015-03-28 |       6 |       E |    TRUE |   TRUE |               1 |              0 |