Count using non integers - 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

General SQL Server Forums

New to SQL Server Programming

Count using non integers

Author

Topic

eembme
Starting Member

18 Posts

Posted - 2007-02-23 : 09:07:57

1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01

I am not sure how to count using non integers

Thanks

Andraax
Aged Yak Warrior

790 Posts

Posted - 2007-02-23 : 09:14:50

Hey!

Maybe something like:

sum(case when type='unscheduled' then 0.1 else 1 end) as thaCount

eembme
Starting Member

18 Posts

Posted - 2007-02-23 : 09:33:55

It needs to incrementental increase (like a typical row number, but with non integers)
1
2
3
3.01
3.02
3.03
4
5
5.01
5.02
6

and it needs to restart every time there is an unscheduled visit.

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-23 : 09:44:10

INSERT INTO Table(Visit)
SELECT MAX(Visit) + .01
FROM Table
WHERE [id] = @x

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-02-25 : 15:13:14

What happens after the 99th unscheduled visit? Yeah, I know... that's a lot, but it could happen and then you're toast. Keep the sub-visits in a separate column so you don't have to worry about 3.99 and the like.

--Jeff Moden

Fatbug
Starting Member

5 Posts

Posted - 2007-02-25 : 20:53:20

--if visit type was denoted by 's' for scheduled and 'u' for unscheduled

create procedure sp_LogVisit @visittype char(1)
AS

--Declare variables
declare @visitnum dec(8,2)
declare @lastvisit dec(8,2)

--Get last recorded visit number
select @lastvisit = isnull((select max(visit_num) from zzLoggedVisits), 0)
its)), 0)

--If visit is scheduled then increment the visit_num by 1
if @visittype = 's'
begin
select @visitnum = cast(@lastvisit + 1 as int)

insert into (LOGTABLE)
values(@visitnum)
end
--Otherwise increment the visit_num by .01
if @visittype = 'u'
begin
select @visitnum = @lastvisit + 0.01

insert into (LOGTABLE)
values(@visitnum)
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-25 : 21:10:29

quote:
Originally posted by Fatbug

--if visit type was denoted by 's' for scheduled and 'u' for unscheduled

create procedure sp_LogVisit @visittype char(1)
AS

--Declare variables
declare @visitnum dec(8,2)
declare @lastvisit dec(8,2)

--Get last recorded visit number
select @lastvisit = isnull((select max(visit_num) from zzLoggedVisits), 0)
its)), 0)

--If visit is scheduled then increment the visit_num by 1
if @visittype = 's'
begin
select @visitnum = cast(@lastvisit + 1 as int)

insert into (LOGTABLE)
values(@visitnum)
end
--Otherwise increment the visit_num by .01
if @visittype = 'u'
begin
select @visitnum = @lastvisit + 0.01

insert into (LOGTABLE)
values(@visitnum)
end

Can be simplified to


insert  into LOGTABLE (visit_num)
select	floor(max(visit_num)) + case @visittype when 's' then 1 else 0.01 end
from	LOGTABLE

scmay
Starting Member

22 Posts

Posted - 2007-02-25 : 22:25:51

Can someone tell me briefly what is
dec(8,2) in the statement
declare @visitnum dec(8,2)?
Thanks
-encora imparo-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-25 : 22:31:03

quote:
Originally posted by scmay

Can someone tell me briefly what is
dec(8,2) in the statement
declare @visitnum dec(8,2)?
Thanks
-encora imparo-

dec is decimal
(8,2) means precision if 8 with scale of 2

These information can be easily found in the Books OnLine

quote:

decimal and numeric
Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Fatbug
Starting Member

5 Posts

Posted - 2007-02-26 : 18:53:22

quote:

Can be simplified to

insert into LOGTABLE (visit_num)
select floor(max(visit_num)) + case @visittype when 's' then 1 else 0.01 end
from LOGTABLE

Hey thanks for cutting the code down khtan

.

But using the same idea codewise, can you change it so that visit_num will increment by .01 upon multiple consecutive unscheduled visits?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 03:20:41

[code]
insert into LOGTABLE (visit_num)
select case @visittype
when 's'
then isnull(floor(max(visit_num)), 0) + 1
else isnull(max(visit_num), 0) + 0.01
end
from LOGTABLE
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources