March 24, 2011 at 6:31 am
Hi guys, i have this code that works:
declare @Station nvarchar(4),
@BeginDate datetime,
@EndDate datetime,
@BeginTime nvarchar(8),
@EndTime nvarchar(8),
@Scheduled int,
@Duration int,
@Program nvarchar(355),
@product nvarchar(6),
@brand-2 nvarchar(4),
@Identifier nvarchar(2),
@Client nvarchar(255)
set @Station = 'a60'
set @BeginTime = '21:55:00'
set @EndTime = '23:05:00'
set @Scheduled = 1
set @Duration = 60
set @Program = 'Just Testing'
set @product = 'pha001'
set @brand-2 = '0001'
set @Identifier = '01'
set @Client = 'Nokia'
;WITH CTE AS
(
SELECT AdDate
,AdTime
,dbo.tbl_Stations.Description as Stations, dbo.tbl_BrandAD.Description AS Identifier
,RN = ROW_NUMBER() OVER(PARTITION BY AdDate ORDER BY AdTime)
FROM dbo.tbl_TelevisionTemp 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
WHERE AdDate In ('25 jan 2011','26 jan 2011','27 jan 2011','31 jan 2011')
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
)
SELECT AdDate,
(Case Rad_Outer.AdDate When Rad_Outer.AdDate Then Count(*) Else 0 END) as CapturedSpots
-- ,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program, Identifier
,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
it gives me a result like this:
insert into mytable(Addate, CapturedSpots, ScheduledSpots, ScheduledTime, CapturedTime, CapturedExtra) Values(
Select '2011-01-26', '1', '1', '21:55:00-23:05:00', '22:16:03', ' ' union all
Select '2011-01-27', '1', '1', '21:55:00-23:05:00', '22:14:40', ' ' union all
Select '2011-01-31', '2', '1', '21:55:00-23:05:00', '22:16:12', '22:16:13'
i want it to return something like this:
insert into mytable(Addate, CapturedSpots, ScheduledSpots, ScheduledTime, CapturedTime, CapturedExtra) Values(
Select '2011-01-25', '0', '1', ' ', ' ' union all
Select '2011-01-26', '1', '1', '21:55:00-23:05:00', '22:16:03', ' ' union all
Select '2011-01-27', '1', '1', '21:55:00-23:05:00', '22:14:40', ' ' union all
Select '2011-01-31', '2', '1', '21:55:00-23:05:00', '22:16:12', '22:16:13'
Here is some sample data:
CREATE TABLE [dbo].[tbl_TelevisionTemp](
[AdDate] [datetime] NULL,
[AdTime] [nvarchar](8) NULL,
[Duration] [float] NULL,
[FK_BrandId] [nvarchar](4) NULL,
[FK_BrandADId] [nvarchar](2) NULL,
[FK_StationId] [nvarchar](5) NULL,
[FK_ProductId] [nvarchar](6) NULL
)
Insert into tbl_Radio(AdDate, AdTime, Duration, FK_BrandId, FK_BrandADId, Fk_StationId, FK_ProductId) Values (
Select '26/01/2011', '22:16:03','60','0001','01','A60','PHA001' union all
Select '27/01/2011', '22:14:27','45','0001','01','A60','PHA001' union all
Select '27/01/2011', '22:14:40','60','0001','01','A60','PHA001' union all
Select '31/01/2011', '22:16:12','60','0001','01','A60','PHA001' union all
Select '31/01/2011', '22:16:13','60','0001','01','A60','PHA001' union all
Thanks very much for your response
Timotech
March 24, 2011 at 8:15 am
So digging through this it looks like you want to return a record for 1/25 in the result but there is no record for that date in the source data? Most likely you will need to a left join to a temp table or something so you can have that date returned with a count of 0.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 24, 2011 at 8:22 am
Thanks for your reply, i'll work on it and get back to you. but how do u suggest my temp table look like.
Thanks
Timotech
March 24, 2011 at 8:30 am
Probably just a date column that would have all 4 dates from the where clause in your cte. Then instead of putting your condition in the where you would do a left join instead. That make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 24, 2011 at 8:50 am
It looks like you're trying to get a list of all of the dates in a calendar week, excluding weekends (include Jan 25, but not Jan 29/30)?
Here's some code that will return all of the weekdays in a month. Does this help out?
-- When posting dates, always include your dateformat setting
-- so others can copy your code and run it.
SET DATEFORMAT DMY;
--Here is some sample data:
-- being put into a table var vs. permanent table
-- (makes cleanup a lot easier!)
DECLARE @tbl_TelevisionTemp TABLE (
[AdDate] [datetime] NULL,
[AdTime] [nvarchar](8) NULL,
[Duration] [float] NULL,
[FK_BrandId] [nvarchar](4) NULL,
[FK_BrandADId] [nvarchar](2) NULL,
[FK_StationId] [nvarchar](5) NULL,
[FK_ProductId] [nvarchar](6) NULL
)
INSERT INTO @tbl_TelevisionTemp
Select '26/01/2011', '22:16:03', '60', '0001', '01', 'A60', 'PHA001' union all
Select '27/01/2011', '22:14:27', '45', '0001', '01', 'A60', 'PHA001' union all
Select '27/01/2011', '22:14:40', '60', '0001', '01', 'A60', 'PHA001' union all
Select '31/01/2011', '22:16:12', '60', '0001', '01', 'A60', 'PHA001' union all
Select '31/01/2011', '22:16:13', '60', '0001', '01', 'A60', 'PHA001' ;
DECLARE @StartDate DATETIME,
@EndDate DATETIME,
@QtyDates INTEGER;
-- get the earliest starting date
SELECT @StartDate = MIN(AdDate)
FROM @tbl_TelevisionTemp;
-- get the beginning of the month for this date.
SET @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0);
-- get the ending of the month.
SET @EndDate = DATEADD(day, -1, DATEADD(MONTH, 1, @StartDate));
-- get the # of days in that month.
SET @QtyDates = DATEDIFF(DAY, @StartDate, @EndDate)+1;
--SELECT @StartDate, @EndDate, @QtyDates;
-- For information on how a tally table works, please see the article
-- 'The "Numbers" or "Tally" Table - What it is and how it replaces a loop'
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/
;WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
SELECT TOP (@QtyDates)
MyDate = DATEADD(DAY, N-1, @StartDate),
DayOfWeek = DATENAME(weekday, DATEADD(DAY, N-1, @StartDate)),
DayNumOfWeek = DATEPART(weekday, DATEADD(DAY, N-1, @StartDate))
FROM TALLY
WHERE N <= @QtyDates
AND DATEPART(weekday, DATEADD(DAY, N-1, @StartDate))
BETWEEN 2 AND 6; -- may need to adjust depending on your set datefirst setting.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 24, 2011 at 9:09 am
I just don't know what is wrong, i actually created the temporary table, and tested it outside the query and it shows me that the temporary table actually contains data, but when i run the complete code, it still doesn't bring out the correct result.
This is the query i ran:
declare @ids varchar(8000),
@Station nvarchar(4),
@BeginDate datetime,
@EndDate datetime,
@BeginTime nvarchar(8),
@EndTime nvarchar(8),
@Scheduled int,
@Duration int,
@Program nvarchar(355),
@product nvarchar(6),
@brand-2 nvarchar(4),
@Identifier nvarchar(2),
@Client nvarchar(255)
set @ids = '25 jan 2011,26 jan 2011,27 jan 2011,31 jan 2011'
set @Station = 'a60'
set @BeginTime = '21:55:00'
set @EndTime = '23:05:00'
set @Scheduled = 1
set @Duration = 60
set @Program = 'Just Testing'
set @product = 'pha001'
set @brand-2 = '0001'
set @Identifier = '01'
set @Client = 'Nokia'
CREATE TABLE #TestData (RequiredDate Datetime)
Insert into #TestData
SELECT * FROM iter$simple_intlist_to_tbl(@ids)
;WITH CTE AS
(
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_TelevisionTemp 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
)
SELECT AdDate,
(Case Rad_Outer.AdDate When Rad_Outer.AdDate Then Count(*) Else 0 END) as CapturedSpots
-- ,COUNT(*) [Number Of Spots]
,@Scheduled AS ScheduledSpots, @Program AS Program, Identifier
,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
drop Table #TestData
This function "'iter$simple_intlist_to_tbl" is to split the dates and store in the table
Thanks for your help
Timotech
March 24, 2011 at 9:21 am
Hi WanyeS, thanks for your reply, actually thats not what i want, if you check my post, what i want is to display also the date that does not meet the requirement, but that date must be a date supplied by me.
Thanks
March 24, 2011 at 9:28 am
Your join logic above still won't get what you want. I can toss some pseudocode at it but without actual dll and some sample data i can't test it. check out the first link in my signature (or Wayne's) for an article on the best way to post this. You will probably have to make your temp table the driving query for you cte.
select ....
from #temp
join all the other stuff
Something along those lines.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 24, 2011 at 9:56 am
Thanks for your reply, You are right about the temp table having control, i used a right join and i started getting results, i'll work more on it and get back to you.
Thanks
Timotech
March 24, 2011 at 10:35 am
LOL or a right join would do it too. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 24, 2011 at 10:45 am
I finally have what i wanted, thanks Guys, here is the Code:
declare @ids varchar(8000),
@Station nvarchar(4),
@BeginDate datetime,
@EndDate datetime,
@BeginTime nvarchar(8),
@EndTime nvarchar(8),
@Scheduled int,
@Duration int,
@Program nvarchar(355),
@product nvarchar(6),
@brand-2 nvarchar(4),
@Identifier nvarchar(2),
@Client nvarchar(255)
set @ids = '25 jan 2011,26 jan 2011,27 jan 2011,31 jan 2011'
set @Station = 'a60'
set @BeginTime = '21:55:00'
set @EndTime = '23:05:00'
set @Scheduled = 1
set @Duration = 60
set @Program = 'Just Testing'
set @product = 'pha001'
set @brand-2 = '0001'
set @Identifier = '01'
set @Client = 'Nokia'
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_TelevisionTemp 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
)
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
drop Table #TestData
Timotech
March 24, 2011 at 11:02 am
Great! Glad we could help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply