NEED HELP ABOUT CURSOR

Hello,
I have this Store Procedure and i am new in "Cursor" programming
The store Procedure is this
CREATE PROCEDURE Testing_Cursor AS
DECLARE @.AccCode varchar(12)
DECLARE @.YEAR varchar(4)
Declare CODE CURSOR
FOR
SELECT DISTINCT(NLAcc_Code)
FROM NLMovement_TMP
OPEN CODE
BEGIN
FETCH NEXT FROM CODE INTO @.AccCode
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE CYEAR CURSOR
FOR SELECT DISTINCT(AccYear) FROM NLMOVEMENT_TMP WHERE
NLACC_CODE=@.ACCCODE
OPEN CYEAR
FETCH NEXT FROM CYEAR INTO @.YEAR
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT SUM(MOVEMENT)
FROM NLMOVEMENT_TMP
WHERE NLACC_CODE=@.ACCCODE
AND ACCYEAR=@.YEAR
END
CLOSE CYEAR
END
END
CLOSE CODE
GO
I have an endless lopp in the second WHILE @.@.FETCH_STATUS = 0
it avtually never goes into the begin - end code after .
Am i using the WHILE statment correct'
Can anyone find the what is wrong with it'
Thanks
SavvasThe first thing you should learn about cursors is that you don't need
them! At least 99.99%, of the time cursors are not a good solution to
problems in SQL. Try this instead
SELECT SUM(movement)
FROM nlmovement_tmp
GROUP BY nlacc_code, accyear ;
If that's not what you wanted then please post DDL and sample data so
that we can understand your requireemnts better.
I would suggest you don't bother learning about cursors until you a
much more expert with "proper" SQL. Only then will you have the
knowledge to judge the exceptional situations when cursors make sense.
Hope this helps.
David Portas
SQL Server MVP
--|||Hi Sawas,
AND ACCYEAR=3D@.YEAR
FETCH NEXT FROM CODE INTO @.AccCode --missed that here
END
CLOSE CYEAR
END
END
CLOSE CODE
GO
But what about NOT using a cursor, they can be slow (not to say that
they are slow, because I don=B4t want to start a religious war in here),
but If you are onyl doing queries, you should rely on a non cursor
solution:
SELECT SUM(MOVEMENT)
FROM NLMOVEMENT_TMP
GROUP BY NLACC_CODE,ACCYEAR
HTH, jens Suessmeyer.|||Well Actually I have this situation
I have a table with
AccCode,
AccMovement,
AccYear, and
AccPeriod
and my data looks like this
AccCode AccYear AccPeriod sumaryOfThe movement
110401 1998 02 541870.00
110401 1998 03 1210000.00
110401 1998 04 2687330.00
110401 1998 05 1220450.00
110401 1998 06 3508000.00
110401 1998 07 5606620.00
110401 1998 08 1567080.00
110401 1998 09 150000.00
110401 1998 10 1202180.00
110401 1998 11 1373980.00
110401 1998 12 2604600.00
And this account has data for many years ,
Now i want to make a table like this
accCode , AccYear , AccPeriod, Opening balance , movement ,
OpeningBalance+Movement where opening balance = closing balnce of
previus period
how can i do this ?|||This would be a bit easier if you used proper dates. Put your financial
periods in a Calendar table instead.
I'm just guessing at the following table structure because you didn't
post it. Note the key.
CREATE TABLE nlmovement (acccode INTEGER NOT NULL, accyear INTEGER NOT
NULL /* Why not a DATETIME column? */ CHECK (accyear BETWEEN 1900 AND
2100), accperiod INTEGER NOT NULL CHECK (accperiod BETWEEN 1 AND 12),
amount NUMERIC(10,2), PRIMARY KEY (acccode,accyear,accperiod)) ;
Try this:
SELECT C.acccode, C.accyear, C.accperiod, C.amount,
SUM(P.amount)-C.amount AS opening_balance,
C.amount,
SUM(P.amount) AS closing_balance
FROM nlmovement AS C, nlmovement AS P
WHERE C.acccode = P.acccode
AND (C.accyear > P.accyear
OR (C.accyear = P.accyear
AND C.accperiod >= P.accperiod))
GROUP BY C.acccode, C.accyear, C.accperiod, C.amount ;
In general it's unwise to store calculations in the database because
any kind of redundancy can lead to inconsistencies and incorrect
results. Don't add the balance columns to a table - just calculate them
in your queries and reports (in fact a reporting tool will probably
calculate the running balance more efficiently than my query will.)
Hope this helps.
David Portas
SQL Server MVP
--sql

Need advice with a query

Hi,
I need an advice.
I have a new project where I have to store employee salary values in order
to keep historical representation of all changes done to the employee
record. Below is the approximate model how I am going to proceed.
My question is, how would I pull ee salary at certain date, say "what was
employee salary on July 1st, 2005".
I know I can do something like that:
SELECT TOP 1 salary FROM tEmployeeSalary
WHERE employee_id = 1
AND DateDiff(dd, 'July 1, 2005', effective_date) >= 0
ORDER BY effective_date ASC
But I would need to use it in joins with other tables. So, is it possible to
write a query to get to the required record without ORDERing and using TOP?
begin tran
create table tEmployeeSalary
(
employee_id int,
salary money,
effective_date datetime,
is_active bit
)
insert into tEmployeeSalary (employee_id, salary, effective_date, is_active)
values (1, 45000, 'dec 12, 2004', 1)
insert into tEmployeeSalary (employee_id, salary, effective_date, is_active)
values (1, 49000, 'mar 1, 2005', 1)
insert into tEmployeeSalary (employee_id, salary, effective_date, is_active)
values (1, 54000, 'june 20, 2005', 1)
insert into tEmployeeSalary (employee_id, salary, effective_date, is_active)
values (1, 67000, 'sep 10, 2005', 1)
select * from tEmployeeSalary
drop table tEmployeeSalary
commit tran
Thank you in advance for all suggestions.
GenaNews,
Assuming salarys are continually increasing and never decrease...maybe this
would work:
SELECT MAX(SALARY)
FROM TEMPLOYEESALARY
WHERE EMPLOYEE_ID = 1 AND EFFECTIVE_DATE <= '7/1/2005'
HTH
Jerry
"News" <zoom191919@.yahoo.com> wrote in message
news:S8Cdna9sLsQCnqHeRVn-iA@.magma.ca...
> Hi,
> I need an advice.
> I have a new project where I have to store employee salary values in order
> to keep historical representation of all changes done to the employee
> record. Below is the approximate model how I am going to proceed.
> My question is, how would I pull ee salary at certain date, say "what was
> employee salary on July 1st, 2005".
> I know I can do something like that:
> SELECT TOP 1 salary FROM tEmployeeSalary
> WHERE employee_id = 1
> AND DateDiff(dd, 'July 1, 2005', effective_date) >= 0
> ORDER BY effective_date ASC
> But I would need to use it in joins with other tables. So, is it possible
> to write a query to get to the required record without ORDERing and using
> TOP?
>
> begin tran
> create table tEmployeeSalary
> (
> employee_id int,
> salary money,
> effective_date datetime,
> is_active bit
> )
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 45000, 'dec 12, 2004', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 49000, 'mar 1, 2005', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 54000, 'june 20, 2005', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 67000, 'sep 10, 2005', 1)
> select * from tEmployeeSalary
> drop table tEmployeeSalary
> commit tran
>
> Thank you in advance for all suggestions.
> Gena
>|||You don't really have every column nullable do you? What are the keys?
Thanks for the DDL and sample data but it would be more helpful if it
was accurate and complete. Assuming that (employee_id, effective_date)
is unique, try this:
SELECT employee_id, salary
FROM tEmployeeSalary AS E
WHERE effective_date =
(SELECT MAX(effective_date)
FROM tEmployeeSalary
WHERE employee_id = E.employee_id
AND effective_date <= '20050701') ;
Be careful with the MONEY datatype. Precision is lost when you multiply
and divide MONEY. In my opinion MONEY is unsuitable for financial data.
(yes, seriously!)
David Portas
SQL Server MVP
--|||You can try to use subquery:
SELECT employee_id, salary
FROM tEmployeeSalary ES1
WHERE ES1.employee_id = 1
AND ES1.effective_date = (SELECT Max(ES2.effective_date )
FROM tEmployeeSalary ES2
WHERE ES2.employee_id =
ES1.employee_id
AND ES2.effective_date <=
'07/01/2004')
Perayu
"News" <zoom191919@.yahoo.com> wrote in message
news:S8Cdna9sLsQCnqHeRVn-iA@.magma.ca...
> Hi,
> I need an advice.
> I have a new project where I have to store employee salary values in order
> to keep historical representation of all changes done to the employee
> record. Below is the approximate model how I am going to proceed.
> My question is, how would I pull ee salary at certain date, say "what was
> employee salary on July 1st, 2005".
> I know I can do something like that:
> SELECT TOP 1 salary FROM tEmployeeSalary
> WHERE employee_id = 1
> AND DateDiff(dd, 'July 1, 2005', effective_date) >= 0
> ORDER BY effective_date ASC
> But I would need to use it in joins with other tables. So, is it possible
> to write a query to get to the required record without ORDERing and using
> TOP?
>
> begin tran
> create table tEmployeeSalary
> (
> employee_id int,
> salary money,
> effective_date datetime,
> is_active bit
> )
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 45000, 'dec 12, 2004', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 49000, 'mar 1, 2005', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 54000, 'june 20, 2005', 1)
> insert into tEmployeeSalary (employee_id, salary, effective_date,
> is_active)
> values (1, 67000, 'sep 10, 2005', 1)
> select * from tEmployeeSalary
> drop table tEmployeeSalary
> commit tran
>
> Thank you in advance for all suggestions.
> Gena
>|||Thanks David,
The data in my table does not allow nulls, although employee_id id is not
unique and the effective_date can repeat in cases when mistake was made and
I can allow to change salary on the same date or earlier (in this case
is_active will be set to 0).
And, thanks for "money" advice, good point.
I guess, I cannot get away from "TOP - ORDER" or subquery. I think I will
write a UDF and call it anytime I need a salary at a time point.
Another question, would it be a good idea to add a field - primary key
identity? emp_salary_id...
Thanks,
Gena
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128008402.193130.131230@.g14g2000cwa.googlegroups.com...
> You don't really have every column nullable do you? What are the keys?
> Thanks for the DDL and sample data but it would be more helpful if it
> was accurate and complete. Assuming that (employee_id, effective_date)
> is unique, try this:
> SELECT employee_id, salary
> FROM tEmployeeSalary AS E
> WHERE effective_date =
> (SELECT MAX(effective_date)
> FROM tEmployeeSalary
> WHERE employee_id = E.employee_id
> AND effective_date <= '20050701') ;
> Be careful with the MONEY datatype. Precision is lost when you multiply
> and divide MONEY. In my opinion MONEY is unsuitable for financial data.
> (yes, seriously!)
> --
> David Portas
> SQL Server MVP
> --
>|||> Another question, would it be a good idea to add a field - primary key
> identity? emp_salary_id...
More important to add a constraint on the relevant business key. Here
I'm guessing:
ALTER TABLE tEmployeeSalary
ADD CONSTRAINT ak1_employee_salary
UNIQUE (employee_id, effective_date)
or:
ALTER TABLE tEmployeeSalary
ADD CONSTRAINT pk_employee_salary
PRIMARY KEY (employee_id, effective_date)
You can add the IDENTITY key if you need to reference it in another
table but otherwise it would be redundant. Every table should of course
have at least one candidate key.
David Portas
SQL Server MVP
--|||Hmm,
I cannot make PRIMARY KEY (employee_id, effective_date) because it will not
allow me to set different salaries for the same employee on the same date.
What if administrator made a mistake and noticed it few months later. I
cannot update it, I will need to add a new record with the same employee_id
and the same effective_date.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128012586.541851.238920@.z14g2000cwz.googlegroups.com...
> More important to add a constraint on the relevant business key. Here
> I'm guessing:
> ALTER TABLE tEmployeeSalary
> ADD CONSTRAINT ak1_employee_salary
> UNIQUE (employee_id, effective_date)
> or:
> ALTER TABLE tEmployeeSalary
> ADD CONSTRAINT pk_employee_salary
> PRIMARY KEY (employee_id, effective_date)
> You can add the IDENTITY key if you need to reference it in another
> table but otherwise it would be redundant. Every table should of course
> have at least one candidate key.
> --
> David Portas
> SQL Server MVP
> --
>|||another option would be to have effective_from and effective_to columns
in the table|||So add a "created_date" or "modified_date" to the key. It seems your
original specification was too much of a simplified example. Adding
duplicate effective dates without further information and just updating
"is_active" would mean you would lose the audit trail containing the
sequence of changes.
David Portas
SQL Server MVP
--|||If you can't define a primary key, then you are sunk. SQL has no
order. If you have 2 entries for the same employee with the same
effective date, which do you wish to select? The higher salary? The
lower? The one entered last? (sorry, that information is not
available). select top ... has no meaning without an order by -
duplicates can appear in any order SQL Server wants to show them.
Given the requirements I have seen so far, you can't get there from
here.
You have gotten quite a bit of good, professional advice from the
posters in this thread. You may want to rethink things a bit.
Good luck.
Payson
News wrote:
> Hmm,
> I cannot make PRIMARY KEY (employee_id, effective_date) because it will no
t
> allow me to set different salaries for the same employee on the same date.
> What if administrator made a mistake and noticed it few months later. I
> cannot update it, I will need to add a new record with the same employee_i
d
> and the same effective_date.
> Thanks
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128012586.541851.238920@.z14g2000cwz.googlegroups.com...

NCHAR vs NVARCHAR

In one of the tables i have a column that will always store a single unicode
character. Now , according to theory i should go with NCHAR(1) . But i would
like to know what is the divantage of using NVARCHAR(1) instead of
NCHAR(1) in this case ? is there going to be any extra overhead ?
Thanks
ManishIf you're always storing 1 character us NCHAR(1).
NVARCHAR(1) will require extra storage space to store the length of the data
(using 4 bytes instead of only 2)
NVARCHAR(1) will allow you to store N'' (empty string) which means it needs
to store length.
"Manish Gaur" <ManishGaur@.discussions.microsoft.com> wrote in message
news:79AEFA64-AB85-4262-9317-204D908C6F57@.microsoft.com...
> In one of the tables i have a column that will always store a single
unicode
> character. Now , according to theory i should go with NCHAR(1) . But i
would
> like to know what is the divantage of using NVARCHAR(1) instead of
> NCHAR(1) in this case ? is there going to be any extra overhead ?
> Thanks
> Manish|||You are going to burn 2 extra bytes per row (per column?) as you will need t
o
store the size of the field, even though it is only 1 in length. Not wise.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Manish Gaur" wrote:

> In one of the tables i have a column that will always store a single unico
de
> character. Now , according to theory i should go with NCHAR(1) . But i wou
ld
> like to know what is the divantage of using NVARCHAR(1) instead of
> NCHAR(1) in this case ? is there going to be any extra overhead ?
> Thanks
> Manish