speedometer query - 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

speedometer query

Author

Topic

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2013-11-10 : 04:57:32

hi team ,
i have came across a logical problen.

there IS a speedometer IN a bike that have AN issue WHERE WHEN it comes TO 5 IN digit it SKIP TO NEXT digit
LIKE IF speedo meter shows 10km actually it runs ONLY 9 km. IF it shows 30 actually it runs ONLY 27 km because it skips
5,15,AND 25 because it CONTAINS 5 .

kindly HELP how can i WRITE query TO get actual kms FOR a given VALUE.

prithvi nath pandey

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-10 : 05:41:54

Try this


select round(km*9/10,0)
  from yourtable

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2013-11-11 : 00:49:34

dear bitsmed query returns 54 when iput value is 60 but it should return 46 because from 50 to 59 all digits contains 5 so it should be ignore

declare @km numeric = 61

select round(@km*9/10,0)

kindly suggest

prithvi nath pandey

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-11 : 11:55:17

quote:
Originally posted by nextaxtion

dear bitsmed query returns 54 when iput value is 60 but it should return 46 because from 50 to 59 all digits contains 5 so it should be ignore

declare @km numeric = 61

select round(@km*9/10,0)

kindly suggest

prithvi nath pandey

Ahh, well in this case, try this:


declare @km numeric=60

select @km-sum(sign(charindex('5',convert(varchar,number))))
  from master.dbo.spt_values
 where name is null
   and type='P'
   and number<=@km

btw.: Shouldn't 60 be converted to 45?

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2013-11-11 : 12:45:37

please suggest if it can be done without using spt_values

prithvi nath pandey

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-11 : 13:12:41

quote:
Originally posted by nextaxtion

please suggest if it can be done without using spt_values

prithvi nath pandey

How about this:


declare @km numeric=60;

with cte as (
   select 1 as km
   union all
   select km+1
     from cte
    where km<@km
)
select @km-sum(sign(charindex('5',convert(varchar,km))))
  from cte

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-11-11 : 19:52:55

Your examples are confusing unless I assume that they are typos. Why would 30 yield 27? Why would 54 yield 46? If I can assume that your keyboard skills are in disarray...[CODE]declare @km numeric=60;

with cte as (
select 1 as km
union all
select km+1
from cte
where km<@km
)
select max(km)
from cte
where km % 10 <> 0 -- Not a multiple of 10 (sic)
and cast(km as varchar(10)) not like '%5%' -- No embedded '5'[/CODE]HTH and good luck on your finals!

=================================================
No, no, you're not thinking, you're just being logical. -Niels Bohr

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2013-11-12 : 04:07:38

quote:
Originally posted by bitsmed

quote:
Originally posted by nextaxtion

please suggest if it can be done without using spt_values

prithvi nath pandey

How about this:

declare @km numeric=60;

with cte as (
select 1 as km
union all
select km+1
from cte
where km<@km
)
select @km-sum(sign(charindex('5',convert(varchar,km))))
from cte

SUPERB...

prithvi nath pandey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources