Select new columns based on bulk order

Question

I looked through a lot of questions on this site, but could not find a solution. I have a table:

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

Channels are limited to 'A, B, C, D, E, F. It has quite a few lines with different GROUPIDs.

I need to get this table:

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

IsFirst = TRUE when the channel is the first in a row group with the same identifier, sorted by time; otherwise FALSE.

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

Channelsingroup - the number of lines in one group (with the same group identifier)

Daysbeforelast - dated in days between the last line in the group and the current.

I do not have access to create or update the table, just for selection.

I hope this data makes sense; any questions, please let me know.

Answer 1

One solution may be to use window 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 

SQL script example

Answer 2

You can use ROW_NUMBER to get the isFirst and isLast and COUNT(*) OVER() column for ChannelsInGroup . Alternatively, you can use CROSS APPLY to calculate for 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 | 

Answer 3

try it.

 select DATE_1 ,GroupID,CHANNEL , case (select top 1 test.CHANNEL from test where test.GroupID = outer1.GroupID order by test.CHANNEL) when outer1.CHANNEL then 'true' else 'false' end as isFirst , case (select top 1 test.CHANNEL from test where test.GroupID = outer1.GroupID order by test.CHANNEL desc) when CHANNEL then 'true' else 'false' end as isLast , (select count(*) from test where test.GroupID = outer1.GroupID) Channelsingroup , (select DATEDIFF(DAY,outer1.DATE_1,MAX(test.DATE_1)) from test where test.GroupID = outer1.GroupID) from test outer1 

Answer 4

  with cte as (select Date,GroupID ,Channel,row_number() over (partition by groupid order by groupid) as first from table1) select Date,GroupID, Channel, case when first=1 then 'true' else 'false' end as isFirst, case when isfirst=b.Channelsingroup then 'true' else 'false' end as isLast b.Channelsingroup, datediff(dd,getdate(),Daysbeforelast) as Daysbeforelast from cte a inner join (select count(*) as Channelsingroup ,max(date) as Daysbeforelast from table1 group by groupid) as b on a.groupid=b,groupid