to find the gaping no - 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)

to find the gaping no

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 15:56:48

I have a table like this:
ID BegNo EndNO
1 1 5
2 10 20

I want to output like this:
ID BegNo EndNo Remarks
1 1 5 NULL
2 10 20 6-9

pls use the follwing script:
update b
set b.remarks = case when b.begno>(a.endno+1) then (a.endno+1) +'-'+ (b.begno-1)
when b.begno<(a.endno+1) then NULL
end
from table_1 a join table_1 b on a.id+1 = b.id

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 16:04:52

[code]Update b
set b.remarks = Case when b.begno>(a.endno+1) then Convert(varchar(10),(a.endno+1)) +'-'+ Convert(varchar(10,(b.begno-1))
Else NULL
end
from table_1 a Join table_1 b
on a.id+1 = b.id[/code]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 16:18:27

if i have like this:
Id BegNo EndNo
1 0001 0005
2 0010 0020

Out put looks like :
Id Remarks:
1 006-009

How do i get hole no too

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 16:22:13

Didn't get it?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 16:23:40

no i jut get 6-9
it suppose to be 0006-0009

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 16:28:00

My BegNo and EndNo are also varchar
so i tried this:
but not getting result

Update b
set b.remarks = Case when Convert(varchar(50),b.begno)>convert(varchar(50),(a.endno+1)) then b.Col2+Convert(varchar(50),(a.endno+1)) +'-'+ b.Col2+Convert(varchar(50),(b.begno-1))
Else NULL
end
from table_1 a Join table_1 b
on a.id+1 = b.id

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 16:45:54

How do i filter only 000 from begno and endno? If i can filter 000, i can add 000 on new updated filed

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 16:53:26

Why you keep on changing requirement? Also why is it stored in varchar?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 17:02:57

no no i am not changing my reqirment because i need 000 too so.
i tried to convert like this
cast(substring(begno,1,10) as varchar(10))

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 17:28:01

Sodeep, did u get how to get 000 to on updated field.
I want :
Id Remarks:
1 006-009

quote:

Originally posted by sodeep

Update b
set b.remarks = Case when b.begno>(a.endno+1) then Convert(varchar(10),(a.endno+1)) +'-'+ Convert(varchar(10,(b.begno-1))
Else NULL
end
from table_1 a Join table_1 b 
on a.id+1 = b.id

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 17:46:16

any body has script to display number on 3 digit format like : 003, 030, 300
the above script i need to display on 3 digit format

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 18:08:33

Try this.

DECLARE @t TABLE (begno varchar(50),endno varchar(50), remarks varchar(50), id int)
insert @t
select '1','5',null,'1' union all
select '10','20',null,'2'

Update b
set b.remarks = Case when b.begno>(a.endno+1) then Right('000' + Convert(varchar(10),(a.endno+1)),3) +'-'+ Right('000' + Convert(varchar(10),(b.begno-1)),3)
Else NULL
end
from @t a Join @t b
on a.id+1 = b.id

select * from @t

gives
1 5 NULL 1
10 20 006-009 2

hope this is what you need.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 18:13:23

[code]Update b
Set b.remarks = Case when b.begno>(a.endno+1) then
(Case len('000'+ Convert(varchar(10),(a.endno+1)))
When 5 then
Substring('000'+ Convert(varchar(10),(a.endno+1)),2,4) + '-' +
Substring('000'+ Convert(varchar(10),(b.begno-1)),2,4)
When 6 then
Substring('000'+ Convert(varchar(10),(a.endno+1)),3,4) + '-' +
Substring('000' + Convert(varchar(10),(b.begno-1)),3,4)
when 7 then
Substring('000'+ Convert(varchar(10),(a.endno+1)),4,4) + '-' +
Substring('000' + Convert(varchar(10),(b.begno-1)),4,4)
Else '000'+ Convert(varchar(10),(a.endno+1))+'-'+'000' + Convert(varchar(10),(b.begno-1) )
End)
Else NULL
end
from table_1 a INNER JOIN table_1 b
on a.id+1 = b.id[/code]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-19 : 18:22:14

great job
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources