Batch update of a SQL table

Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.

I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.

In a seperate table I have a charge per day for late returns. I read this into a variable @.LateCharges

I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLatecolumn*@.Latecharges

Any help much appreciated.

Regards

Clive

UPDATE Loans
SET CHARGES = CHARGES + @.LateCharges
WHERE Status <> 'Returned'

|||

Hi

That would almost do it I think - however it would need to read the value of DaysLate in each case too - can I just use that name in my set statement and it woudl automatically be the one applicable to the current row?

ie:

UPDATE Loans
SET CHARGES = DaysLate * @.LateCharges
WHERE Status <> 'Returned'

|||

It sounds like you need to do a join. rather than read just one value into a variable. Please post the table definitions.

Basic, perhaps silly SQLServer 2005 question

I've previously used version 7 and 2000.

I've recently started to learn ASP.net. I've installed
Visual Web Developer 2005 Express and this includes
SQLServer 2005 in the installation. The task manager
and and the list of services definitely show I've got
SQLServer running on my computer.

Here's the problem: how do I actually use it? I can't
find any tools to create and access databases.

I've tried installing the Enterprise Manager from 2000
but this gives me an error message saying I must use the 2005
tools to connect.

So, is there an Enterprise Manager (or similar) for 2005?
Where do I find it?Download Microsoft SQL Server Management Studio Express from
http://www.microsoft.com/downloads/...&DisplayLang=en

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145465194.532705.252780@.u72g2000cwu.googlegr oups.com...
> Download Microsoft SQL Server Management Studio Express from
> http://www.microsoft.com/downloads/...&DisplayLang=en

Thanks, thats great

Basic steps as how to web usage mine in sql server 2005

Hi there,

I am doing a project on web usage mining of my universities server logs and im just wondering how i go about mining them in sql server 2005?

Do i mine them in one table? do i normalise the web log data? what algorithms will i use on them as im trying to get usage patterns from the users and also where most of the users come from.

Thanks in advance

Gary

Hi

Here’re some thoughts that might help you design your Data Mining for weblogs:

1. You can put the data in one or more tables as per the semantics of the data. Data which is an entity by itself should be put in a flat table with one key per entry (user sessions on the web server for example), whereas some data naturally will have a many to one relation with the primary data (page visits per session for example) and can be put in a separate table with primary/foreign key relationship. SQL Server 2005 will model them as case table/nested table for the purpose of mining.

2. Normalize: Depends on what your data looks like. If you want to run clustering algorithm and your data has two attributes, A and B and A is 10 times more important than B, you should normalize accordingly. If the actual values of A are in order of thousand and actual values ob B are in order of tens and they are equally important, you should again normalize. However, if you want to use the as-is value without a weight, you do not have to normalize the data.

3. Usage Patterns, like a sequence of page visits can be modeled using sequence clustering algorithm. User categorization based on attributes might be a clustering algorithm. If you have more information on what you want to find out, I might be able to suggest more specific choices.

Hope this helps

Shuvro

basic RS questions

Hi,
those things I can't find out:
1. What means "Export" to "HTML with Office web components"? It looks just
like the orginal report viewer to me, ie confusing as the first export format.
2. Is it possible to re-sort a report table on "any" report column in the
report viewer or do I have to export to excel ?
3. Where do I declare "document map pane" items ? Can't find any props.
4. Can History page be hidden in the viewer when props for this report says
it can't generate History ?
5. Is it possible to stop users (in security) to do subscriptions and if so,
will those pages/buttons dissappear in the viewer ?
/Jerome KI can help with some of this... see inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:0C8E29BE-0140-4F67-AC3A-358FC894CA94@.microsoft.com...
> Hi,
> those things I can't find out:
> 1. What means "Export" to "HTML with Office web components"? It looks just
> like the orginal report viewer to me, ie confusing as the first export
> format.
This exports to a file that you can place on some other web site...
> 2. Is it possible to re-sort a report table on "any" report column in the
> report viewer or do I have to export to excel ?
If there are no groupings you can do this... Ihave some examples using SQL
2000 at www.msbicentral.com It is kind of a pain to do in SQL 2k , but SQL
2005 makes it easy. (If there are groupings you can still do it, but it
becomes more complicated.)
> 3. Where do I declare "document map pane" items ? Can't find any props.
right click on any Group, text box etc. and when the dialog box opens, go to
the navigation tab... use the document map box to enter the field info.
> 4. Can History page be hidden in the viewer when props for this report
> says
> it can't generate History ?
I Don't know..
> 5. Is it possible to stop users (in security) to do subscriptions and if
> so,
> will those pages/buttons dissappear in the viewer ?
Yes it is possible to NOT allow subscriptions, RS is role based security and
one of the privileges is whether or not a user can make subscriptions...
Simply create a new role (BrowserNoSuscriptions)... and add every permission
that a Browser role has except subscriptions and you will be good.
I do not know if the buttons disappear or not... You'll have to test it and
see.
> /Jerome K
>