Linear Regression for column values

This is a real challenge. I hope someone is smart enough to know how
to do this.

I have a table

TABLE1
[Column 1- 2001]
[Column 2- 2002]
[Column 3- 2003]
[Column 4 - 2004]
[Column 5 - 2005]
[Column 6 - 2006]
[Column 7 - Slope]

[2001][2002][2003][2004][2005][2006] [Slope]
[1] [2] [3] [4] [5] [6] [1]
[1.2] [.9] [4] [5] [5.4] [6.2] [?]

Slope is defined as "M" in the equation y=mx+b

I need a way a finding the linear equation that best fits the points so
I can have SQL calculate the slope.

Are there any smart people around that would know how to do this?

thankssql guy123 (stoppal@.hotmail.com) writes:

Quote:

Originally Posted by

This is a real challenge. I hope someone is smart enough to know how
to do this.
>
I have a table
>
TABLE1
[Column 1- 2001]
[Column 2- 2002]
[Column 3- 2003]
[Column 4 - 2004]
[Column 5 - 2005]
[Column 6 - 2006]
[Column 7 - Slope]
>
>
[2001][2002][2003][2004][2005][2006] [Slope]
[1] [2] [3] [4] [5] [6] [1]
[1.2] [.9] [4] [5] [5.4] [6.2] [?]
>
>
Slope is defined as "M" in the equation y=mx+b
>
I need a way a finding the linear equation that best fits the points so
I can have SQL calculate the slope.
>
Are there any smart people around that would know how to do this?

Smart? Either you did learn linear regression in school or you didn't.

I will have to admit that although I taught mathematical statistics as a
student, that I've forgotten the forumulas. But I looked them up in my
statisticcs book:

