Identify Products where Quantity Decimal Value is > 0

Question

Post reply

Identify Products where Quantity Decimal Value is > 0

2Tall

SSChampion

Points: 14632
More actions
July 28, 2010 at 5:23 am

#221813

I would like to identify all products that have a decimal quantity value > 0.
Product A Quantity 10.25
Product B Quantity 10.00
Query would return Product A.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

will "modulo" help ? (see BOL)

select 10.25 % 1

select 10.00 % 1

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT [product], [quantity], [INTquantity] = CAST([quantity] AS INT)

FROM (

SELECT 1 AS [product], 2.21 AS [quantity] UNION ALL

SELECT 2, 2.01 UNION ALL

SELECT 3, 2.00

) d

WHERE [quantity] > CAST([quantity] AS INT)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Amit kulkarni-491236 Default port Points: 1492 More actions · Answer 3

consider table Product

ProductID Quantity

A 10.25

B 10.00

select * from product where Quantity - floor(Quantity) >0

this will return Product A

thanks,

Amit kulkarni

2Tall SSChampion Points: 14632 More actions · Answer 4

Thanks guys. Amit your example worked a treat.

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper