February 17, 2011 at 3:17 am
Thanks Guys, I ran this query and it worked very fine:
declare @BeginDate datetime, @EndDate datetime, @Station nvarchar(4), @BeginTime nvarchar(8), @EndTime nvarchar(8),
@Scheduled int, @Duration int
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
SELECT AdDate,
COUNT(Duration) AS [Number Of Spots],
@Scheduled AS ScheduledSpots,
@BeginTime + '-' + @EndTime AS ScheduledTime,
STUFF(CAST((
SELECT ', ' + AdTime
FROM dbo.tbl_radio mt2
WHERE mt2.AdDate = mt1.AdDate
AND mt2.FK_StationId = @Station
AND mt2.AdTime BETWEEN @BeginTime AND @EndTime
AND mt2.Duration = @Duration
FOR XML PATH(''),TYPE
) AS NVARCHAR(MAX)),1,2,'') AS CapturedTime
FROM dbo.tbl_radio mt1
WHERE AdDate BETWEEN @BeginDate AND @EndDate
AND FK_StationId = @Station
AND AdTime BETWEEN @BeginTime AND @EndTime
AND Duration = @Duration
GROUP BY AdDate
it gave me this results
insert into mytable(Addate, [Number Of Spots], ScheduledSpots, ScheduledTime, CapturedTime) Values(
Select '2010-06-02', '11', '6', '05:55:00-12:05:00', '07:28:35, 07:44:07, 09:52:57, 10:58:05, 11:06:35, 11:07:05, 11:29:38, 05:55:48, 05:57:55, 09:00:42, 09:34:03' union all
Select '2010-06-03', '7', '6','05:55:00-12:05:00', '08:43:47, 08:46:18, 08:46:48, 09:55:25, 06:07:49, 06:45:05, 07:33:12'
Thanks Guys
Please guys, as u can see, the SchedulesSpots in my test is '6', so i would want the CapturedTime to show only 6 values, then the rest will be stored in another Column called CapturedExtra.
I don't know if u got what i said, but let me put it like this:
insert into mytable(Addate, [Number Of Spots], ScheduledSpots, ScheduledTime, CapturedTime, CapturedExtra) Values(
Select '2010-06-02', '11', '6', '05:55:00-12:05:00', '07:28:35, 07:44:07, 09:52:57, 10:58:05, 11:06:35, 11:07:05', '11:29:38, 05:55:48, 05:57:55, 09:00:42, 09:34:03' union all
Select '2010-06-03', '7', '6','05:55:00-12:05:00', '08:43:47, 08:46:18, 08:46:48, 09:55:25, 06:07:49, 06:45:05', '07:33:12'
Thanks so much for your helps
Timotech
February 17, 2011 at 6:23 am
ColdCoffee (2/16/2011)
Oops, Jeff had already posted the same solution 🙂 Sorry Jeff,dint see it 🙂
No problem CC... now the OP has a couple of choices. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2011 at 6:27 am
timotech (2/17/2011)
Please guys, as u can see, the SchedulesSpots in my test is '6', so i would want the CapturedTime to show only 6 values, then the rest will be stored in another Column called CapturedExtra.I don't know if u got what i said, but let me put it like this:
insert into mytable(Addate, [Number Of Spots], ScheduledSpots, ScheduledTime, CapturedTime, CapturedExtra) Values(
Select '2010-06-02', '11', '6', '05:55:00-12:05:00', '07:28:35, 07:44:07, 09:52:57, 10:58:05, 11:06:35, 11:07:05', '11:29:38, 05:55:48, 05:57:55, 09:00:42, 09:34:03' union all
Select '2010-06-03', '7', '6','05:55:00-12:05:00', '08:43:47, 08:46:18, 08:46:48, 09:55:25, 06:07:49, 06:45:05, 07:33:12', ' '
I'm on my way to work so won't be able to get back to this until tonight... heh... unless ColdCoffee beats me to it which isn't a bad thing at all. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2011 at 7:01 am
Thanks Jeff, I'll be expecting.
February 17, 2011 at 9:24 pm
timotech (2/17/2011)
Thanks Jeff, I'll be expecting.
Ummmm.... based on what I showed you in the previous code, couldn't you be "attemping" instead of "expecting"? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2011 at 11:18 pm
This?
DECLARE @Scheduled VARCHAR(2) = '3'
,@BeginTime VARCHAR(8) = '05:55:00'
,@EndTime VARCHAR(8) = '12:30:00'
,@Station VARCHAR(5) = 'B97'
,@Duration FLOAT = 45
,@BeginDate DATE = '2010-06-02 00:00:00.000'
,@EndDate DATE = '2010-06-02 00:00:00.000'
;WITH CTE AS
(
SELECT AdDate
,AdTime
--,CapturedTime
,RN = ROW_NUMBER() OVER(ORDER BY AdTime)
FROM dbo.tbl_Radio Rad_Outer
WHERE AdDate BETWEEN @BeginDate AND @EndDate
AND CAST( AdTime AS TIME) BETWEEN @BeginTime AND @EndTime
AND FK_StationId = @Station
AND Duration = @Duration
)
SELECT AdDate
,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN >= @Scheduled
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE
GROUP BY AdDate
February 18, 2011 at 1:56 am
Hi Jeff, Thanks, Actually i meant to say i'm also working on it, and expecting what u will also come out with. Thanks so much Cold Coffee, I tried your code, it actually worked but it was showing a duplication on the captured extra column, i.e. the same time was shown for both dates, and it still listed all the 11 spots not the remaining 5 extra spots.
This is what was displayed:
insert into mytable(Addate, [Number Of Spots], ScheduledSpots, ScheduledTime, CapturedTime, CapturedExtra) Values(
Select '2010-06-02', '11', '6', '05:55:00-12:05:00', '05:55:48,05:57:55,06:07:49,06:45:05,07:28:35,07:33:12', '07:44:07,08:43:47,08:46:18,08:46:48,09:00:42,09:34:03,09:52:57,09:55:25,10:58:05,11:06:35,11:07:05,11:29:38' union all
Select '2010-06-03', '7', '6', '05:55:00-12:05:00', '05:55:48,05:57:55,06:07:49,06:45:05,07:28:35,07:33:12', '07:44:07,08:43:47,08:46:18,08:46:48,09:00:42,09:34:03,09:52:57,09:55:25,10:58:05,11:06:35,11:07:05,11:29:38'
As u can see its still displaying the 11 spots on the CapturedExtra, I am also working on the code Jeff sent, i'll post it later.
Thanks
Timotech
February 18, 2011 at 5:09 am
Timotech, just change the >= to > in the CapturedExtra column and check if it is performing the way it should...
February 18, 2011 at 5:57 am
Thanks so much Cold Coffee, sorry that my disturbance is too much, the '>' actually worked when i tested with one single date, i.e making @BeginDate and @EndDate the same. Every was ok, but when i increase the dates such as the query below, it starts duplicating things again.
declare @BeginDate datetime, @EndDate datetime, @Station nvarchar(4), @BeginTime nvarchar(8), @EndTime nvarchar(8),
@Scheduled int, @Duration int
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
;WITH CTE AS
(
SELECT AdDate
,AdTime
--,CapturedTime
,RN = ROW_NUMBER() OVER(ORDER BY AdTime)
FROM dbo.tbl_Radio Rad_Outer
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
,@BeginTime + '-' + @EndTime AS ScheduledTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN <= @Scheduled
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedTime
,STUFF ( CAST((SELECT ',' + Rad_Inner.AdTime
FROM CTE Rad_Inner
WHERE RN > @Scheduled
FOR XML PATH('')) AS NVARCHAR(MAX))
,1,1,'') AS CapturedExtra
FROM CTE
GROUP BY AdDate
but when i try:
set @BeginDate = '2 jun 2010'
set @EndDate = '2 jun 2010'
everything works very fine.
Thanks
February 18, 2011 at 9:35 pm
Ok, how about this?
declare @BeginDate datetime, @EndDate datetime, @Station nvarchar(4), @BeginTime nvarchar(8), @EndTime nvarchar(8),
@Scheduled int, @Duration int
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
;WITH CTE AS
(
SELECT AdDate
,AdTime
--,CapturedTime
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_Radio
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
,@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
February 19, 2011 at 1:19 am
Perfect. Cold Coffee, you're the man, thanks so much for your help. Thanks Jeff also for your help, please how can i get an article or something that explains this technology you used in detail, that is for dummies kind of thing?
Thanks so much
Timotech
February 19, 2011 at 6:51 am
timotech (2/19/2011)
Perfect. Cold Coffee, you're the man, thanks so much for your help. Thanks Jeff also for your help, please how can i get an article or something that explains this technology you used in detail, that is for dummies kind of thing?Thanks so much
Timotech
:blush: whoosh.. thanks for the feedback Timo 🙂 Wayne Sheffield has an interesting article that describes about this; i will look for it and paste the link here..
February 19, 2011 at 8:57 am
Thanks man, 🙂
February 21, 2011 at 3:45 am
Here's the link to Wayne's article...
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2011 at 3:48 am
Jeff Moden (2/21/2011)
Here's the link to Wayne's article...http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Thanks Jeff, i hadn't had time to search for it 🙂
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply