Doing some calculations - 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

Doing some calculations

Author

Topic

binuj
Starting Member

5 Posts

Posted - 2008-09-30 : 04:43:38

Dear All,

I have a SQL Server table named TABLE1 which is having 2 columns (location nchar(100), score int). The location column is having 100 records. two sample records are:

1. 82~@$0,3,0,0,0,0,1,0
2. 82~@$0,3,0,2,0,0,1,0

The score column is having value a 0.

My requirement is:

I want to extract the values 300001 (8th index, 10th, index upto 18th index) and divide each value with a particular number (i.e, 3/4, 0/2...like that) and the result for each division should be stored and finally added and stored in score column.

please help me on this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 05:00:48

WHere will you get divisor info from? 4,2...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 05:26:09

Here is everything you need to get help
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

binuj
Starting Member

5 Posts

Posted - 2008-09-30 : 06:00:43

That i have fixed values....3,4,3,3,2,1

binuj
Starting Member

5 Posts

Posted - 2008-09-30 : 06:04:02

Dear All,

I have written a cursor statement...please check whether this is teh best possible way...pleae guide

declare @CustId nchar(100)
declare @first float
declare @second float
declare @third float
declare @four float
declare @five float
declare @six float
declare CustList cursor for
select top 5 location from dbo.TABLE1
OPEN CustList
FETCH NEXT FROM CustList
INTO @CustId
WHILE @@FETCH_STATUS = 0
BEGIN
set @first= (Convert(numeric, SUBSTRING(@custid, 8, 1))/3)*100
print @first
set @second= (Convert(numeric, SUBSTRING(@custid, 10, 1))/4)*100
print @second
set @third= (Convert(numeric, SUBSTRING(@custid, 12, 1))/3)*100
print @third
set @four= (Convert(numeric, SUBSTRING(@custid, 14, 1))/3)*100
print @four
set @five= (Convert(numeric, SUBSTRING(@custid, 16, 1))/2)*100
print @five
set @six= (Convert(numeric, SUBSTRING(@custid, 18, 1))/1)*100
print @six
print ((@first + @second + @third + @four + @five + @six) *100)/600

Update table1
set score=((@first + @second + @third + @four + @five + @six) *100)/600
where location = @custid
FETCH NEXT FROM CustList
INTO @CustId
END
CLOSE CustList
DEALLOCATE CustList
select * from table1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 06:28:21

[code]declare @divisor table
(ID int,
Val int)

insert into @divisor
select 1,3 union all
select 2,4 union all
select 3,3 union all
select 4,3 union all
select 5,2 union all
select 6,1

SELECT r.Val AS Numerator,d.Val AS Denominator,r.Val*1.0/d.Val AS Quotient
FROM
(
SELECT t.ID as TID,t.str,b.ID AS BID,b.Val
FROM
(
SELECT 1 as ID,'82~@$0,3,0,0,0,0,1,0' AS str union all
SELECT 2,'82~@$0,3,0,2,0,0,1,0'
)t
CROSS APPLY dbo.ParseValues(t.str,',')b
)r
JOIN @divisor d
ON d.ID=r.BID-1

output
----------------------
Numerator denominator quotient
3 3 1.0000000000000
0 4 0.0000000000000
0 3 0.0000000000000
0 3 0.0000000000000
0 2 0.0000000000000
1 1 1.0000000000000
3 3 1.0000000000000
0 4 0.0000000000000
2 3 0.6666666666666
0 3 0.0000000000000
0 2 0.0000000000000
1 1 1.0000000000000[/code]
Also parsevalues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485&SearchTerms=parsevalues

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 06:43:17

[code]DECLARE @Sample TABLE
(
ID INT,
Data VARCHAR(200),
Score FLOAT
)

INSERT @Sample
(
ID,
Data
)
SELECT 1, '82~@$0,3,0,0,0,0,1,0' UNION ALL
SELECT 2, '82~@$0,3,0,2,0,0,1,0'

UPDATE @Sample
SET Score = 100.0E0 * (SUBSTRING(Data, 8, 1) / 3.0E0
+ SUBSTRING(Data, 10, 1) / 4.0E0
+ SUBSTRING(Data, 12, 1) / 3.0E0
+ SUBSTRING(Data, 14, 1) / 3.0E0
+ SUBSTRING(Data, 16, 1) / 2.0E0
+ SUBSTRING(Data, 18, 1) / 1.0E0
) / 6.0E0

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources