February 21, 2011 at 5:12 am
Thank you all Very Much.
February 21, 2011 at 6:57 am
Hi guys, what i'm i doing wrong its telling me invalid object name dbo.tbl_Radio
This is my code:
declare @BeginDate datetime, @EndDate datetime, @Station nvarchar(4), @BeginTime nvarchar(8), @EndTime nvarchar(8),
@Scheduled int, @Duration int, @Program nvarchar(Max)
set @BeginDate = '2 jun 2010'
set @EndDate = '3 jun 2010'
set @Station = 'b83'
set @BeginTime = '05:55:00'
set @EndTime = '12:05:00'
set @Scheduled = 6
set @Duration = 30
set @Program = 'ROS on RSTV P/H'
;WITH CTE AS
(
SELECT dbo.tbl_Radio.AdDate
,dbo.tbl_Radio.AdTime
,dbo.tbl_Stations.Description as Station
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_Radio INNER JOIN
dbo.tbl_Stations ON dbo.tbl_Radio.FK_StationId = dbo.tbl_Stations.StationId
WHERE dbo.tbl_Radio.AdDate BETWEEN @BeginDate AND @EndDate
AND dbo.tbl_Radio.AdTime BETWEEN @BeginTime AND @EndTime
AND dbo.tbl_Radio.FK_StationId = @Station
AND dbo.tbl_Radio.Duration = @Duration
)
SELECT AdDate
,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN > @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE Rad_Outer
GROUP BY AdDate
Thanks
Timotech
February 21, 2011 at 8:49 am
Hi Guys, I got it, this is the code i used, works ok
declare @BeginDate datetime, @EndDate datetime, @Station nvarchar(4), @BeginTime nvarchar(8), @EndTime nvarchar(8),
@Scheduled int, @Duration int, @Program nvarchar(Max)
set @BeginDate = '2 jun 2010'
set @EndDate = '3 jun 2010'
set @Station = 'b83'
set @BeginTime = '05:55:00'
set @EndTime = '12:05:00'
set @Scheduled = 6
set @Duration = 30
set @Program = 'ROS on RSTV P/H'
;WITH CTE AS
(
SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_Radio as r INNER JOIN
dbo.tbl_Stations ON r.FK_StationId = dbo.tbl_Stations.StationId
WHERE AdDate BETWEEN @BeginDate AND @EndDate
AND AdTime BETWEEN @BeginTime AND @EndTime
AND FK_StationId = @Station
AND Duration = @Duration
)
SELECT AdDate
,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program
,Stations
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN > @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE Rad_Outer
GROUP BY AdDate, Stations
February 21, 2011 at 4:58 pm
timotech (2/21/2011)
Hi Guys, I got it, this is the code i used, works ok
Very cool. Congrats and thanks for posting your final solution! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2011 at 12:28 am
Hi Jeff, thanks for your reply, since then i've been doing a lot of experiments, i have a little challenge though.
I want to be able to pass in dynamic dates for the @BeginDate, now called @AdDate and therefore get rid of the @EndDate, something like this:
declare @AdDate datetime, @Station nvarchar(4), @BeginTime nvarchar(8), @EndTime nvarchar(8),
@Scheduled int, @Duration int, @Program nvarchar(Max)
--set @AdDate in ('1 Jun 2010','2 Jun 2010','3 Jun 2010')
set @Station = 'b104'
set @BeginTime = '05:55:00'
set @EndTime = '12:05:00'
set @Scheduled = 8
set @Duration = 30
set @Program = 'Premium time On Wazobia P/H'
;WITH CTE AS
(
SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_Radio as r INNER JOIN
dbo.tbl_Stations ON r.FK_StationId = dbo.tbl_Stations.StationId
WHERE AdDate In ('1 Jun 2010','2 Jun 2010','3 Jun 2010')
AND AdTime BETWEEN @BeginTime AND @EndTime
AND FK_StationId = @Station
AND Duration = @Duration
)
SELECT AdDate
,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program
,Stations
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN > @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE Rad_Outer
GROUP BY AdDate, Stations
Notice the commented @AdDate, I can't seem to get it to work. I tried using this function:
RETURNS @tbl TABLE (number datetime NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(datetime, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
which does the trick but only returns one row, what do u think i can do to the 'IN' Statement so i can be reading my dates dynamically. This is how i used the function:
Create PROCEDURE [dbo].[stp_GetReconciliation_sel] (
@ids nvarchar(11),
@Station nvarchar(4),
@BeginTime nvarchar(8),
@EndTime nvarchar(8),
@Scheduled int,
@Duration int,
@Program nvarchar(255)
)
AS
BEGIN
;WITH CTE AS
(
SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_Radio as r INNER JOIN
dbo.tbl_Stations ON r.FK_StationId = dbo.tbl_Stations.StationId
WHERE AdDate In (SELECT i.number FROM iter$simple_intlist_to_tbl(@ids) i)
AND AdTime BETWEEN @BeginTime AND @EndTime
AND FK_StationId = @Station
AND Duration = @Duration
)
SELECT AdDate
,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program
,Stations
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN > @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE Rad_Outer
GROUP BY AdDate, Stations
END
Any Advice will be appreciated.
Thanks
Timotech
February 22, 2011 at 5:42 am
Hi Guys, seems like that other issue is not a big problem, i have a bigger problem, how can i insert the result of the cte into a table called tbl_Reconciliation such as :
Create PROCEDURE [dbo].[stp_GetReconciliationTelevision_sel] (
@ids nvarchar(11),
@Station nvarchar(4),
@BeginTime nvarchar(8),
@EndTime nvarchar(8),
@Scheduled int,
@Duration int,
@Program nvarchar(255)
)
AS
BEGIN
;WITH CTE AS
(
SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_Television as t INNER JOIN
dbo.tbl_Stations ON t.FK_StationId = dbo.tbl_Stations.StationId
WHERE AdDate In (SELECT i.number FROM iter$simple_intlist_to_tbl(@ids) i)
AND AdTime BETWEEN @BeginTime AND @EndTime
AND FK_StationId = @Station
AND Duration = @Duration
)
SELECT AdDate
,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program
,Stations
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN > @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE Rad_Outer
GROUP BY AdDate, Stations
Insert into tbl_Reconciliation
Select * from CTE
END
Go
February 22, 2011 at 7:42 am
Anyway guys, i solved the bigger problem again, remaining the smaller one, i.e how to adjust the function
This is my code:
ALTER PROCEDURE [dbo].[stp_GetReconciliationRadio_sel] (
@ids nvarchar(11),
@Station nvarchar(4),
@BeginTime nvarchar(8),
@EndTime nvarchar(8),
@Scheduled int,
@Duration int,
@Program nvarchar(255)
)
AS
BEGIN
;WITH CTE AS
(
SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_Radio as r INNER JOIN
dbo.tbl_Stations ON r.FK_StationId = dbo.tbl_Stations.StationId
WHERE AdDate In (SELECT i.number FROM iter$simple_intlist_to_tbl(@ids) i)
AND AdTime BETWEEN @BeginTime AND @EndTime
AND FK_StationId = @Station
AND Duration = @Duration
),
CTE2 AS
(SELECT AdDate
,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program
,Stations
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM ReconciliationCTE Rad_Inner
WHERE RN <= @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM ReconciliationCTE Rad_Inner
WHERE RN > @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM ReconciliationCTE Rad_Outer
GROUP BY AdDate, Stations
)
Insert into tbl_Reconciliation(AdDate, Number_Of_Spots, ScheduledSpots, Program, Stations, ScheduledTime, CapturedTime, CapturedExtra)
Select AdDate, [Number Of Spots], ScheduledSpots, Program, Stations, ScheduledTime, CapturedTime, CapturedExtra from CTE2;
END
So guys any solution with the function in my previous post
Thanks all
Timotech
February 23, 2011 at 3:44 am
All right guys i saw my mistake about the function, it actually works, but i did a mistake with this line:
@ids nvarchar(11),
its supposed to be something like this @ids nvarchar(255), so all works very fine now
Thanks all for all the knowledge impacted.
God bless you
Timotech
March 24, 2011 at 5:05 am
Hi Guys, i'm back again on this topic, i need some help, i've tried everything i know.
From my previous posts, i have this code:
declare @AdDate datetime, @Station nvarchar(4), @BeginTime nvarchar(8), @EndTime nvarchar(8),
@Scheduled int, @Duration int, @Program nvarchar(Max)
set @AdDate in ('1 Jun 2010','2 Jun 2010','3 Jun 2010')
set @Station = 'b104'
set @BeginTime = '05:55:00'
set @EndTime = '12:05:00'
set @Scheduled = 8
set @Duration = 30
set @Program = 'Premium time On Wazobia P/H'
;WITH CTE AS
(
SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_Radio as r INNER JOIN
dbo.tbl_Stations ON r.FK_StationId = dbo.tbl_Stations.StationId
WHERE AdDate In ('1 Jun 2010','2 Jun 2010','3 Jun 2010')
AND AdTime BETWEEN @BeginTime AND @EndTime
AND FK_StationId = @Station
AND Duration = @Duration
)
SELECT AdDate
,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program
,Stations
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN > @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE Rad_Outer
GROUP BY AdDate, Stations
which works very fine. my need is that it only displays for specified dates that meet the criteria, what if a date does not meet the criteria, i want to show the date with a zero count, to show that on that date no records was returned, please how can i do that, so that it not only returns the right days, but also return the date that does not meet the criteria as zero.
Thanks guys
example result below:
insert into mytable(Addate, [Number Of Spots], ScheduledSpots, ScheduledTime, CapturedTime, CapturedExtra) Values(
Select '2010-06-01', '0', '3', '05:55:00-12:05:00', ' ' union all
Select '2010-06-02', '2', '3', '05:55:00-12:05:00', '07:28:35, 07:44:07' union all
Select '2010-06-03', '3', '3', '05:55:00-12:05:00', '08:43:47, 08:46:18, 08:46:48'
Please note on the first returned zero for [Number Of Spots]
Thanks for your help
March 26, 2011 at 12:08 pm
My apologies. I lost track of this thread. I've been pretty busy.
If you could put the CREATE TABLE, data inserts, the code in question, and the expected results all in one post to save me time by me not having to look for all the parts to put together, I'll give it a crack.:-)
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2011 at 2:25 pm
Thanks Jeff, i already solved the problem and only forgot to post it here. Thanks so much for your reply, it shows u care. I appreciate.
March 26, 2011 at 2:31 pm
timotech (3/26/2011)
Thanks Jeff, i already solved the problem and only forgot to post it here. Thanks so much for your reply, it shows u care. I appreciate.
Hope you intend to post the solution so that other who may have a similar problem can learn from you. In other words give back to the community who attempted to assist you.
March 27, 2011 at 10:43 am
Hope you intend to post the solution so that other who may have a similar problem can learn from you. In other words give back to the community who attempted to assist you.
Yes bitbucket-25253, I'll post it, this is the code that solved the problem
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)
;WITH CTE AS
(
Select Isnull(d.AdDate,#TestData.RequiredDate) as AdDate, AdTime, Stations, Identifier, Isnull(RN,0) as RN From
(SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations, dbo.tbl_BrandAD.Description AS Identifier
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_RadioTemp as t JOIN
dbo.tbl_Stations ON t.FK_StationId = dbo.tbl_Stations.StationId 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 left outer join #TestData on t.AdDate = #TestData.RequiredDate
WHERE 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
),
CTE2 AS
(SELECT AdDate
,(Case When Rad_Outer.Identifier IS NULL Then 0 Else Count(*) END) as CapturedSpots
-- ,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program, isnull(Identifier, (Select [Description] from tbl_BrandAd where FK_ProductId = @product and FK_Brandid = @brand-2 and BrandAdId = @Identifier)) As Identifier
,isnull(Stations,(Select [Description] from tbl_Stations Where StationId = @Station)) as Stations, @Client as Client
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ', ' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ', ' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN > @Scheduled
AND Rad_Inner.AdDate = Rad_Outer.AdDate
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE Rad_Outer
GROUP BY AdDate, Stations, Identifier
)
Select AdDate, CapturedSpots, ScheduledSpots, Program, Stations, ScheduledTime, CapturedTime, CapturedExtra, Identifier, Client from CTE2;
drop Table #TestData
Thanks for reminding me, i'm grateful.
Timotech
March 27, 2011 at 11:53 am
hey timo, sorry couldnt get time to work on ur request, but seems like u figured it out yourself... thanks for the posting the final code, helps a lot of people who may stumble this thread...
March 27, 2011 at 1:01 pm
timotech (3/26/2011)
Thanks Jeff, i already solved the problem and only forgot to post it here. Thanks so much for your reply, it shows u care. I appreciate.
Thank you for the reply and the code. It's positive proof that you care, as well. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply