extrapolate results out? - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

extrapolate results out?

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2008-09-16 : 08:45:07

Hi,

I've got a table that logs the amount of free diskspace on a pc or servers physical disks (a bit like this one).



create table #disks
(computername varchar(20),
drive varchar(3),
[size] int,
freespace int,
datestamp datetime)

insert into #disks values('computer 1','c:',40000,40000,'01/12/2007')
insert into #disks values('computer 1','c:',40000,39500,'15/12/2007')
insert into #disks values('computer 1','c:',40000,39500,'28/12/2007')
insert into #disks values('computer 1','c:',40000,39500,'01/01/2008')
insert into #disks values('computer 1','c:',40000,37000,'15/01/2008')
insert into #disks values('computer 1','c:',40000,36000,'28/01/2008')
insert into #disks values('computer 1','c:',40000,35000,'01/02/2008')
insert into #disks values('computer 1','c:',40000,34000,'15/02/2008')
insert into #disks values('computer 1','c:',40000,33000,'28/02/2008')
insert into #disks values('computer 1','c:',40000,32000,'01/03/2008')
insert into #disks values('computer 1','c:',40000,31000,'15/03/2008')
insert into #disks values('computer 1','c:',40000,30000,'28/03/2008')
insert into #disks values('computer 1','c:',40000,29000,'01/04/2008')
insert into #disks values('computer 1','c:',40000,28000,'15/04/2008')
insert into #disks values('computer 1','c:',40000,27000,'28/04/2008')
insert into #disks values('server 1','c:',100000,99000,'01/12/2007')
insert into #disks values('server 1','c:',100000,95000,'15/12/2007')
insert into #disks values('server 1','c:',100000,91000,'28/12/2007')
insert into #disks values('server 1','c:',100000,87000,'01/01/2008')
insert into #disks values('server 1','c:',100000,83000,'15/01/2008')
insert into #disks values('server 1','c:',100000,79000,'28/01/2008')
insert into #disks values('server 1','c:',100000,75000,'01/02/2008')
insert into #disks values('server 1','c:',100000,71000,'15/02/2008')
insert into #disks values('server 1','c:',100000,67000,'28/02/2008')
insert into #disks values('server 1','c:',100000,63000,'01/03/2008')
insert into #disks values('server 1','c:',100000,59000,'15/03/2008')
insert into #disks values('server 1','c:',100000,55000,'28/03/2008')
insert into #disks values('server 1','c:',100000,51000,'01/04/2008')
insert into #disks values('server 1','c:',100000,47000,'15/04/2008')
insert into #disks values('server 1','c:',100000,43000,'28/04/2008')
insert into #disks values('server 1','z:',100000,98000,'01/12/2007')
insert into #disks values('server 1','z:',100000,96000,'15/12/2007')
insert into #disks values('server 1','z:',100000,94000,'28/12/2007')
insert into #disks values('server 1','z:',100000,92000,'01/01/2008')
insert into #disks values('server 1','z:',100000,90000,'15/01/2008')
insert into #disks values('server 1','z:',100000,88000,'28/01/2008')
insert into #disks values('server 1','z:',100000,86000,'01/02/2008')
insert into #disks values('server 1','z:',100000,84000,'15/02/2008')
insert into #disks values('server 1','z:',100000,82000,'28/02/2008')
insert into #disks values('server 1','z:',100000,80000,'01/03/2008')
insert into #disks values('server 1','z:',100000,78000,'15/03/2008')
insert into #disks values('server 1','z:',100000,76000,'28/03/2008')
insert into #disks values('server 1','z:',100000,74000,'01/04/2008')
insert into #disks values('server 1','z:',100000,72000,'15/04/2008')
insert into #disks values('server 1','z:',100000,70000,'28/04/2008')
insert into #disks values('server 1','e:',100000,95000,'01/12/2007')
insert into #disks values('server 1','e:',100000,90000,'15/12/2007')
insert into #disks values('server 1','e:',100000,85000,'28/12/2007')
insert into #disks values('server 1','e:',100000,80000,'01/01/2008')
insert into #disks values('server 1','e:',100000,75000,'15/01/2008')
insert into #disks values('server 1','e:',100000,70000,'28/01/2008')
insert into #disks values('server 1','e:',100000,65000,'01/02/2008')
insert into #disks values('server 1','e:',100000,60000,'15/02/2008')
insert into #disks values('server 1','e:',100000,55000,'28/02/2008')
insert into #disks values('server 1','e:',100000,50000,'01/03/2008')
insert into #disks values('server 1','e:',100000,45000,'15/03/2008')
insert into #disks values('server 1','e:',100000,40000,'28/03/2008')
insert into #disks values('server 1','e:',100000,35000,'01/04/2008')
insert into #disks values('server 1','e:',100000,30000,'15/04/2008')
insert into #disks values('server 1','e:',100000,25000,'28/04/2008')
insert into #disks values('computer 2','c:',40000,40000,'01/12/2007')
insert into #disks values('computer 2','c:',40000,40000,'15/12/2007')
insert into #disks values('computer 2','c:',40000,40000,'28/12/2007')
insert into #disks values('computer 2','c:',40000,38000,'01/01/2008')
insert into #disks values('computer 2','c:',40000,36000,'15/01/2008')
insert into #disks values('computer 2','c:',40000,24000,'28/01/2008')
insert into #disks values('computer 2','c:',40000,19000,'01/02/2008')
insert into #disks values('computer 2','c:',40000,18000,'15/02/2008')
insert into #disks values('computer 2','c:',40000,17500,'28/02/2008')
insert into #disks values('computer 2','c:',40000,17000,'01/03/2008')
insert into #disks values('computer 2','c:',40000,16500,'15/03/2008')
insert into #disks values('computer 2','c:',40000,16000,'28/03/2008')
insert into #disks values('computer 2','c:',40000,15500,'01/04/2008')
insert into #disks values('computer 2','c:',40000,15000,'15/04/2008')
insert into #disks values('computer 2','c:',40000,14500,'28/04/2008')

