July 6, 2013 at 6:43 pm
Hi People,
This is a tough one for me. Let me start by explaining what i want to do then posting my code and data for you guys to give me ideas on how i can go about it.
I have this table that shows date, time, programme code(i.e. brandflag field) and station that an advert is being captured:
CREATE TABLE [dbo].[TestTable](
[AdDate] [datetime] NULL,
[AdTime] [nvarchar](8) NULL,
[FK_StationId] [nvarchar](5) NULL,
[BrandFLag] [nvarchar](5) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:36', N'A1', N'T79')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:20:00', N'A1', N'T1004')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:43:00', N'A1', N'T1004')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:58:22', N'A1', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:02:57', N'A1', N'T320')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:00', N'A1', N'T320')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:23', N'A1', N'T320')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:07:59', N'A1', N'T320')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:08:34', N'A1', N'T320')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:00', N'A1', N'T320')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:01', N'A1', N'T320')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:02', N'A1', N'T320')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'23:35:00', N'A1', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'06:30:16', N'A10', N'T79')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:00:00', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:30:05', N'A10', N'T79')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:31:30', N'A10', N'T79')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:32:27', N'A10', N'T79')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:45:45', N'A10', N'T79')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:10', N'A10', N'T79')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:48:12', N'A10', N'T79')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:01:18', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:02:18', N'A10', N'T1442')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:30:31', N'A10', N'T1443')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'10:01:00', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'15:58:05', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:03:00', N'A10', N'T202')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:02', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:14', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:00:13', N'A10', N'T1154')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:31:15', N'A10', N'T219')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:00:00', N'A10', N'T1154')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:30:37', N'A10', N'T231')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:58:40', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:59:47', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:00:00', N'A10', N'T375')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:17:46', N'A10', N'T1164')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:21:53', N'A10', N'T1164')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:31:42', N'A10', N'T1154')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:58:22', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:00:00', N'A10', N'T0')
INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:01:53', N'A10', N'T0')
And i have this code that shows the position of each advert per each programme (brandflag):
SELECT AdDate, AdTime, FK_StationId,brandflag, CAST(rank() over(partition by brandflag order by fk_stationid, addate,adtime) AS VARCHAR(10)) + '/ ' +
CAST(count(*) over(partition by fk_stationid,brandflag) AS VARCHAR(10)) as Position
FROM dbo.testtable
GROUP BY AdDate, AdTime, FK_StationId, brandflag
HAVING (AdDate = CONVERT(DATETIME, '2013-01-04 00:00:00', 102))
order by fk_stationid, addate, adtime
That gives me this result:
CREATE TABLE [dbo].[TestResult](
[AdDate] [datetime] NULL,
[AdTime] [nvarchar](8) NULL,
[FK_StationId] [nvarchar](5) NULL,
[brandflag] [nvarchar](5) NULL,
[Position] [varchar](22) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:36', N'A1', N'T79', N'1/ 1')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:20:00', N'A1', N'T1004', N'1/ 2')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:43:00', N'A1', N'T1004', N'2/ 2')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:58:22', N'A1', N'T0', N'1/ 2')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21', N'1/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21', N'2/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21', N'3/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21', N'4/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21', N'5/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21', N'6/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21', N'7/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21', N'8/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:02:57', N'A1', N'T320', N'1/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:00', N'A1', N'T320', N'2/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:23', N'A1', N'T320', N'3/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:07:59', N'A1', N'T320', N'4/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:08:34', N'A1', N'T320', N'5/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:00', N'A1', N'T320', N'6/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:01', N'A1', N'T320', N'7/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:02', N'A1', N'T320', N'8/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'23:35:00', N'A1', N'T0', N'2/ 2')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'06:30:16', N'A10', N'T79', N'2/ 7')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:00:00', N'A10', N'T0', N'3/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:30:05', N'A10', N'T79', N'3/ 7')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:31:30', N'A10', N'T79', N'4/ 7')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:32:27', N'A10', N'T79', N'5/ 7')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:45:45', N'A10', N'T79', N'6/ 7')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:10', N'A10', N'T79', N'7/ 7')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:48:12', N'A10', N'T79', N'8/ 7')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:01:18', N'A10', N'T0', N'4/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:02:18', N'A10', N'T1442', N'1/ 1')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:30:31', N'A10', N'T1443', N'1/ 1')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'10:01:00', N'A10', N'T0', N'5/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'15:58:05', N'A10', N'T0', N'6/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:03:00', N'A10', N'T202', N'1/ 1')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:02', N'A10', N'T0', N'7/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:14', N'A10', N'T0', N'8/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:00:13', N'A10', N'T1154', N'1/ 3')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:31:15', N'A10', N'T219', N'1/ 1')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:00:00', N'A10', N'T1154', N'2/ 3')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:30:37', N'A10', N'T231', N'1/ 1')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:58:40', N'A10', N'T0', N'9/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:59:47', N'A10', N'T0', N'10/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:00:00', N'A10', N'T375', N'1/ 1')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:17:46', N'A10', N'T1164', N'1/ 2')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:21:53', N'A10', N'T1164', N'2/ 2')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:31:42', N'A10', N'T1154', N'3/ 3')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:58:22', N'A10', N'T0', N'11/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:00:00', N'A10', N'T0', N'12/ 11')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:01:53', N'A10', N'T0', N'13/ 11')
Actually, the kind of result i'm looking for is something like breaking the results into separate advert breaks, e.g. when you have a programme, one way of determining the advert breaks in the programme is to look at the closeness of the adverts e.g: looking at the results below, you will notice that the first record i.e '1/8' and '2/8' are close together having the times '21:10:57' and '21:12:22', but for the rest of the timing they start from '21:30:51' through to '21:34:01', i would consider that as the 2nd advert break and consider the first set as the first advert break.
I would like to get a result displays in this format e.g. '1/2 of 1' meaning that for the first advert break, the first advert is
advert 1 of 2 of the first advert break i.e ('21:10:57') and ('21:12:22') is advert '2/2 of 1' i.e breaking the adverts based
on advert breaks.
Then the rest will follow suit e.g. ('21:30:51') will be '1/6 of 2' meaning advert 1 of six adverts in the 2nd break.
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21', N'1/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21', N'2/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21', N'3/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21', N'4/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21', N'5/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21', N'6/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21', N'7/ 8')
INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21', N'8/ 8')
Please if you need any clarification, i will be willing to explain again, i hope there is a solution to this.
Thanks very much.
Tim
July 7, 2013 at 3:08 pm
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
July 7, 2013 at 3:58 pm
Wow, MickyT, you are the man.
Thanks so much
it worked.
I'm very greatfull.
Thanks
Tim
July 7, 2013 at 4:24 pm
No problem ... I'm glad it helped. It based on the work of smarter people than me.
Essentially it came down to a gaps and islands problem. This article by Jeff Moden was the one was thinking of when I put it together, but there are other articles out there that also cover it.
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
July 22, 2013 at 1:51 pm
Hi Tim
I think this is what you are after. I have grouped together the sequential brands and use the brand groups rather than the brandflag
--select * from testtable3 --where brandflag = 't43'
--order by addate, adtime, fk_stationid;
WITH adBrandPosition 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
,ROW_NUMBER() OVER (ORDER BY FK_StationID, AdDate, AdTime) N -- Sequence timeslots
FROM dbo.testtable3
)
-- Group Brand sequences for station and date ** ADDED **
,adBrandGroup AS
(
SELECT *
,(N - adBrandPosition) brandgroup
,ROW_NUMBER() OVER (PARTITION BY (N - adBrandPosition), FK_StationID, AdDate ORDER BY AdTime) adBrandGroupPosition
FROM adBrandPosition
)
-- Determine time from previous advert in seconds
,TimeToPrevAd AS (
SELECT a.AdDate, a.AdTime, a.FK_StationId, a.brandflag, a.NumAds, a.AdBrandPosition
, a.adBrandGroupPosition, a.N, a.brandgroup -- ** ADDED Extra Columns**
,DATEDIFF(s, b.AdTime, a.adTime) TimeToPrevAd
FROM adBrandGroup a
LEFT OUTER JOIN adBrandGroup b
ON
--a.AdBrandPosition = b.AdBrandPosition + 1
a.N = b.N + 1 -- ** ADDED **
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((adBrandGroupPosition /*Changed*/ * PrevAdInGroup) - ROW_NUMBER() OVER (PARTITION BY BrandGroup /*Changed*/, 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 BrandGroup, FK_StationID, AdDate, g ORDER BY AdTime) as varchar(10)) + '/'
-- Number in the group
+ cast(COUNT(*) OVER (PARTITION BY BrandGroup, FK_StationID, AdDate, g) as varchar(10)) + ' of '
-- Group Number
+ cast(g as varchar(10)) desc1
FROM adGroups
ORDER BY fk_stationid, addate, adtime, brandflag;
July 23, 2013 at 2:44 am
MickyT, as i said, you are the man.
Thanks so much, you have saved my job.
I'm Greatfull, it works as expected. I'm studying the codes, the place i had small problem was the first cte, i noticed that once the first cte is well organised, you are able to conjure the others.
Thanks so much
Tim
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply