Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how do I eliminate multiple results

Author  Topic 

bpb
Starting Member

6 Posts
Posted - 2008-11-25 : 14:13:15
Hello, I have been tasked with writing a script that will return a value from a particular time for a number of days in the past. I succeeded, but it returns a dataset (correct term?) for each iteration. Can somebody help. I have included my feeble attempts below :


declare @Width DateTime
declare @EndDate DateTime
declare @End varchar(50)
declare @Start varchar(50)
declare @Temp varchar(50)
declare @NumberDays int
declare @inc int

SET @NumberDays = 4
SET @inc = @NumberDays
SET @Start = CONVERT(Varchar(30),dateadd(dd,-@NumberDays,GetDate()))
SET @End = CONVERT(Varchar(30),GetDate())
SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))

WHILE (@inc >= 0)
BEGIN

SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))
declare @InnerSqlQry as varchar(4000)
declare @SqlQry as varchar(4000)
set @InnerSqlQry = 'Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >='''+convert(varchar(30), @Start ,120) +''' AND Datetime <= '''+convert(varchar(30),@Width ,120) +''' and wwResolution =60000 and wwRetrievalMode = "cyclic"'

set @SqlQry = N'Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' )'


exec(@sqlQry)

set @inc = @inc - 1
SET @Start = CONVERT(Varchar(30),dateadd(dd,-@inc,GetDate()))
SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))

END

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2008-11-25 : 15:23:01
I am not going to inspect your code so deep but an approach maybe:
Set/append all @InnerSqlQry using UNION ALL.
EXEC @sqlQry AFTER the end of the WHILE for only one time.

Webfred


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

bpb
Starting Member

6 Posts
Posted - 2008-11-26 : 09:04:44
Thanks for the info. I tried to get that to work but I just get one result back:

WHILE (@dec >= 1)
BEGIN

SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))


if (@dec = 1)
begin
set @InnerSqlQry = ' Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >='''+convert(varchar(30), @Start ,120) +''' AND Datetime <= '''+convert(varchar(30),@Width ,120) +''' and wwResolution =60000 and wwRetrievalMode = "cyclic" '
set @SqlQry = N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) '
end
else
begin
set @InnerSqlQry = ' Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >='''+convert(varchar(30), @Start ,120) +''' AND Datetime <= '''+convert(varchar(30),@Width ,120) +''' and wwResolution =60000 and wwRetrievalMode = "cyclic" '

set @SqlQry = N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ); Union all '

end

set @dec = @dec - 1
SET @Start = CONVERT(Varchar(30),dateadd(dd,-@inc,GetDate()))
SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))

END
exec(@sqlQry)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-26 : 09:09:02
Running this query on 3:07PM yielded this result (print instead of exec)
Select top 1 * from openquery(INSQL,'Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >=''Nov 22 2008  3:07PM'' AND Datetime <= ''2008-11-22 15:08:00'' and wwResolution =60000 and wwRetrievalMode = "cyclic"' )
Select top 1 * from openquery(INSQL,'Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >=''Nov 23 2008  3:07PM'' AND Datetime <= ''2008-11-23 15:08:00'' and wwResolution =60000 and wwRetrievalMode = "cyclic"' )
Select top 1 * from openquery(INSQL,'Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >=''Nov 24 2008  3:07PM'' AND Datetime <= ''2008-11-24 15:08:00'' and wwResolution =60000 and wwRetrievalMode = "cyclic"' )
Select top 1 * from openquery(INSQL,'Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >=''Nov 25 2008  3:07PM'' AND Datetime <= ''2008-11-25 15:08:00'' and wwResolution =60000 and wwRetrievalMode = "cyclic"' )
Select top 1 * from openquery(INSQL,'Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >=''Nov 26 2008  3:07PM'' AND Datetime <= ''2008-11-26 15:08:00'' and wwResolution =60000 and wwRetrievalMode = "cyclic"' )


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2008-11-26 : 09:20:22
quote:
Originally posted by bpb

Thanks for the info. I tried to get that to work but I just get one result back:

set @SqlQry = ''
WHILE (@dec >= 1)
BEGIN

SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))


if (@dec = 1)
begin
set @InnerSqlQry = ' Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >='''+convert(varchar(30), @Start ,120) +''' AND Datetime <= '''+convert(varchar(30),@Width ,120) +''' and wwResolution =60000 and wwRetrievalMode = "cyclic" '
set @SqlQry = @SqlQry + N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) '
end
else
begin
set @InnerSqlQry = ' Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >='''+convert(varchar(30), @Start ,120) +''' AND Datetime <= '''+convert(varchar(30),@Width ,120) +''' and wwResolution =60000 and wwRetrievalMode = "cyclic" '

set @SqlQry = @SqlQry + N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) Union all '

end

set @dec = @dec - 1
SET @Start = CONVERT(Varchar(30),dateadd(dd,-@inc,GetDate()))
SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))

END
exec(@sqlQry)


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-26 : 09:25:35
I assume you want first record within that timerange even though you have no ORDER BY?
Look! No use for dynamic SQL either...
DECLARE	@fromTime DATETIME,
	@toTime DATETIME,
	@fromDate DATETIME,
	@toDate DATETIME

SELECT	@fromTime = CONVERT(CHAR(5), GETDATE(), 108),
	@toTime = DATEADD(SECOND, 90, @fromTime),
	@fromDate = DATEADD(DAY, DATEDIFF(DAY, '19000105', GETDATE()), '19000101'),
	@toDate = DATEADD(DAY, 4, @fromDate)

SELECT	[Datetime],
	ACMKWHYTD_1
FROM	(
		SELECT	[Datetime],
			ACMKWHYTD_1,
			ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, '19000101', [Datetime]) ORDER BY [Datetime]) AS recID
		FROM	INSQL..WideHistory
		WHERE	[Datetime] >= @fromDate + @fromTime
			AND [Datetime] < @toDate + @toTime
			AND wwResolution = 60000
			AND wwRetrievalMode = 'cyclic'
			AND CONVERT(CHAR(8), [Datetime], 108) >= @fromTime
			AND CONVERT(CHAR(8), [Datetime], 108) < @toTime
	) AS d
WHERE	recID = 1

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

bpb
Starting Member

6 Posts
Posted - 2008-11-26 : 12:14:37
I GOT it thanks everyone!

I am not sure why it works this way but...it works..kind of a mess:

WHILE (@dec >= 1)
BEGIN

SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))

set @InnerSqlQry = ' Select DateTime, ACMKWHYTD_1 From WideHistory Where Datetime >='''+convert(varchar(30), @Start ,120) +''' AND Datetime <= '''+convert(varchar(30),@Width ,120) +''' and wwResolution =60000 and wwRetrievalMode = "cyclic" '

if(@dec = 1)
begin
set @SqlQry = @SqlQry+ N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) '
end

if((@dec > 1) and (@dec < @NumberDays))
begin
set @SqlQry = @SqlQry+ N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) union '
end

if(@dec = @NumberDays)
begin
set @SqlQry = N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) union '
end

set @dec = @dec - 1

SET @Start = CONVERT(Varchar(30),dateadd(dd,-@dec,GetDate()))
SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))

END

exec(@sqlQry)
Go to Top of Page

bpb
Starting Member

6 Posts
Posted - 2008-11-26 : 12:26:44
Okay...now I have the problem that the sqlQry string size is too small. As a result I can only get maximium of 18 statments. what is the top end for the character array?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-26 : 12:39:33
The answer is a simple "Do not use dynamic sql".


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

bpb
Starting Member

6 Posts
Posted - 2008-11-26 : 21:17:29
The answer is a simple "Do not use dynamic sql".

what do you mean by Dynamic SQL...please explain.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2008-11-26 : 21:27:13
http://www.sommarskog.se/dynamic_sql.html
Go to Top of Page

bpb
Starting Member

6 Posts
Posted - 2008-11-26 : 21:40:38
thanks sodeep...much to learn
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2008-11-26 : 22:28:32
No problem Happy Thanksgiving!!!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site