m = SUM((Xi - AVG(X))*(Yi - AVG(Y)) / SUM(SQR(Xi - AVG(X))

where Xi and Yi are the individual values for the X and Y variables.

For the computation in SQL you can of course not use SUM and AVG since
your table is turned the wrong way. The years should have been columns
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your information. I never learned the actual formula,
only how to click the linear regression button in excel.

Is there a way to transform the table into the correct format? Maybe a
tranverse function in SQL?

thanks

Erland Sommarskog wrote:

Quote:

Originally Posted by

sql guy123 (stoppal@.hotmail.com) writes:

Quote:

Originally Posted by

This is a real challenge. I hope someone is smart enough to know how
to do this.

I have a table

TABLE1
[Column 1- 2001]
[Column 2- 2002]
[Column 3- 2003]
[Column 4 - 2004]
[Column 5 - 2005]
[Column 6 - 2006]
[Column 7 - Slope]

[2001][2002][2003][2004][2005][2006] [Slope]
[1] [2] [3] [4] [5] [6] [1]
[1.2] [.9] [4] [5] [5.4] [6.2] [?]

Slope is defined as "M" in the equation y=mx+b

I need a way a finding the linear equation that best fits the points so
I can have SQL calculate the slope.

Are there any smart people around that would know how to do this?

>
Smart? Either you did learn linear regression in school or you didn't.
>
I will have to admit that although I taught mathematical statistics as a
student, that I've forgotten the forumulas. But I looked them up in my
statisticcs book:
>
m = SUM((Xi - AVG(X))*(Yi - AVG(Y)) / SUM(SQR(Xi - AVG(X))
>
where Xi and Yi are the individual values for the X and Y variables.
>
For the computation in SQL you can of course not use SUM and AVG since
your table is turned the wrong way. The years should have been columns
instead.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||sql guy123 (stoppal@.hotmail.com) writes:

Quote:

Originally Posted by

Thank you for your information. I never learned the actual formula,
only how to click the linear regression button in excel.
>
Is there a way to transform the table into the correct format? Maybe a
tranverse function in SQL?

There is an UNPIVOT operator in SQL 2005. But it's mainly syntactic
sugar, and you can easily unpivot without it:

SELECT year, value = CASE year WHEN 2001 THEN [2001]
WHEN 2002 THEN [2002]
...
END
FROM tbl
CROSS JOIN (SELECT 2001 UNION ALL
SELECT 2002 UNION ALL
...) AS year

My thought, though, was that you would redesign the table on a
permanent basis.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Line location as expression

Is it possible to move lines based on the values from dataset(trying to draw graph kinda thing by joining points using lines) ?Let me know if I am not clear here.

Thanks

Yes, it is possible if I understand you correctly and if you are willing to do some custom rendering. Check out CustomReportItem and look at the Polygons sample in the RS sample stuff.

If you go this route, take it from me you will find out it's not all about 1's and 0's <rofl>.

But, yes, you are really not being clear <g>. So maybe I've misunderstood...

>L<

sql

Line Chart

I have a line chart where I am displaying values (x axis) for a 30 day period
(y axis) for 4 different regions (series). My problem is that when the chart
is displayed it is repeating a series value although that series does only
appear 30 times. If I change the series to only display 1 region the problem
still occurs. I think it may have something to do with zero values returned
in dataset ie
Day Region value
1 A 0
2 A 1.2
3 A 0
4 A 1.5
If I put a filter on to start at day 2 then I do not have the problem,
however I don't neccessarily know what day would be the first one without a
zero value.
I can return values only above zero but I'd prefer to plot that zero value
on the graph.Assuming you have RS 2000 SP2 installed, you can try the following:
Change the dataset (or use a calculated field or use an IIF expression in
the chart value expression), so that 0 is represented as NULL in the dataset
(or as VB: Nothing in the RDL expression). Then the "zero" value datapoints
will be interpreted as "empty" points, but on the other hand you should
still get the x-axis starting with 1.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Me and SRS" <Me and SRS@.discussions.microsoft.com> wrote in message
news:D087DFC5-1822-4685-819A-4D768FD80618@.microsoft.com...
>I have a line chart where I am displaying values (x axis) for a 30 day
>period
> (y axis) for 4 different regions (series). My problem is that when the
> chart
> is displayed it is repeating a series value although that series does only
> appear 30 times. If I change the series to only display 1 region the
> problem
> still occurs. I think it may have something to do with zero values
> returned
> in dataset ie
> Day Region value
> 1 A 0
> 2 A 1.2
> 3 A 0
> 4 A 1.5
> If I put a filter on to start at day 2 then I do not have the problem,
> however I don't neccessarily know what day would be the first one without
> a
> zero value.
> I can return values only above zero but I'd prefer to plot that zero value
> on the graph.|||Thanks for your help and to a degree this works, but I want to plot the zero
value when it shows even if it is the first value. For some regions I may
not get a reading till the 10th day so in the chart that line will now not
appear till half way thru the graph and depending on circumstances may end on
the 25th day. Originally I am plotting the zero values but just get
duplicates for the series.
We are using RS 2000 SP2.
Thanks
"Robert Bruckner [MSFT]" wrote:
> Assuming you have RS 2000 SP2 installed, you can try the following:
> Change the dataset (or use a calculated field or use an IIF expression in
> the chart value expression), so that 0 is represented as NULL in the dataset
> (or as VB: Nothing in the RDL expression). Then the "zero" value datapoints
> will be interpreted as "empty" points, but on the other hand you should
> still get the x-axis starting with 1.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Me and SRS" <Me and SRS@.discussions.microsoft.com> wrote in message
> news:D087DFC5-1822-4685-819A-4D768FD80618@.microsoft.com...
> >I have a line chart where I am displaying values (x axis) for a 30 day
> >period
> > (y axis) for 4 different regions (series). My problem is that when the
> > chart
> > is displayed it is repeating a series value although that series does only
> > appear 30 times. If I change the series to only display 1 region the
> > problem
> > still occurs. I think it may have something to do with zero values
> > returned
> > in dataset ie
> > Day Region value
> > 1 A 0
> > 2 A 1.2
> > 3 A 0
> > 4 A 1.5
> > If I put a filter on to start at day 2 then I do not have the problem,
> > however I don't neccessarily know what day would be the first one without
> > a
> > zero value.
> > I can return values only above zero but I'd prefer to plot that zero value
> > on the graph.
>
>|||I seem to have fixed the problem. Even though dataset returned the correct
amount of records (4 * 30) I was still getting duplicate rows in the series.
Had to use the TRIM function on the series field in the chart and just like
that the duplicates disappeared.
Thanks for your help.
"Me and SRS" wrote:
> Thanks for your help and to a degree this works, but I want to plot the zero
> value when it shows even if it is the first value. For some regions I may
> not get a reading till the 10th day so in the chart that line will now not
> appear till half way thru the graph and depending on circumstances may end on
> the 25th day. Originally I am plotting the zero values but just get
> duplicates for the series.
> We are using RS 2000 SP2.
> Thanks
> "Robert Bruckner [MSFT]" wrote:
> > Assuming you have RS 2000 SP2 installed, you can try the following:
> >
> > Change the dataset (or use a calculated field or use an IIF expression in
> > the chart value expression), so that 0 is represented as NULL in the dataset
> > (or as VB: Nothing in the RDL expression). Then the "zero" value datapoints
> > will be interpreted as "empty" points, but on the other hand you should
> > still get the x-axis starting with 1.
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Me and SRS" <Me and SRS@.discussions.microsoft.com> wrote in message
> > news:D087DFC5-1822-4685-819A-4D768FD80618@.microsoft.com...
> > >I have a line chart where I am displaying values (x axis) for a 30 day
> > >period
> > > (y axis) for 4 different regions (series). My problem is that when the
> > > chart
> > > is displayed it is repeating a series value although that series does only
> > > appear 30 times. If I change the series to only display 1 region the
> > > problem
> > > still occurs. I think it may have something to do with zero values
> > > returned
> > > in dataset ie
> > > Day Region value
> > > 1 A 0
> > > 2 A 1.2
> > > 3 A 0
> > > 4 A 1.5
> > > If I put a filter on to start at day 2 then I do not have the problem,
> > > however I don't neccessarily know what day would be the first one without
> > > a
> > > zero value.
> > > I can return values only above zero but I'd prefer to plot that zero value
> > > on the graph.
> >
> >
> >

Line 1: Incorrect syntax near 're'.

Hi Guys,
This is my Sql Query to insert values. All the fields are nvarchar
except the postcode which is Int. I have a condition if the postcode
field is numeric then insert values otherwise error message. Only one
of the customer gets the message on his browser
"Line 1: Incorrect syntax near 're'.
/westernsydney/register.asp, line 198 "
But other registrations are still going through with this code. Can
someone tell me why this is happening
strSql5 = "INSERT INTO tblattendeem
(attendee_firstname,attendee_surname,att
endee_location_street1,attendee_loca
tion_suburb,attendee_location_postcode,a
ttendee_company_name,attendee_phone,
attendee_email,event_id)"_
& "VALUES
('"&Request.QueryString("firstname")&"','"&Request.QueryString("surname")&"'
,"_
&"
'"&Request.QueryString("address")&"','"&Request.QueryString("suburb")&"',"_
&" '"&Request.QueryString("postcode")&"',
'"&Request.QueryString("company")&"',"_
&" '"&Request.QueryString("phone_number")&"',
'"&Request.QueryString("email")&"',"_
&" '"&Request.Cookies("event_id")&"' )"
Many ThanksHey Nachi, without looking at your sample data, it's difficult to tell
what's going on. My guess is that you have a customer with either a
quote (") or an apostrophe (') in the column.
There are other issues that you be aware of, however; building a string
like you are doing increases the chances that someone will be able to
inject another SQL statement into your code, thus doing damange to your
application. See http://aspalliance.com/385 for an example.
Also, do any of your postal codes start with a 0? If so, you may want
reconsider using an integer to store the data.
HTH,
Stu|||Hi Stu,
Thank you so much for your time & Quick reply. This Groups is really
useful. Im waiting on the Data from this specific customer. thank you
for your suggestion as well on Sql Injection. I will impl this in
future.
K Regards,
Nachi
Stu wrote:
> Hey Nachi, without looking at your sample data, it's difficult to tell
> what's going on. My guess is that you have a customer with either a
> quote (") or an apostrophe (') in the column.
> There are other issues that you be aware of, however; building a string
> like you are doing increases the chances that someone will be able to
> inject another SQL statement into your code, thus doing damange to your
> application. See http://aspalliance.com/385 for an example.
> Also, do any of your postal codes start with a 0? If so, you may want
> reconsider using an integer to store the data.
> HTH,
> Stu|||To emphasize Stu's point, there are hackers who do nothing all day but
search for web sites to exploit SQL injection vulnerabilities. If your site
is exposed to the public internet, be sure to heed Stu's advice use only
parameterized SQL queries. I hope that 'in the future' means now.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nachi" <Rengenath@.gmail.com> wrote in message
news:1148612435.121757.148630@.j33g2000cwa.googlegroups.com...
> Hi Stu,
> Thank you so much for your time & Quick reply. This Groups is really
> useful. Im waiting on the Data from this specific customer. thank you
> for your suggestion as well on Sql Injection. I will impl this in
> future.
> K Regards,
> Nachi
> Stu wrote:
>

Limits SQLServer Express

Hi, can someone explain which are the limits o maximum values that SQLServer Express permit?

I dont see anywhere a compare table in which it says, 10 concurrent connections, etc.

Thanks in advance.

The only limitations that apply are shown here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

There is no longer a workload governor.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Limiting the amount of data used between two tables

Two tables: People and TestValues
People
--
Name varchar(50)
TestValues
--
TestString varchar(10)
Insert into People (Name) values ('William')
Insert into People (Name) values ('Blake')
Insert into People (Name) values ('Kevin')
Insert into People (Name) values ('Merry')
Insert into People (Name) values ('Gary')
Insert into People (Name) values ('Tamara')
Insert into People (Name) values ('Rich')
insert into TestValues (testString) values ('A')
insert into TestValues (testString) values ('M')
insert into TestValues (testString) values ('K')
insert into TestValues (testString) values ('X')
insert into TestValues (testString) values ('Z')
The goal is to have each table filter the other, so that we only return
those items that they have in common. So:
'William' has an "A", so WIlliam and A both are included (there is also
an M, but A is a match so M is not necessary)
"Blake" has an "A", so both are included
"Kevin" has a "K"
"Merry" matches "M"
"Gary" matches "A"
"Rich" doesn't match anything, and isn't included
"X" and "Z" don't match, and aren't included
I would rather not create two cursors and two temp tables if there is a
set based way to do this. Thanks!
- Wm in Kansas Cityselect p.name, min(t.teststring) as teststring
from people p
join testvalues t on p.name like '%'+t.teststring+'%'
group by p.name
this probably won't perform very well, especially if the amount of data
gets large.
seamlyne@.hotmail.com wrote:
> Two tables: People and TestValues
> People
> --
> Name varchar(50)
>
> TestValues
> --
> TestString varchar(10)
>
> Insert into People (Name) values ('William')
> Insert into People (Name) values ('Blake')
> Insert into People (Name) values ('Kevin')
> Insert into People (Name) values ('Merry')
> Insert into People (Name) values ('Gary')
> Insert into People (Name) values ('Tamara')
> Insert into People (Name) values ('Rich')
>
> insert into TestValues (testString) values ('A')
> insert into TestValues (testString) values ('M')
> insert into TestValues (testString) values ('K')
> insert into TestValues (testString) values ('X')
> insert into TestValues (testString) values ('Z')
> The goal is to have each table filter the other, so that we only return
> those items that they have in common. So:
> 'William' has an "A", so WIlliam and A both are included (there is also
> an M, but A is a match so M is not necessary)
> "Blake" has an "A", so both are included
> "Kevin" has a "K"
> "Merry" matches "M"
> "Gary" matches "A"
> "Rich" doesn't match anything, and isn't included
> "X" and "Z" don't match, and aren't included
> I would rather not create two cursors and two temp tables if there is a
> set based way to do this. Thanks!
> - Wm in Kansas City
>|||select * from #people join #TestValues on charindex(testString, Name)>0

Limitation on Multiple parameters with Multi-Select and default values

Dear Anyone,

I created 6 parameters in my report. Each of these parameters are multi-select enabled. Each of their available values comes from a data set. Their default values also comes from a data set in order to have the select all check box selected by default.

My problem is only the first 2 parameters gets to have the select all selected. And the rest just have blank values. I was wondering if RS parameters have some sort of limitation that I cannot enable data set default values in all of my parameters.
Thanks,
JosephThere seems to be a bug in RS2005 in the parameters section. Filter the data set your using for the parameters to not include blanks. This is aparently whats causing your problem.|||

Hello,

I need to create reports with parameters that allow for multi-selection values.

How can I make them multi-select enabled?

Appreciate your reponse.

SK

Limitation on Multiple parameters with Multi-Select and default values

Dear Anyone,

I created 6 parameters in my report. Each of these parameters are multi-select enabled. Each of their available values comes from a data set. Their default values also comes from a data set in order to have the select all check box selected by default.

My problem is only the first 2 parameters gets to have the select all selected. And the rest just have blank values. I was wondering if RS parameters have some sort of limitation that I cannot enable data set default values in all of my parameters.
Thanks,
JosephThere seems to be a bug in RS2005 in the parameters section. Filter the data set your using for the parameters to not include blanks. This is aparently whats causing your problem.|||

Hello,

I need to create reports with parameters that allow for multi-selection values.

How can I make them multi-select enabled?

Appreciate your reponse.

SK