Updating Hits Without Looping
Sep 6, 2007
Hi.
This is my first attempt at a using stored procedures and I'm a bit confused.
I'm trying to follow as many best practices as I can to improve speed, security and scalability. However, I can't find a solution to what I think should be a simple problem.
I have a search page where users enter the criteria of properties they are interested in (bedrooms, price etc...). That takes them to a results page where the properties are displayed.
The problem is that I want the amount of times each property has been shown on the results page to be tracked so the property owner gets statistics.
The property details are all held in a single table along with the amount of times each property has be shown:
Table Name: zk_Property_USA
ID
INT
User_ID
INT
Property_Type
TINYINT
Market_Status
TINYINT
Price
INT
Bedrooms
TINYINT
Address_State
VARCHAR
Address_Location
VARCHAR
Property_Description
VARCHAR
Searched
INT
0
Contacted
INT
0
I'm trying to find a way to SELECT all the property details to be returned to my results page and UPDATE the "Searched" field by 1 without re-scanning the table for the UPDATE.
Is there a way to update "Searched" at the time when the record is chosen to be a result? I am using SQL Server 2005 and Visual Basic ASP.NET 2.0.
Many Thanks
View 4 Replies
ADVERTISEMENT
Nov 8, 2007
Hello, Its hard trying to explain this.
I have 3 tables
Table 1 is where the users are stored, each user has a username and a userrank
Table 2 is where the points that decides the userrank are stored
Table 3 contains the available userranks like this
Table 1 (user_list) looks briefly like this:username nvarchar(20),userrank int, -- Reference to Table3 id... alot more fields
Table 2 (settings_profile) looks like this:username nvarchar(20),total_active_points int,... some more fields
Table 3 (data_ranks) looks like this:id int primary key auto inc,rankname nvarchar(20),min_pts int,max_pts int
Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:
WHILE (SELECT @username = username, @id = id, @temp1 = ap_sentmails, @temp2 = ap_createdthreads, @temp3 = ap_createdanswers, @temp4 = ap_signguestbook, @temp5 = ap_blogcomment, @temp6 = ap_createblogentry, @temp7 = ap_profilefirsttime, @temp8 = ap_profilephoto, @temp9 = ap_activateguestbook, @temp10 = ap_addnewfriend, @temp11 = ap_superguruvote, @temp12 = ap_forumtopicvote, @temp13 = ap_labervote, @temp14 = ap_funstuffitemvote, @temp15 = ap_movievote, @temp16 = ap_actorvote, @temp17 = ap_money_new WHERE (created < Dateadd(dd, -7, @todaysdate))BEGINSET @sum = 0SET @sum = @temp1 + @temp2 + @temp3 + @temp4 + @temp5 + @temp6 + @temp7 + @temp8 + @temp9 + @temp10 + @temp11 + @temp12 + @temp13 + @temp14 + @temp15 + @temp16 + @temp17UPDATE settings_profile SET total_active_points = total_active_points - @sum WHERE (username = @username)DELETE FROM konto_daylist WHERE (id = @id)END
Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.Something like this...
DECLARE @username nvarchar(20)DECLARE @pts int, @rank int
...something that starts a loop thru table A (user_list) and get the username into @username...
SELECT @pts = total_active_points FROM settings_profile WHERE (username = @username)-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranksSELECT @rank = id FROM data_ranks WHERE (pts_min => @pts AND pts_max < @pts)UPDATE user_list SET rank = @rank WHERE (username = @username)
...next persion in the loop...
This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...
But how do i loop thru all the users? with a cursor?
View 10 Replies
View Related
Jun 3, 2008
Hello,
I am a little new at T-SQL. and I could use a suggestion on the best way to accomplish this task. I have an application where I must loop through a table in Microsoft SQL Server 2005, find the records that match and break them up into groups where the value of the Thickness field is in groups of ( <=8).
Lets say I have 100 records and the table contains sacks marked A, B, C, and D. Within Sack A is 6 records. Each of the 6 records has a thickness column with a value between 0.5 to 1.0 (my example shows 2) I have a column called bundle that I must update to show a 1 for the first group of (<=8) inches thick a 2 for the second group (<=8) inches thick and so on. Then repeat the process for sacks B, C and D.
The table itself has the following
Order Number |Zipcode |Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |NULL |
2 |19809 |A | 2 |NULL
3 |19809 |A | 2 |NULL
4 |19809 |A | 2 |NULL
5 |19809 |A | 2 |NULL
6 |19809 |A | 2 |NULL |
7 |19721 |B | 4 |NULL |
8 |19721 |B | 3 |NULL |
9 |19721 |B | 2 |NULL |
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
What I need to do is sum the thickness until it gets to less than 8 and update bundle with a 1. The result would be 4 records and the table would then look like this:
Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |NULL
6 |19809 |A | 2 |NULL |
7 |19721 |B | 4 |NULL |
8 |19721 |B | 3 |NULL |
9 |19721 |B | 2 |NULL |
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
Notice there are two records that are not bundles for sack A. I need to place them in a separate bundle called 2. The result will be as follows
Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |NULL|
8 |19721 |B | 3 |NULL
9 |19721 |B | 2 |NULL
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
Now Sack A is complete so I must move on to Sack B. The first two records total to 7 and adding another record would move the thickness past 8 so I must update the bundle for only the first two records with a 1. The next two total to seven also and the following 2 total to four. We must set those bundles to 1, 2, 3 as follows
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |1 |
8 |19721 |B | 3 |1 |
9 |19721 |B | 2 |2 |
10 |19721 |B | 5 |2 |
11 |19721 |B | 2 |3 |
12 |19721 |B | 2 |3|
These scenarios just keep repeating until the last record
Thank you for any help you can give me
View 7 Replies
View Related
Oct 1, 2004
The process of adding a column with DEFAULT (0) to a table that has 15million records takes a despicable amount of time (too much time) and the transaction log of the database grew to an unacceptable size. I would like to accomplish the same task using this procedure:
·Add the column to the table with null value.
·Loop through the table (500000 records at a time) and SET the value in the newly added column to 0.
·Issue a commit statement after each batch
·Issue a checkpoint statement after each batch.
·Alter the table and SET the column to NOT Null DEFAULT (0)
Here is my Sample script
ALTER TABLE EMPLOYEE ADD EZEVALUE NUMERIC (9,6) NULL
Go
Loop
UPDATE EMPLOYEE SET EZEVALUE = 0
Commit Tan
CHECKPOINT
END (Repeat the loop until the rows in EMPLOYEE have the value 0)
Go
ALTER TABLE EMPLOYEE ALTER COLUMN EZEVALUE NUMERIC (9,6) NOT NULL DEFAULT (0)
My problem is with the loop section of this script. How do I structure the loop section of this script to loop through the employee table and update the EZEVALUE column 500000 rows at a time, issue a Commit Tran and a CHECKPOINT statement until the whole table has been updated. Does anyone out there know how to accomplish this task? Any information would be greatly appreciated.
Thanks in advance
View 5 Replies
View Related
Apr 29, 2005
Hello!
is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?
my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...
select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id
any idaes?
View 1 Replies
View Related
Aug 24, 2006
This routine works in most cases, but fails when a bad date is enteredsuch as:19910631 -- there is no June 31st.Instead of ignoring the bad date the entire DTS job fails. Obviouslythis is something that should be validated at data entry, butunfortunately the only control I have is when appending to the tablewith these data quirks. Any suggestions appreciated!!!'************************************************* *********************' Visual Basic Transformation Script' Copy each source column to the' destination column'************************************************* ***********************Function Main()'DTSDestination("Col002") = DTSSource("Col002")if DTSSource("Col002") = "99999999" or DTSSource("Col002") =Null thenMain = DTSTransforStat_SkipRowelseDTSDestination("Col002") = mid(DTSSource("Col002"),1,4) & "/"&mid(DTSSource("Col002"),5,2) & "/" & mid(DTSSource("Col002"),7,2)End ifMain = DTSTransformStat_OKEnd FunctionRBollinger
View 3 Replies
View Related
Nov 8, 2007
In any asp.net application, whats the simplest way to monitor how many times a page hits the database (opens and closes a connection)?
View 1 Replies
View Related
Sep 27, 2007
In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?
View 10 Replies
View Related
Jan 22, 2008
Using MSSQL (pre 2005):
I have a Link table:
int id (primary key/identity)
varchar(50) linkName
varchar(255) linkHref
//some other stuff
and my Hits table:
int id (primary key/identity)
int linkId (foreign key to Link)
datetime dateCreated
//some other stuff
Hits gets an insert whenever a link is clicked. (All this works just fine)
I'm trying to create a report that shows each link by its name and href, a counter and the last date each link was visited. What I currently have is an accurate listing for those that have been clicked on, but it does not show anything for links that haven't been clicked on. Any suggestions as to how I can modify the following SQL to return "0" and "never" (or DBNULL) if no entries are found in Hits that have the same id? Or do I have to do this in a couple queries?
SELECT COUNT(h.id) as counter, MAX(h.dateCreated) as lastVisited,
l.id as id, l.linkName as linkName, l.linkHref as linkHref
FROM Link as l INNER JOIN Hits as h ON l.id=h.moduleId
GROUP BY l.linkName, l.id, l.linkHref
View 6 Replies
View Related
Apr 20, 2006
Hello,
i am making a Fulltextsearch on MS SQL Server 2005 (indexed, with "Contains").
Because of performance reasons i am only showing the first 200 rows mssql finds ("select top 200...:").
Is there any possibility to get the estimated totalnumber of all rows? i have heard something that is possible to get this in mssql-server. The server then estimates how many rows with that searchword could be in the whole database. google i.e. makes the same thing....
is that true? what do i have to do to get this?
greetings and thx
cpt.oneeye
View 1 Replies
View Related
Apr 1, 2002
I'd like to capture the avg. # of user logins and # db hits per a 5 interval for a weeks time. I'm guessing there are sys tables containing this info. and by using temporary tables and/or creating/modifying SPROCS this info. can be retrieved. If I'm on the right track, a little direction would be very appreciated. If I'm not on track, please assist this rookie dba.
Thank you,
Eoin
View 2 Replies
View Related
Feb 7, 2008
I am serving ad-units. In each ad-unit I show somewhere between 3 and 10 article headline. I track the headline impressions to get an idea of the headline click through rate. I save the output from the stats process in another table. I am currently evaluating the stats every hour, and then truncating the table every night at midnight. The problem is that I get lots of impressions and the database gets bogged down evaluating the data such as...
SELECT COUNT FROM articleimpressions WHERE articleid = x
But the issue isn't the reporting of the data...so much as it's the capture. I had to add caching on the ad-server because the database couldn't handle the number of inserts.
I thought about parsing the web server log files the next day, but the file sizes seem to be too large, and I can't process them all in one day. (At least not on the hardware that I am using.)
I've thought about splitting log files by hour, but was wondering if there may be a more "native" solution within SQL Server? Maybe a trigger, and/or multi-threaded SP that fires and forgets an insert statement to a linked server. But performance is the key here.
Thoughts?
View 2 Replies
View Related
Jul 23, 2005
In MSSQL, is there a way to count the number of instances of asubstring within a string, so that I can sort by that?For example:table tst contains one column: tst_dataif tst_data = "the man with the plan"I'd want a function that counts the occurances of "the"count_substring(tst_data,'the') = 2Basically, I'm making a search engine and I'd like to put the mostrelavent hits at the top of the page.
View 2 Replies
View Related
Dec 19, 2007
hi . would greatly appreciate a clever way to make an internal "counter" on an sql table that contains 6000 articles.(one in each row). these are being accessed from a web page and i would like to know which are the most read ones. so i thought of adding a column that would in some way count each time that the row is being accessed. is there a way for this to be done?? please be detailed since i am quite new to all this.
thanks beforehands.
View 3 Replies
View Related
Mar 28, 2008
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
View 5 Replies
View Related
Feb 4, 2008
Hello friends
I am new to SQL database design.I am having a table name customerletter_master in which all details of customer letters are saved.At a time i.e in single second around 200- 400 entry are made in customerletter_master table. Hits to customerletter_master table are more.
Following fields are used in customerletter_master – CID (autogen number), letterno(primary key),consignee, consignor, letterstatus1, letterstatus2, letterstatus3, POD, and some more fields. When letter passes to different stages letterstatus1 is filled to yes and then letterstatus2 and letterstatus3 are filled according to passage of letter.
And at same time many user can accesses the customerletter_master table to search any letter according to letterno. Therefore customerletter_master is used to enter data and to search data at same time and there can be more than 200-400 users doing add and search records to and from customerletter_master
how should i design this table. What should i use to enter and search record and minimize the table hits made by the user at same time. Should i use store procedures or any other method to add and search record. Plz help me out by giving some example.
View 1 Replies
View Related
Sep 24, 2015
I'm trying to piece together some code on how to find members of our organization who have visited our website.
View 2 Replies
View Related
Apr 11, 2006
I was curious to know if it the amount of data sent to the sql server mattered.
I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).
So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.
Thanks
View 1 Replies
View Related
Oct 28, 2015
I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'Is this at all possible?
View 2 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Mar 19, 2002
I want to loop through a recordset and do inserts into another table based on each record.
The way I have been doing it is copy my key data into a temp table,
Loop through temp finding the max ID
Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.
I know there has to be a better way. The table I am working with is millions of records.
Thanks in advance,
Chris Reeder
View 2 Replies
View Related
Mar 28, 2002
I need to loop through a set of tables and move the data through a data pump from one server to another. This set of tables is dynamic so I have greated a global recordset and the looping is working fine.
During the looping process I need to change the transformations for each table so the source, destination, and transformation of the datapump are correct for the next table in the loop. I am using a VBS to handle this right now but cannot get the transformation to change. I essentially want to auto-remap using a vbs script. Is this possible?
Thanks for any help.
View 1 Replies
View Related
May 23, 2008
Hello clever people
I have a table that holds duplicates that I want to change into a table that has no duplicates. The current table is this
name
compound_id integer
name varchar(150)
name_type integer
This table stores chemical names. There is no primary key in the table so there are multiple compound_id's. I think the original idea was to have four name-types
1 = chemical name
2 = a description of the chemical
3 = a synonym of the chemical
4 = a formula of the chemical
I have created a new table called compound_name with this structure
id int primary key (auto identity)
compound_id int used as a foreign key
compound_name varchar(150)
compound_desc varchar(250)
compound_synonym varchar(150)
compound_formula varchar(50)
compound_trade_nme varchar(50)
I have also started to populate the new table by running this code
insert into compound_name(compound_id,compound_name)
SELECT DISTINCT compound_id, name
FROM dbo.name
WHERE (name_type = 1)
Now I need to somehow loop through the name table getting distinct compound_id's, and perform a case when name_type = 2 (which is synonym name_type) Then inside the loop update compound_name.compound_synonym for each compound_id which matches name_type 2
Then case 3 do the same for name_type 3 which is the name_type for descripton
Then case 4 do the same for name_type 4 which is the formula
Any help would be appreciated
View 6 Replies
View Related
Sep 29, 2004
Hi there, I am new to SQL and am having trouble looping a script. I have the following script that needs to be refreshed a large number of times, or needs to be looped indefinitely until stopped:
select
df.tablespace_name "Tablespace",
block_size "Block Size",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
dba_tablespaces ts,
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where
ts.tablespace_name = fs.tablespace_name
and
df.tablespace_name = fs.tablespace_name(+)
;
I know this question may have a very easy solution, but I have no idea how to solve it.
Thanks in advance
View 4 Replies
View Related
Mar 21, 2007
hi guys,
I have a table like this,
S D C
Bi Ar Ar
Bi Ar Ch
Bi Ar Ma
Bi Au Ar
Bi Au Ch
Bi Au Ma
As Ar Ar
As Ar Ch
As Ar Ma
As Au Ar
As Au Ch
As Au Ma
As Au Ma
I have 3 columns S, D, C. i have text values in it. I need to write a query such that it will check each row for distinct value.For ex, all the rows are distinct except the last one. so i need to see all the duplicate entries. can anyone help me?
View 4 Replies
View Related
Jun 15, 2007
Hi guys,
Can anybody tell me how to do looping in SQL?
Regards,
David
View 6 Replies
View Related
Sep 6, 2007
Hi All,
I would like to know the best way to approach the following requirement:
I have an ASP.net 2 web site which gets its data from SQL 2005.
I am trying to run a series of 'rules' which are SQL where statements stored in a table, against rows stored in another table. I open the 'Rules' table looping through all records. I copy each rule to a string and put it on the end of the SQL statement so that the rule will only be appended if it passes the rule... this may be a little confusing.
The rules process will fire when the details have been submitted to the database.
Table containg rules would contain something like:
ID, RuleSQL
1, (ClientAge >18)
2, (ClientIncome>10000)
3 Etc...
This a very simplified version of the table but gives the general idea.
I currently use ASP.NET 2 and sqlconnections/datareaders to do this. I would like to know if there is a way of doing the same thing server side using Transact SQL because that would (I believe) speed up the time taken to perform all the tests as i wouldn't need to rely on ASP to open all recordsets and append the data.
If the ASP route would be the standard way of doing it and is not likely to have a detremental effect on performance then i am fine to stick with it because i know it works.
any comments or suggestions would be welcomed.
Thanks,
Ian
View 2 Replies
View Related
Jan 17, 2008
I have an array (12,2) of values plus a profile variable that I want to pass as parameters while writing to a database. I've been told that I've set up the parameters wrong, and they cannot be changed every time I loop using the method I'm using. But I have no idea how to use any other method. Please... I'm down to the wire in terms of deadline here. I have until midnight to get it uploaded and running online. [CODE]Sub WriteClasses(ByVal CreditsArray) Dim i As Integer Dim EnrollDb As SqlConnection Dim cmdEnroll As SqlCommand EnrollDb = New SqlConnection("Server=LONNASQLEXPRESS;Integrated Security=True;database=LGordonTouroReg") cmdEnroll = New SqlCommand("INSERT INTO Enrollment (SectionID, Semester, Year, ClassID, StudentID) VALUES (@SectionID, 'Fall', '2007', @ClassID, @StudentID)", EnrollDb) EnrollDb.Open() For i = 0 To 12 cmdEnroll.Parameters.AddWithValue("@SectionID", CreditsArray(i, 2))
cmdEnroll.Parameters.AddWithValue("@ClassID", CreditsArray(i, 0))
cmdEnroll.Parameters.AddWithValue("@studentID", Profile.StudentID) If Not CreditsArray(i, 0) = "" Then cmdEnroll.ExecuteNonQuery() Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>") End If Next i EnrollDb.Close() End Sub[/CODE]
View 6 Replies
View Related
Apr 11, 2008
lets say i have a stored procedure (for insert command) which i am calling in my code to execute. The user provided data is being stored in a array. My class takes the stored procedure name and also takes parameters name and types. Is there any way to loop through the parameters, (various columns in the table which is of diffrent data type ie varchar, int, etc). How to implement it?
View 1 Replies
View Related
Sep 14, 2004
Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.
I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@roleID", SqlDbType.NVarChar)).Value = RoleID
Try
DS = New DataSet
MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records
End Sub
Thanks for any help!!!
View 1 Replies
View Related
Jul 11, 2000
I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:
SELECT item_no
FROM Items
BEGIN
SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty
WHERE item_no=@item_no
UPDATE Items
SET Sales=@Total_Sales,
Qty=@Total_Qty
WHERE item_no=@item_no
END
I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?
Thanks for the help,
Chris
View 2 Replies
View Related
Aug 30, 1999
What I have to do is loop through a table with about 900 records, do computations on each record and update a database, can someone help me out.
here is the code that I am using right now on another site with coldfusion but i want to convert it all to a stored proc for speed reasons.
<!--- Query the Stores Table --->
<cfquery name="Zip2" datasource="#application.data#" username="#application.username#" password="#application.password#">
exec Stores_GetStoreZipInfo
</cfquery>
<!--- Loop through the STORE table --->
<cfloop query="zip2">
<cfset Lat1 = #zip1.lat#>
<cfset Lg1 = #zip1.long#>
<cfset Lat2 = #zip2.lat#>
<cfset Lg2 = #zip2.long#>
<!--- Do the actual distance calculation between the user's zipcode each store's zipcode --->
<cfset DistLat = 69.1 * (Lat2-Lat1)>
<cfset DistLong = 69.1 * (Lg2-Lg1) * #cos(Lat1 / 57.3)#>
<cfset Dist = ((#abs(DistLat)# * 2) + (#abs(DistLong)# * 2)) * 0.5>
<!--- Update the DISTANCE field on STORE table --->
<cfquery name="UpdateZips" datasource="#application.data#" username="#application.username#" password="#application.password#">
exec Stores_UpdateZipSeachInfo '#Dist#', '#zip2.zipcode#'
</cfquery>
</cfloop>
View 1 Replies
View Related
Aug 16, 2005
I am not sure if what I wish to do is possible, but I shall ask anyway;
My project examines a database log of all the pages of an online teaching tool. Once the user has completed all the pages they are to be issued a certificate. Users may complete the teaching tool in any order, and the pages are always stored whenever they are acccessed, regardless of certification.
I have created a number of views that extract the data into a list of all the possible completion dates; i.e. where all the pages have been completed within any 12 month period. I need to write a query/view that uses the view to extract the first possible user completion date followed by every completion 12 months after that, then after that etc. to present day.
Can I do this?
Am I making sense ?
A no is acceptable in this case; I know I can do this with multiple queries from withing an application. I'd just rather not.
View 4 Replies
View Related
