Sql Numbering - 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 2000 Forums

Transact-SQL (2000)

Sql Numbering

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-04-08 : 07:25:04

hi guys i need help with this one....

@mytable

[Type] [Date] [Bill_No]
'c' 10/10/2005 NULL
'p' 10/11/2005 NULL
'p' 10/13/2005 NULL
'c' 11/10/2005 NULL
'c' 12/10/2005 NULL
'p' 12/15/2005 NULL
'c' 01/15/2006 NULL

This is the output that i want

[Type] [Date] [Bill_No]
'c' 10/10/2005 1
'p' 10/11/2005 1
'p' 10/13/2005 1
'c' 11/10/2005 2
'c' 12/10/2005 3
'p' 12/15/2005 3
'c' 01/15/2006 4

for every change in [TYPE] where [TYPE] = 'C'
[BILL_NO] Increments by 1

cursor is very slow..
can you help me build an sql query for this ...
this is how i did it with cursor ...

---------------------------------------------------------------------------------------
declare @bill_no int
declare @date smalldatetime
declare @type char(1)

declare @mytable table ([type] char(1), [date] smalldatetime, [Bill_no] smallint)

insert into @mytable
select 'c', '10/10/2005', null
union all (select 'c', '10/10/2005', null)
union all (select 'p', '10/11/2005', null)
union all (select 'p', '10/13/2005', null)
union all (select 'c', '11/10/2005', null)
union all (select 'c', '12/10/2005', null)
union all (select 'p', '12/15/2005', null)
union all (select 'c', '01/15/2006', null)

set @bill_no = 0

fetch next from @mytable into @type, @date
while @@fetch_status =0
begin

if @type = 'c'
set @bill_no =@bill_no + 1
end

update into @mytable set bill_no = @bill where [date] = @date

fetch next from @mytable into @type,@date
end

select * from @mytable
-----------------------------------------------------------------------------------------

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-08 : 07:35:00

[code]update m
set Bill_no = (select count(*) from @mytable x where x.type = m.type and x.date <= m.date)
from @mytable m[/code]

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-04-08 : 07:38:30

thanks khtan

TCC

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-11 : 06:39:36

Well, I'm confused. The answer doesn't seem to match the question, but there seems to be acknowledgement that it's correct. Ah well, here's my suggestion all the same...

--data
set dateformat mdy
declare @mytable table ([Type] char(1), [Date] datetime, [Bill_No] int)
insert @mytable
          select 'c', '10/10/2005', NULL
union all select 'p', '10/11/2005', NULL
union all select 'p', '10/13/2005', NULL
union all select 'c', '11/10/2005', NULL
union all select 'c', '12/10/2005', NULL
union all select 'p', '12/15/2005', NULL
union all select 'c', '01/15/2006', NULL

--calculation
declare @bill_no int
set @bill_no = 0
update @mytable set Bill_No = @bill_no, @bill_no = case when Type = 'c' then @bill_no + 1 else @bill_no end

--results
select * from @mytable

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources