RE: Ranking Based of Advert Breaks

SSCrazy

Points: 2698

July 22, 2013 at 5:07 am

Hi MickyT,

Thanks for your help the other time.

I want to bring something to your notice, it has to do with the grouping of the advert breaks

I noticed that actually the data is being grouped based on programs(i.e brandflag) but i also noticed that the advert break is not looking at time variations for example. Looking at the data below

CREATE TABLE [dbo].[TestTable3](

[AdDate] [datetime] NULL,

[AdTime] [nvarchar](8) NULL,

[FK_StationId] [nvarchar](5) NULL,

[brandflag] [nvarchar](5) NULL,

[Position] [varchar](35) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'07:56:39', N'A60', N'T294', N'1/1 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'08:24:14', N'A60', N'T294', N'1/1 of 2')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'09:32:15', N'A60', N'T408', N'1/1 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'09:44:30', N'A60', N'T408', N'1/1 of 2')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'10:14:00', N'A60', N'T502', N'1/1 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'10:30:50', N'A60', N'T502', N'1/1 of 2')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'18:57:37', N'A60', N'T1142', N'1/1 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'19:57:32', N'A60', N'T3196', N'1/1 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'20:09:11', N'A60', N'T43', N'1/2 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'20:10:09', N'A60', N'T43', N'2/2 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'20:20:10', N'A60', N'T43', N'1/2 of 2')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'20:21:10', N'A60', N'T43', N'2/2 of 2')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'20:28:34', N'A60', N'T43', N'1/1 of 3')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'20:59:18', N'A60', N'T0', N'1/1 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'21:11:02', N'A60', N'T508', N'1/1 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'21:48:02', N'A60', N'T508', N'1/1 of 2')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'21:53:46', N'A60', N'T508', N'1/5 of 3')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'21:54:16', N'A60', N'T508', N'2/5 of 3')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'21:54:49', N'A60', N'T508', N'3/5 of 3')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'21:55:17', N'A60', N'T508', N'4/5 of 3')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'21:56:49', N'A60', N'T508', N'5/5 of 3')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:13:14', N'A60', N'T43', N'1/7 of 4')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:13:31', N'A60', N'T43', N'2/7 of 4')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:14:35', N'A60', N'T43', N'3/7 of 4')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:15:32', N'A60', N'T43', N'4/7 of 4')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:16:05', N'A60', N'T43', N'5/7 of 4')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:16:38', N'A60', N'T43', N'6/7 of 4')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:17:41', N'A60', N'T43', N'7/7 of 4')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:29:35', N'A60', N'T43', N'1/8 of 5')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:30:05', N'A60', N'T43', N'2/8 of 5')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:30:35', N'A60', N'T43', N'3/8 of 5')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:31:05', N'A60', N'T43', N'4/8 of 5')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:31:20', N'A60', N'T43', N'5/8 of 5')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:32:21', N'A60', N'T43', N'6/8 of 5')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:32:51', N'A60', N'T43', N'7/8 of 5')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:33:38', N'A60', N'T43', N'8/8 of 5')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:39:48', N'A60', N'T43', N'1/7 of 6')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:40:22', N'A60', N'T43', N'2/7 of 6')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:40:49', N'A60', N'T43', N'3/7 of 6')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:41:33', N'A60', N'T43', N'4/7 of 6')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:42:06', N'A60', N'T43', N'5/7 of 6')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:42:35', N'A60', N'T43', N'6/7 of 6')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:43:32', N'A60', N'T43', N'7/7 of 6')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:49:41', N'A60', N'T43', N'1/2 of 7')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:49:45', N'A60', N'T43', N'2/2 of 7')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'22:57:06', N'A60', N'T0', N'1/1 of 2')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'23:17:00', N'A60', N'T1053', N'1/1 of 1')

INSERT [dbo].[TestTable3] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A1E400000000 AS DateTime), N'23:52:35', N'A60', N'T0', N'1/1 of 3')

The other T43 branflag should also have breaks like 1, 2, and 3 but its having breaks like 4, 5, and 6 reason being that its counting from previous timing, with previous breaks, i think every break should be unique to the time cluster, please what can i do to this.

Thanks