First, I need to get the amount of disk space remaining at one point in time each month. I went for the last recorded day each month, like this:


select
d.computername,
d.drive,
d.freespace,
d.datestamp,
d.[size]
from 

#disks d 

join
	(select 
	computername,
	drive,
	max(datestamp) as datestamp
	from #disks
	group by computername, drive, datepart(mm, datestamp), datepart(yy, datestamp)) as monthlyfreespace

on d.computername = monthlyfreespace.computername 
and d.drive = monthlyfreespace.drive 
and d.datestamp = monthlyfreespace.datestamp

If anyones got a more efficient way of doing this bit, then that would be cool.

The bit that I'm stuck on is that I need to be able to extrapolate a result based on previous results - based on the rate the disk space is getting used up, I need to figure out that if it continues at the current rate, how long before it hits zero?

e.g. computer 1 decreases by 500mb per month, so starting with a disk free space of 40,000, its going to run out of space in 80 months (ish).

To be honest, I don't know if I'm asking for help with the maths or the SQL, but a bit of assistance with both would be good

Cheers,

Yonabout

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 10:02:57

[code]-- Prepare sample data
SET NOCOUNT ON
GO
CREATE TABLE Disks
(
ComputerName VARCHAR(20),
Drive VARCHAR(3),
[Size] BIGINT,
FreeSpace BIGINT,
Datestamp DATETIME,
IsLess BIT DEFAULT 0,
IsLast BIT DEFAULT 0
)
GO
CREATE TRIGGER trgDisk ON Disks
AFTER INSERT
AS

UPDATE d
SET d.IsLast = CASE
WHEN d.Datestamp = w.theLast THEN 1
ELSE 0
END,
d.IsLess = CASE
WHEN d.FreeSpace = w.theLess THEN 1
ELSE 0
END
FROM Disks AS d
INNER JOIN (
SELECT d.ComputerName,
d.Drive,
DATEDIFF(MONTH, '19000101', d.Datestamp) AS theMonth,
MAX(d.Datestamp) AS theLast,
MIN(d.FreeSpace) AS theLess
FROM Disks AS d
INNER JOIN inserted AS i ON i.ComputerName = d.ComputerName
AND i.Drive = d.Drive
GROUP BY d.ComputerName,
d.Drive,
DATEDIFF(MONTH, '19000101', d.Datestamp)
) AS w ON w.ComputerName = d.ComputerName
AND w.Drive = d.Drive
WHERE DATEDIFF(MONTH, '19000101', d.Datestamp) = w.theMonth
GO

-- insert sample data
set dateformat dmy

insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,40000,'01/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,39500,'15/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,39500,'28/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,39500,'01/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,37000,'15/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,36000,'28/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,35000,'01/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,34000,'15/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,33000,'28/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,32000,'01/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,31000,'15/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,30000,'28/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,29000,'01/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,28000,'15/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 1','c:',40000,27000,'28/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,99000,'01/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,95000,'15/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,91000,'28/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,87000,'01/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,83000,'15/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,79000,'28/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,75000,'01/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,71000,'15/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,67000,'28/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,63000,'01/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,59000,'15/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,55000,'28/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,51000,'01/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,47000,'15/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','c:',100000,43000,'28/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,98000,'01/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,96000,'15/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,94000,'28/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,92000,'01/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,90000,'15/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,88000,'28/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,86000,'01/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,84000,'15/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,82000,'28/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,80000,'01/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,78000,'15/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,76000,'28/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,74000,'01/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,72000,'15/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','z:',100000,70000,'28/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,95000,'01/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,90000,'15/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,85000,'28/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,80000,'01/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,75000,'15/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,70000,'28/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,65000,'01/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,60000,'15/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,55000,'28/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,50000,'01/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,45000,'15/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,40000,'28/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,35000,'01/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,30000,'15/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('server 1','e:',100000,25000,'28/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,40000,'01/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,40000,'15/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,40000,'28/12/2007')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,38000,'01/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,36000,'15/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,24000,'28/01/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,19000,'01/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,18000,'15/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,17500,'28/02/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,17000,'01/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,16500,'15/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,16000,'28/03/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,15500,'01/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,15000,'15/04/2008')
insert into disks (ComputerName, Drive, [Size], FreeSpace, Datestamp) values('computer 2','c:',40000,14500,'28/04/2008')
GO

-- show last measure point each month
select computername,
drive,
[size],
freespace,
datestamp
from disks
where islast = 1

-- show measure point for less space each month
select computername,
drive,
[size],
freespace,
datestamp
from disks
where isless = 1

-- Now the TRICK! Calculate when space is zero!
declare @today datetime
set @today = datediff(day, '19000101', getdate())

select computername,
drive,
[size],
freespace,
datestamp,
1.0 * datediff(MINUTE, @today, datestamp) AS x,
cast(0 as float) AS n,
cast(0 as float) AS m,
cast(0 as float) AS b
into #temp
from disks

update w
set w.n = (select count(*) From #temp as t where t.computername = w.computername and t.drive = w.drive)
from #temp as w

update w
set w.m = r.m,
w.b = r.b
from #temp AS w
inner join (
select computername, drive,
((min(n) * sum(x*freespace)) - (sum(x)*sum(freespace)))/
((min(n) * sum(Power(x,2)))-Power(Sum(x),2)) AS M,
avg(freespace) - ((min(n) * sum(x*freespace)) - (sum(x)*sum(freespace)))/
((min(n) * sum(Power(x,2)))-Power(Sum(x),2)) * avg(x) as B
from #temp
group by computername, drive
) AS r ON r.computername = w.computername
and r.drive = w.drive

-- Show the result
select distinct computername,
drive,
DATEADD(MINUTE, - b / m, @today) AS theEnd
FROM #temp

-- clean up
DROP TABLE Disks,
#Temp[/code]

E 12°55'05.63"
N 56°04'39.26"

yonabout
Posting Yak Master

112 Posts

Posted - 2008-09-16 : 10:40:20

Wow.

Its going to take me a while to work through this, but thanks in advance!

Cheers,

Yonabout

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 10:47:36

This is the part what you need to concentrate on.

-- Get today's date without time
declare @today datetime
set @today = datediff(day, '19000101', getdate())

-- get the data to work with
select	computername,
	drive,
	freespace,
	cast(datediff(MINUTE, @today, datestamp) AS float) AS x,
	cast(0 as float) AS n,
	cast(0 as float) AS m,
	cast(0 as float) AS b
into	#temp
from	disks

-- how many data for each computer and drive?
update	w
set	w.n = (select count(*) From #temp as t where t.computername = w.computername and t.drive = w.drive)
from	#temp as w

-- calculate linear regression
update		w
set		w.m = r.m,
		w.b = r.b
from		#temp AS w
inner join	(
			select  computername, drive,
				((min(n) * sum(x*freespace)) - (sum(x)*sum(freespace)))/
				((min(n) * sum(Power(x,2)))-Power(Sum(x),2)) AS M,
				avg(freespace) - ((min(n) * sum(x*freespace)) - (sum(x)*sum(freespace)))/
				((min(n) * sum(Power(x,2)))-Power(Sum(x),2)) * avg(x) as B
			from #temp
			group by computername, drive
		) AS r ON r.computername = w.computername
			and r.drive = w.drive

-- Show the result
select	distinct computername,
	drive,
	DATEADD(MINUTE, - b / m, @today) AS theEnd
FROM	#temp

-- clean up
DROP TABLE	#Temp

E 12°55'05.63"
N 56°04'39.26"

yonabout
Posting Yak Master

112 Posts

Posted - 2008-09-16 : 11:28:12

I figured that was the business end of things - the triggers just providing us with the last entry per month and entries where the free space has decreased?

Looks like I need to brush up on my linear regression theory!

Thanks for your help - don't think I mentioned it worked a treat, even though I don't understand how yet.

Cheers

Yonabout

iupmike426
Starting Member

3 Posts

Posted - 2008-10-08 : 09:44:43

Peso,

What formula is this based on? I'm trying to do a similar calculation to project when a drive's free space will hit a threshold above zero (eg. 1 gig). I'm just having problems understanding your calculations enough to adjust for this threshold.

Thanks in advance,
Mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 10:00:12

Least square method.

E 12°55'05.63"
N 56°04'39.26"

iupmike426
Starting Member

3 Posts

Posted - 2008-10-09 : 08:30:30

Any idea how I would solve this for y = {a given threshold} instead of y=0? I'd like to warn when the free space is a few days away from a predetermined threshold instead of zero.

I've wrestled with this equation for some time now to no avail.

Thanks again,
Mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 08:39:30

The least squares method will give you an equation of

y = mx + b, where x is timeline and m and b are calculated in the table.

For y = 0 (zero bytes) gives

-b / m = x, which is what is used in DATEDIFF function.

If you want to know when there is 100 MB left,

100000000 = mx + b
(100000000 - b) / m = x

so the select should be

select	distinct computername,
	drive,
	DATEADD(MINUTE, (100000000 - b) / m, @today) AS theEnd
FROM	#temp

For 50 MB, it should be

select	distinct computername,
	drive,
	DATEADD(MINUTE, (50000000 - b) / m, @today) AS theEnd
FROM	#temp

You MUST use same scale for the threshold in the formula as the data in tables are!
If FreeSpace are in KB, the threshold in the formula also must be KB.

E 12°55'05.63"
N 56°04'39.26"

iupmike426
Starting Member

3 Posts

Posted - 2008-10-09 : 10:50:06

PERFECT!

Thank you very much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 10:58:44

Also see this post I made a long time ago
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77311

The function there is slightly better, because it include the r2 variable which holds a value in percent how accurate the estimate is compared with the set of datapoints.

If r2 is 0.993 it means the least sqaure method is accurate to 99.3% compared to the set of datapoints.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-26 : 10:35:03

If you have migrated to SQL Server 2005 or later, see this query!

SELECT		computername,
		drive,
		dbo.LeastSquare(STR(datediff(day, 0, datestamp), 38, 10) + ',' + STR(freespace, 38, 10))
FROM		#disks
group by	computername,
		drive

which returns this xml result

computername	drive	(No column name)
computer 1	c:	<dws bestfit="linear" r2="0.982804437026271"><linear A="3849737.82474124" b="-96.6249018670084" r2="0.982804437026271" y="A+b*x" x="(y-A)/b" /><exponential A="6.73175412624164E+53" b="-0.00287458498023942" r2="0.979780232581423" /><logarithmic A="40417446.6272144" b="-3815587.79241381" r2="0.982761696027703" /><power A="Infinity" b="-113.510496380187" r2="0.979592809462072" /></dws>
computer 2	c:	<dws bestfit="power" r2="0.865707435343337"><linear A="8421172.43884172" b="-212.629377633799" r2="0.824361936769858" /><exponential A="6.14055645991933E+148" b="-0.00842182517293328" r2="0.865511077372201" /><logarithmic A="88906241.7420077" b="-8397908.65324008" r2="0.82461210380589" /><power A="Infinity" b="-332.610491498941" r2="0.865707435343337" /></dws>
server 1	c:	<dws bestfit="linear" r2="0.996151502836827"><linear A="15417024.6708597" b="-388.606646874633" r2="0.996151502836827" /><exponential A="1.91748607601782E+102" b="-0.00568185255264517" r2="0.983005163559512" /><logarithmic A="162488444.709836" b="-15345855.3591809" r2="0.996146227797154" /><power A="Infinity" b="-224.359953339695" r2="0.982908845008411" /></dws>
server 1	e:	<dws bestfit="linear" r2="0.996151502836775"><linear A="19242530.8385746" b="-485.758308593292" r2="0.996151502836775" /><exponential A="4.07666455702034E+156" b="-0.00885494435120419" r2="0.964434765269131" /><logarithmic A="203081805.888317" b="-19182319.1989237" r2="0.996146451026786" /><power A="Infinity" b="-349.644443697597" r2="0.964256315381241" /></dws>
server 1	z:	<dws bestfit="linear" r2="0.996151502837263"><linear A="7757012.33542985" b="-194.303323437317" r2="0.996151502837263" /><exponential A="6.91466407816874E+44" b="-0.00232753782210824" r2="0.993700848071005" /><logarithmic A="81292722.3565575" b="-7672927.67969527" r2="0.996146736914063" /><power A="Infinity" b="-91.9109452704864" r2="0.993709730868877" /></dws>

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources