How to increment a variable according to a 'if' statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to increment a variable according to a 'if' statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-05 : 08:46:29

Adele writes "I am learning SQL and try to use it at work. I wonder if you can help me to implement this logic in SQL codes:

The logic:

Select
Year=SUBSTRING(TERM,1,4),
If Semester='1' then Year =Year +1
from Table_student
go

I look forward to hearing from you. Thanks!

Adele"

Crespo

85 Posts

Posted - 2002-09-05 : 09:23:15

How about this :

CREATE TABLE dbo.SCHOOL(
[INFO] [VARCHAR] (12) NULL,
[YEAR] [INTEGER] NULL,
[COURSE] [VARCHAR] (20) NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.SCHOOL VALUES('01/01/1994-2', 10, 'History')
INSERT INTO dbo.SCHOOL VALUES('01/01/1994-3', 6, 'Sport')
INSERT INTO dbo.SCHOOL VALUES('01/01/1994-4', 9, 'Art')

--************
-- THE CURSOR
--************

DECLARE @INFO VARCHAR(12),
@YEAR INTEGER,
@SEMESTER VARCHAR(20)

DECLARE SCHOOL_CURSOR CURSOR
FOR
SELECT INFO,
YEAR,
COURSE
FROM SCHOOL

OPEN SCHOOL_CURSOR
FETCH NEXT FROM SCHOOL_CURSOR INTO @INFO,
@YEAR,
@SEMESTER
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF SUBSTRING(@INFO, 12, 1) = 2
BEGIN
UPDATE SCHOOL
SET YEAR = YEAR + 1
END

FETCH NEXT FROM SCHOOL_CURSOR INTO @INFO,
@YEAR,
@SEMESTER
END

CLOSE SCHOOL_CURSOR
DEALLOCATE SCHOOL_CURSOR

I set up a table as follows :

01/01/1994-2 11 History
01/01/1994-3 7 Sport
01/01/1994-4 10 Art

01/01/1994-2 : first part is the date and the last didgit on the right is the semester.
The numbers in the middle is the year etc.

The cursor fetches a row, checks the semester number.. if it is equal to 2 then the year column is incremented by 1 and the table is updated and so on
I am sure you can adapat a similar method to your problem.

Of course, there is probably an easier way to do it, but that's how I would do it.

Hope this helps.

Good Luck!

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

mr_mist
Grunnio

1870 Posts

Posted - 2002-09-05 : 09:36:54

quote:

Select
Year=SUBSTRING(TERM,1,4),
If Semester='1' then Year =Year +1
from Table_student
go

I'm not sure I understand your logic (specifically the substring bit, or whether you want to do a select or an update), but...

SELECT
case
when semester = '1' then left (term, 4) + 1
else left (term,4)
end as year
FROM
table_student

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources