Query individual records within a table

[Angelina]

Please can someone advise me on how I can create a statement that will query each individual row within a table.

As the data will vary month by month I'm unable to state the amount of rows that will need to be queried.

An Overview:
We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly.
As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.

Please find below the script - its very much in its infancy!!

CREATE PROCEDURE sp_reallocate AS
select * from tbl_mobile_reallocate
order by allocation_change_date

update tbl_mobile_import
set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate
where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno
and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date

update tbl_mobile_reallocate
set status = &#39;Closed&#39;
GO

[Jim]

One way would be to use a cursor getting the &#39;next&#39; row from the table.
The alternative equivalent way is to copy the table to a temporary table
( insert into #tempTable select * from tble_mobile ) possibly adding and identity column to #temptable then in a while loop select the columns of the #temptable to variables for the maximim identity column - do the processing delete the currently selected row from the temp table and repeat while there are rows in the tmp table using the while test clause.


------------
Angelina at 5/21/2002 9:22:10 AM

Please can someone advise me on how I can create a statement that will query each individual row within a table.

As the data will vary month by month I&#39;m unable to state the amount of rows that will need to be queried.

An Overview:
We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly.
As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.

Please find below the script - its very much in its infancy!!

CREATE PROCEDURE sp_reallocate AS
select * from tbl_mobile_reallocate
order by allocation_change_date

update tbl_mobile_import
set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate
where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno
and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date

update tbl_mobile_reallocate
set status = &#39;Closed&#39;
GO

[Angelina]

Hi Jim

Thanks for the reply below, I&#39;ve been able to find a script using cursors and the Fetch Next function. However I could do with someone verifying what should go where!!!!


DECLARE allocate_cursor CURSOR FOR
select * from tbl_mobile_reallocate
WHERE status = &#39;Open&#39;
ORDER BY allocation_change_date

OK the top bits fine - I understand that I&#39;ve sorted the data into the correct order but I now need to apply the following statement to each row!

update tbl_mobile_import
set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate
where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno
and tbl_mobile_reallocate.allocation_change_date <= tbl_mobile_import.invoice_date
Update tbl_mobile_reallocate
set status = &#39;Closed&#39;
where status = &#39;Open&#39;

Now should this script go before the Open allocate_cursor or after the Fetch Next from allocate_cursor?
Its just that the updating tbl_mobile_reallocate
set status = &#39;Closed&#39;
where status = &#39;Open&#39;
is being applied to the table as a whole and not to the individual rows!!!

OPEN allocate_cursor

-- Perform the first fetch.
FETCH NEXT FROM allocate_cursor


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM allocate_cursor
End

CLOSE allocate_cursor
DEALLOCATE allocate_cursor

update tbl_mobile_import
set firstname = empinfo.firstname,
lastname = empinfo.lastname,
department = empinfo.department,
business_unit = empinfo.business_unit
from empinfo
where tbl_mobile_import.empno = empinfo.empno

I appreciate your help!

Many Thanks
Angelina


------------
Jim at 5/22/2002 5:43:17 AM

One way would be to use a cursor getting the &#39;next&#39; row from the table.
The alternative equivalent way is to copy the table to a temporary table
( insert into #tempTable select * from tble_mobile ) possibly adding and identity column to #temptable then in a while loop select the columns of the #temptable to variables for the maximim identity column - do the processing delete the currently selected row from the temp table and repeat while there are rows in the tmp table using the while test clause.


------------
Angelina at 5/21/2002 9:22:10 AM

Please can someone advise me on how I can create a statement that will query each individual row within a table.

As the data will vary month by month I&#39;m unable to state the amount of rows that will need to be queried.

An Overview:
We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly.
As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.

Please find below the script - its very much in its infancy!!

CREATE PROCEDURE sp_reallocate AS
select * from tbl_mobile_reallocate
order by allocation_change_date

update tbl_mobile_import
set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate
where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno
and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date

update tbl_mobile_reallocate
set status = &#39;Closed&#39;
GO

[jim]

Angelina,
you may have progressed by now
but briefly
declare some variables to fetch the results from the select into

change select * in cursor to name fields that you need possibly

declare @empno char(10), @mobno char(10)

while @@fetch_staus =0 begin
fetch next from allocate_cursor into @empno , @mobno

update tbl_mobile_import set empno= @empno

where tble_mobile_import= @mobno in outline

end
/* this should do the entire table */

close cursor allocate_cursor
deallocat allocate cursor

/* rest of it */

Hope this is of some use.





------------
Angelina at 5/21/2002 9:22:10 AM

Please can someone advise me on how I can create a statement that will query each individual row within a table.

As the data will vary month by month I&#39;m unable to state the amount of rows that will need to be queried.

An Overview:
We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly.
As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.

Please find below the script - its very much in its infancy!!

CREATE PROCEDURE sp_reallocate AS
select * from tbl_mobile_reallocate
order by allocation_change_date

update tbl_mobile_import
set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate
where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno
and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date

update tbl_mobile_reallocate
set status = &#39;Closed&#39;
GO