Error converting data type varchar to float.

Think you have incorrect single quotes (') which are causing the issue.  See the code snippet for correction.

Declare @sqlquery1 float, @sqlquery2 float
set @sqlquery1= (select top 1 round(convert(float,logspaceused),4) as SQL1
from dbo.EnterpriseDBLogspaceHist
where server_id = 3 and database_name = 'bistage'
order by  LogDT desc)
 
set @sqlquery2=(select top 1  round(convert(float,logspaceused),4)as SQL2 from 
(select top 2 round(convert(float,logspaceused),4)as sql3,logdt 
from dbo.EnterpriseDBLogspaceHist 
where server_id = 3 and database_name = 'bistage'
order by  LogDT desc )as test order by  LogDT asc)
 
select @SQLquery1-@sqlquery2

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

http://sequelserver.blogspot.com/2007/01/return-value-from-dynamic-sql.html


Declare @sqlquery1 float, @sqlquery2 float
declare @Sql1 nvarchar(1000), @Sql2 nvarchar(1000)
set @sql='
select top 1 @sqlquery1 = logspaceused from dbo.DBLogspaceHist
where server_id = 3 and database_name = ''bistage''
order by  LogDT desc'

set @sqlquery2='
select top 1 @sqlquery1=  logspaceused from
(select top 2 logspaceused,logdt from dbo.DBLogspaceHist
where server_id = 3 and database_name = ''bistage''
order by  LogDT desc )as test order by  LogDT asc'

exec sp_ExecuteSQL @Sql1, N'@sqlquery1 float output', @sqlquery1 output
exec sp_ExecuteSQL @Sql2, N'@sqlquery2 float output', @sqlquery2 output

select (round(convert(float,@sqlquery1),4)  -
       round(convert(float,@sqlquery2),4)) as SQL

thanks guys for the reply
ralmada: ur write no need of @sqlvariables the query worked fine
how does the query look if i try using @sqlvariables??

i have one more question here .. i have a list of logfilesize detalis from last few days in a table
i am actually trying to come with old data and get the percent growth of logfile ??
how can i do this?

did u get me??
thanks..

See mwvisa1's comment 25071602 for the alternative of using the variables.

Thaks for the reply..
i tried as mwvisa1's reply

Declare @sqlquery1 float, @sqlquery2 float
set @sqlquery1= (select top 1 round(convert(float,logspaceused),4) as SQL1
from dbo.EnterpriseDBLogspaceHist
where server_id = 3 and database_name = 'bistage'
order by  LogDT desc)
 
set @sqlquery2=(select top 1  round(convert(float,logspaceused),4)as SQL2 from
(select top 2 round(convert(float,logspaceused),4)as sql3,logdt
from dbo.EnterpriseDBLogspaceHist
where server_id = 3 and database_name = 'bistage'
order by  LogDT desc )as test order by  LogDT asc)
 
select @SQLquery1-@sqlquery2

getting below error
Invalid column name 'logspaceused'.
for the line <<set @sqlquery2=(select top 1  round(convert(float,logspaceused),4)as SQL2 from >>

can u pls help in this too

i have one more question here .. i have a list of logfilesize detalis from last few days in a table
i am actually trying to come with old data and get the percent growth of logfile ??
how can i do this?

<<i am actually trying to get compare last 2 value to get % of growth in log file>>
<< have a list of logfilesize detalis from last few days in a table
i am actually trying to come with old data and get the percent growth of logfile>>

As ralmada originally suggested, you don't need to use variables, you can accomplish what you want in one query.  For example, you could do something like the below.

-- use select top(2) ... to get just last two values
select LogDT, logspaceused
, case 
   when prev_logspaceused <> 0 
      then (logspaceused - prev_logspacedused) * 100.0 / prev_logspacedused 
   end as pct_growth
from (
	select LogDT, logspaceused
	, coalesce((
	   select top 1 logspaceused
	   from dbo.DBLogspaceHist h2
	   where h2.server_id = h1.server_id 
		and h2.database_name = h1.database_name
		and h2.LogDT < h1.LogDT
	   order by LogDT desc
	), 0) as prev_logspaceused
	from dbo.DBLogspaceHist h1
	where server_id = 3 and database_name = 'bistage'
) t
order by LogDT desc

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

<<Invalid column name 'logspaceused'.>>

This error is because of the derived table (inner most query) aliases the logspaceused column as 'sql3' but then you referred to it as 'logspaceused' in outer query.  It was my fault for copying and pasting and not commenting on or correcting that previously.

Using mwvisa's first approach. I would do it like this (see attached).

regarding >>i have a list of logfilesize detalis from last few days in a table
i am actually trying to come with old data and get the percent growth of logfile ?? <<

I'm not following you. Can you please post an example?

Declare @result1 float, @result2 float
 
select @result1 = convert(float, (select top 1 logspaceused from dbo.DBLogspaceHist
			where server_id = 3 and database_name = 'bistage'
			order by  LogDT desc))
 
select @result2 = convert(float, (select top 1  logspaceused from 
			(select top 2 logspaceused,logdt from dbo.DBLogspaceHist 
			where server_id = 3 and database_name = 'bistage'
			order by  LogDT desc )as test 
			order by  LogDT asc))
 
select @result1 - @result2

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

how to get the prev_logspacedused detalis every time
(select top2).....??

Not sure I understand your last question.  Please run my query and see what the results look like.  Then if you are wanting just the last two still, you can just add the phrase 'top(2)' in between 'select' and 'LogDT'.

hmmm, I agree with mwvisa1, your second question is not clear. Can you please post an example as well as the structure of DBLogspaceHist?

i mean how to get the 'prev_logspacedused'  values because its the same column name logspacedused right ?

do i need to use this query to get previous value (select top 2 logspaceused,logdt from dbo.EnterpriseDBLogspaceHist )

how can i compare last five days value and get the %growth??

Maybe you missed my post here -- http:#25072052.  Please run this code and see as it should have what you need to get the prev_logspacedused.  If the value is in a different table, then will need more information, but showed you an example with same table name in post I showed above.  Please review and adjust table and column names according to your actual environment.

thanks for the reply

my table structure is as below

LogDT                     Serverid            Databasename            Logsize              LogSizeUsed

09-08-10                     3                      bistage                     8986.31              1.6613  

09-08-10                     3                      bistage                     8986.31               1.6629

09-08-10                      3                      bistage                     8986.31              1.67

09-08-11                     3                       bistage                     8986.31               1.81

09-08-11                      3                      bistage                     8986.31                1.98


i want to compare last 5 or 6 values and get the %used
Here there will be no column like 'prev_logspacedused'prev_logspacedused right?
it same logspacedused column
how can i get 'prev_logspacedused value do i need to use some self join and get .......pls let me know

getting the error because there is no column 'prev_logspacedused'

Are you using SQL 2005? If so, then you can get the %used per each line.

;with CTE as (
	select LogDT, Serverid, Databasename, Logsize, LogSizeUsed, row_number() over (order by LogDT) rn
	from DBLogspaceHist
)
select a.LogDT, a.Serverid, a.Databasename, a.Logsize, a.LogSizeUsed, case when a.LogSizeUsed <> 0 then ((b.LogSizeUsed - a.LogSizeUsed)/a.LogSizeUsed) * 100 else 0 end as PercentageIncrease
from CTE a
inner join CTE b on a.ServerID = b.ServerID 
where b.rn = a.rn + 1 and a.server_id = 3 and a.database_name = 'bistage'

                                              

1:
2:
3:
4:
5:
6:
7:
8:

If you look at the code, I have "as prev_logspaceused" which is where prev_logspaceused is coming into play.  It is an alias.

Ramalda is using my same approach but for SQL Server 2005, which I would highly recommend if you have newer version of SQL as you can clearly see it is much smaller code.

My concern with your data is I don't see timestamps and you have multiple entries per day.  This will interfere with the accuracy of this report.  Is there a better column to use for sequencing of rows?

>> This will interfere with the accuracy of this report.  Is there a better column to use for sequencing of rows? <<

I agree with mwvisa1, I would just add that because in the example provided there is no better ID column, I have used the row_number() function to create it on the fly.

thanks

i am getting below result from ralmada query..... this does not look like actual result right

logpaceUsed             PercentgeIncrrease
 1.661396                           326.9147
 1.66291                             326.5259
1.670658                            324.548
1.819391                            289.8415
1.923211                            268.7969

thanks..

thanks a lot....... it looks fine now

yep i am using 2005
and logDt has timestamp

if u dont mind can u briefly explain me the query
mainly
select *,
  row_number() over (partition by server_id, database_name order by LogDT,
LogSpaceUsed) rn
  from dbo.EnterpriseDBLogspaceHist ------- what does this part get ? whats partition by?

select a.server_id, a.database_name, a.LogDT,a.logsize,
a.LogSpaceUsed AS LogspaceUsed
, cast(case
   -- avoid any divide by zero errors
   when coalesce(b.LogSpaceUsed, 0) <> 0  ----------whats  coalesce do here??
      then (a.LogSpaceUsed - b.LogSpaceUsed) * 100.0 / b.LogSpaceUsed  ---- b ? how does it take secondline here
   end as decimal(10,4)) as PercentgeIncrrease
from cte a
left join cte b on a.rn = b.rn+1 -- cte b??
  and a.server_id = b.server_id
  and a.database_name = b.database_name

thanks .............

i will remove the where clause and run against all database it works fine right??

Ok, since LogDT has timestamps then you can get rid of the second sort in the OVER statement but will explain the rest.


;with cte as (
  select *,
  -- replace LogSizeUsed with column that correctly holds order per day
  -- not needed at all if LogDT has timestamps
  row_number() over (partition by serverid, databasename order by LogDT) rn
  from DBLogspaceHist
)

The above portion of the query is a common table expression (CTE) (i.e., WITH ...) that allows you to create a virtual table of your data.  Would be same as if you took the query inside the parenthesis and made it a view or a temp table.  This gives added benefit over derived table as you can use it more than once in the outer query without a lot of ugly coding.  

The row_number() function inside this CTE is used in conjunction with the analytical over() clause to sequence your rows and give you a nice numerical field you can depend on.  In other words, I now that the row after row one has rn = 2, then rn = 3, etc.

The partition within the over() clause is the grouping.  Without this, you would get the rows sequenced as they appeared in the table by the 'order by LogDT'.  This is fine if you only have data from same serverid and database; however, the partition will treat each serverid and database combination as a new sequence, so you can have row 1 for serverid = 3 and database = 'bistage' and row 1 for serverid = 3 and database = 'test'.

For more information see this ref.: http://msdn.microsoft.com/en-us/library/ms189461.aspx.

select a.LogSizeUsed AS LogspaceUsed
, cast(case
   -- avoid any divide by zero errors
   when coalesce(b.LogSizeUsed, 0) <> 0

Coalesce is like isnull() function.  It will take the first non-null value between the columns result and literal 0.  Table aliased with 'b' identifier is representing the data in the row previous to the one we are on.  This is achieved in the join by 'a.rn = b.rn+1' which is same as 'b.rn = a.rn-1' so on row 1 the matching b would have to have a rn = 0.  Since there is no rn = 0, the previous row is NULL.  This is appropriate since your first record isn't increasing from anything so it should be NULL.

Consequently, what if there is a previous row, but the LogSizeUsed is 0.  You will get a divide by 0 error.  Therefore, I use coalesce(previous_value, 0) <> 0 to kill two birds with one stone as both NULL and 0 will be filtered out.

      then (a.LogSizeUsed - b.LogSizeUsed) * 100.0 / b.LogSizeUsed

b.LogSizeUsed is from the previous record, see above comment on how.

   end as decimal(10,4)) as PercentgeIncrrease
from cte a
left join cte b on a.rn = b.rn+1

This is the advantage of using a CTE that I declared above.  I am self joining the CTE that we aliased as cte (could be called c, t, or whatever you want -- just happens to be cte here so don't let that confuse you) to itself so to keep things from being ambiguous, one is called a and the other b.

  and a.serverid = b.serverid
  and a.database_name = b.database_name
where a.server_id = 3 and a.database_name = 'bistage'

Because of the partitioning, the results should work perfectly fine without the where clause.

;with cte as (
  select *,
  -- replace LogSizeUsed with column that correctly holds order per day
  -- not needed at all if LogDT has timestamps
  row_number() over (partition by serverid, databasename order by LogDT, LogSizeUsed) rn
  from DBLogspaceHist
) select a.*
, cast(case 
   -- avoid any divide by zero errors
   when coalesce(b.LogSizeUsed, 0) <> 0
      then (a.LogSizeUsed - b.LogSizeUsed) * 100.0 / b.LogSizeUsed 
   end as decimal(10,4)) as PercentgeIncrrease
from cte a
left join cte b on a.rn = b.rn+1
  and a.serverid = b.serverid 
  and a.database_name = b.database_name

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16: