September 15, 2012 at 9:39 am
hi all,
Please i need help, what i want to achieve is for example, i have some records in this format
Date Time Campaign
12/08/2012 06:01:00 Mtn
12/08/2012 06:05:00 Mtn
12/08/2012 06:07:00 Mtn
I want to send a report to a client that booked for 6 spots on the particular date between '06:00:00' and '06:10:00'
the result will be shown like this:
Date Time Campaign Status
12/08/2012 06:01:00 Mtn Detected
12/08/2012 06:05:00 Mtn Detected
12/08/2012 06:07:00 Mtn Detected
12/08/2012 Mtn Not Detected
12/08/2012 Mtn Not Detected
12/08/2012 Mtn Not Detected
September 15, 2012 at 9:41 am
post your query which you use
September 15, 2012 at 10:03 am
This is the query
declare @ids varchar(8000),
@Station nvarchar(4),
@BeginDate datetime,
@EndDate datetime,
@BeginTime nvarchar(8),
@EndTime nvarchar(8),
@BeginTime1 nvarchar(8),
@EndTime1 nvarchar(8),
@BeginTime2 nvarchar(8),
@EndTime2 nvarchar(8),
@Scheduled int,
@Duration int,
@Program nvarchar(355),
@product nvarchar(6),
@brand-2 nvarchar(4),
@Identifier nvarchar(2),
@Client nvarchar(255)
set @ids = '17 jul 2012,18 jul 2012,19 jul 2012,20 jul 2012,21 jul 2012,22 jul 2012,23 jul 2012,24 jul 2012,25 jul 2012,27 jul 2012,28 jul 2012,29 jul 2012,30 jul 2012,31 jul 2012'
set @Station = 'b107'
set @BeginDate = '1 jul 2012'
set @Enddate = '31 jul 2012'
set @BeginTime = '06:00:00'
set @EndTime = '09:00:00'
set @Scheduled = '6'
set @Duration = '60'
set @Program = 'ROS on Beat 99.9 FM, Lagos'
set @product = 'hog001'
set @brand-2 = '0931'
set @Identifier = '06'
set @Client = 'hpz'
IF OBJECT_ID('tempdb..#TestData','U') IS NOT NULL DROP TABLE #TestData;
CREATE TABLE #TestData (RequiredDate Datetime)
Insert into #TestData
SELECT * FROM iter$simple_intlist_to_tbl(@ids)
Select Isnull(d.AdDate,#TestData.RequiredDate) as AdDate, AdTime, isnull(Stations, (Select Description from tbl_Stations Where StationId = @Station)) as Station,Identifier, Brand,Isnull(RN,0) as RN From
(SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations, dbo.tbl_BrandAD.Description AS Identifier, dbo.tbl_Brand.Description as Brand
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_TelevisionTemp as t INNER JOIN
dbo.tbl_Stations ON t.FK_StationId = dbo.tbl_Stations.StationId JOIN
dbo.tbl_Brand on t.FK_ProductId = dbo.tbl_Brand.FK_ProductId and t.FK_BrandId = dbo.tbl_Brand.BrandId INNER JOIN
dbo.tbl_BrandAD ON t.FK_ProductId = dbo.tbl_BrandAD.FK_ProductId AND t.FK_BrandId = dbo.tbl_BrandAD.FK_BrandId AND
t.FK_BrandADId = dbo.tbl_BrandAD.BrandADId
WHERE (AdDate In (SELECT i.number FROM iter$simple_intlist_to_tbl(@ids) i))
AND (AdTime BETWEEN @BeginTime AND @EndTime)
AND (t.fk_stationid = @Station)
--AND t.Duration = @Duration
And t.fk_productid = @product
And t.fk_brandid = @brand-2 And t.fk_brandadid = @Identifier) as d right join #TestData on d.AdDate = #TestData.RequiredDate
You can ask for clarifications
NB: The Scheduled spots determine how many rows
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply