urgent need help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

urgent need help

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 06:17:04

I have a table called student_master_Table. i am maintaining all the details about the student like

admission_no,
student_name,
class,
section,
joiningdate.

In this admission_no is an identity(100,1).

Now i need to custom auto generate roll numbers of a students based on the class & section.

twelve classes will be there.it should be like this

Admission_no stu_name class section roll_no
--------------------------------------------
100 A 1 A S0001
101 B 1 A S0002
102 C 1 B S0001
103 A 1 A S0003
104 D 1 B S0002

please help me...can we do like this.

thanks in advance

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 06:26:20

SELECT 'S'+CAST(ROW_NUMBER() OVER(PARTITION BY  class ORDER BY admission_no) AS varchar(10)) AS RollNo,
 *
FROM YourTable

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 06:41:29

thanks for the reply...

but i need like

if its first class , A section then its like s1,s2,----------- ... and when its first class, B section then again it should start from S1,s2------------..... .

each and every class and section it should start from first......

please help me

Thanks

Zakeer Sk

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-07 : 06:58:16

quote:
Originally posted by shaik.zakeer

thanks for the reply...

but i need like

if its first class , A section then its like s1,s2,----------- ... and when its first class, B section then again it should start from S1,s2------------..... .

each and every class and section it should start from first......

please help me

Thanks

Zakeer Sk

Did you try Visakh's query?

Madhivanan

Failing to plan is Planning to fail

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 07:00:39

i tried that but it is showing like
s1,s2,s3,,,,,,,,,,,,,,,,

its not spliting based on the class and section ..

i need to allot based on the class and section

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:00:47

[code]declare @test table
(
Admission_no int,
stu_name char(1),
class int,
section char(1),
roll_no varchar(5)
)

insert into @test (Admission_no, stu_name, class, section)
select 100, 'A', 1, 'A' union all
select 101, 'B', 1, 'A' union all
select 102, 'C', 1, 'B' union all
select 103, 'A', 1, 'A' union all
select 104, 'D', 1, 'B'

select *, 'S'+ right('0000'+ cast(row_number() over (partition by class,section order by Admission_no) as varchar(10)),4) as rollno
from @test
order by Admission_no

output
------------------------------------------
Admission_no stu_name class section roll_no rollno
------------ -------- ----------- ------- ------- ------
100 A 1 A NULL S0001
101 B 1 A NULL S0002
102 C 1 B NULL S0001
103 A 1 A NULL S0003
104 D 1 B NULL S0002[/code]

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 07:08:41

AWESOME..NICE WORK...

HOW CAN I INSERT INTO TABLE AT THE TIME OF INSERTING A ROW INTO THE TABLE

.PLS HELP ME FRIEND

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:22:05

is this what you're asking for?

select *, 'S'+ right('0000'+ cast(row_number() over (partition by class,section order by Admission_no) as varchar(10)),4) as rollno 
into newtable
from @test
order by Admission_no

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 07:24:43

NOT THAT.....IF I ENTER A ROW INTO THE STUDENT_MASTER_TABLE, IT SHOULD GENERATE THE ROLL-NO AUTOMATICALLY BASED ON THE CLASS AND SECTION....

I HOPE YOU UNDERSTOOD

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:33:22

quote:
Originally posted by shaik.zakeer

NOT THAT.....IF I ENTER A ROW INTO THE STUDENT_MASTER_TABLE, IT SHOULD GENERATE THE ROLL-NO AUTOMATICALLY BASED ON THE CLASS AND SECTION....

I HOPE YOU UNDERSTOOD

Thanks

Zakeer Sk

for that you need to get maximum value corresponding to section from table at time of insertion and add values to that.how is your current insert procedure?

b/w can you please refrain from using caps lock?

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 07:38:02

admission no is an identity column ,starts from 100 increment is 1..
and the rest is common.

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:47:56

[code]insert into yourtable (roll_no, otherfields)
select (select 'S'+right('0000'+cast(max(replace(rollno,'S','')*1)+1 as varchar(10)),4) from yourtable where section=@section and class=@class),
@class,
@section,
@stu_name...[/code]

where @section,@class etc are values that you pass to procedure for insertion.

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 08:13:50

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 08:17:19

[code]CREATE PROC PRCAUTOROLLNO
(
@STUDENT_NAME VARCHAR(250),
@CLASS INT,
@SECTION VARCHAR(5)
)
AS
BEGIN
INSERT INTO STUDENT_MASTER_TABLE (STUDENT_NAME,CLASS,SECTION,ROLL_NO)
SELECT @STUDENT_NAME,
@CLASS,
@SECTION,
(
select 'S'+right('0000'+cast(max(replace(roll_no,'S','')*1)+1 as varchar(10)),4)
from STUDENT_MASTER_TABLE
where SECTION=@SECTION
and CLASS=@CLASS
)

END[/code]

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 08:25:38

-------------------------------------------------
admin_no stu_name class section roll_no
----------------------------------------------------
100 zakeer 1 A NULL
101 NULL NULL NULL S001
102 zak 1 A NULL
103 NULL NULL NULL S001

when i execute the procedure .........its storing values like this..

and also when we inserting first row into the table we need check the whether is there any value or null....

at the time of executing procedure it showing some worning and inserting the record in second row....

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 08:32:38

[code]CREATE PROC PRCAUTOROLLNO
(
@STUDENT_NAME VARCHAR(250),
@CLASS INT,
@SECTION VARCHAR(5)
)
AS
BEGIN
INSERT INTO STUDENT_MASTER_TABLE (STUDENT_NAME,CLASS,SECTION,ROLL_NO)
SELECT @STUDENT_NAME,
@CLASS,
@SECTION,
(
select 'S'+right('0000'+cast(coalesce(max(replace(roll_no,'S','')*1),0)+1 as varchar(10)),4)
from STUDENT_MASTER_TABLE
where SECTION=@SECTION
and CLASS=@CLASS
)

END[/code]

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-07 : 08:36:07

its working fine...but when i insert a single row its inserting two rows....
how can i avoid this

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 09:44:37

quote:
Originally posted by shaik.zakeer

its working fine...but when i insert a single row its inserting two rows....
how can i avoid this

Thanks

Zakeer Sk

how are you calling the above procedure?

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-08 : 01:31:34

after creating the sp ... i am just executing the sp like

exec prcautorollno 'A',1,'A'

but its inserting two rows into the table

admission_no stu_name class section roll_no
-----------------------------------------------
100 A 1 A s0001
101 null null null s0002

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 01:36:45

show your whole procedure please?

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-10-08 : 02:35:48

CREATE PROC PRCAUTOROLLNO
(
@STUDENT_NAME VARCHAR(250),
@CLASS INT,
@SECTION VARCHAR(5)
)
AS
BEGIN
INSERT INTO STUDENT_MASTER_TABLE (STUDENT_NAME,CLASS,SECTION,ROLL_NO)
SELECT @STUDENT_NAME,
@CLASS,
@SECTION,
(
select 'S'+right('0000'+cast(coalesce(max(replace(roll_no,'S','')*1),0)+1 as varchar(10)),4)
from STUDENT_MASTER_TABLE
where SECTION=@SECTION
and CLASS=@CLASS
)

END
-------------------------------
exec prcautorollno 'A',1,'A'
------------------------------

-------------------------------------------------
admin_no stu_name class section roll_no
----------------------------------------------------
100 A 1 A S0001
101 NULL NULL NULL S0002

if i insert one more row

exec prcautorollno 'B',1,'A'

-------------------------------------------------
admin_no stu_name class section roll_no
----------------------------------------------------
100 A 1 A S0001
101 NULL NULL NULL S0002
102 B 1 A S0002
103 NULL NULL NULL S0003

please help me

Thanks

Zakeer Sk

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources