Hi

I think the following will do the trick for you

with adBrandPostion AS (

select AdDate, AdTime, FK_StationId, brandflag

,COUNT(*) OVER (PARTITION BY BrandFlag, FK_StationID, AdDate) NumAds

,ROW_NUMBER() OVER (PARTITION BY BrandFlag, FK_StationID, AdDate ORDER BY AdTime) AdBrandPosition

FROM dbo.testtable

)

-- Determine time from previous advert in seconds

,TimeToPrevAd AS (

SELECT a.AdDate, a.AdTime, a.FK_StationId, a.brandflag, a.NumAds, a.AdBrandPosition

,DATEDIFF(s, b.AdTime, a.adTime) TimeToPrevAd

FROM adBrandPostion a

LEFT OUTER JOIN adBrandPostion b

ON a.AdBrandPosition = b.AdBrandPosition + 1

and a.AdDate = b.AdDate

and a.FK_StationId = b.FK_StationId

and a.brandflag = b.brandflag

)

-- Mark the beginnings of each ad group based on time difference

,adBreaks AS (

SELECT *

,CASE WHEN TimeToPrevAd > 120 THEN 0 ELSE 1 END PrevAdInGroup -- change the seconds in here to suit

FROM TimeToPrevAd

)

-- number the groups

,adGroups AS (

SELECT *

,ABS((AdBrandPosition * PrevAdInGroup) - ROW_NUMBER() OVER (PARTITION BY BrandFlag, FK_StationID, AdDate, PrevAdInGroup ORDER BY AdTime)) + 1 g

FROM adBreaks

)

SELECT AdDate, AdTime, FK_StationId, brandflag

-- Position in the group

,cast(ROW_NUMBER() OVER (PARTITION BY BrandFlag, FK_StationID, AdDate, g ORDER BY AdTime) as varchar(10)) + '/'

-- Number in the group

+ cast(COUNT(*) OVER (PARTITION BY BrandFlag, FK_StationID, AdDate, g) as varchar(10)) + ' of '

-- Group Number

+ cast(g as varchar(10)) desc1

FROM adGroups

ORDER BY fk_stationid, brandflag, addate, adtime;

The CTE can probably be compressed up into a smaller query, but I have left it as is to show workings