# How to create a SQL statement that checks another value range if true of not

The example is for a school.
they have a few columns where they want to figure out who has a scholarship and is paying 80% to 99.99% of the original tuition.
Charge Amount = \$100
Scholarship Amount = \$5
Net Charge Amount = \$95
so this would be a true result

Charge Amount = \$100
Scholarship Amount = \$35
Net Charge Amount = \$65
so this would be a false result

Charge Amount = \$100
Scholarship Amount = \$0
Net Charge Amount = \$100
so this would be a false result

how do i write an Exists statement that gives me the true results
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Some division and a CASE BLOCK will work..
``````SELECT CASE
WHEN [Charge Amount] = 0 THEN CAST(0 as bit)  -- To prevent divide by zero errors
WHEN 1 - ([Scholarship Amount] / cast([Charge Amount] AS NUMERIC(19,4) > 80.0 then 1 -- the true
ELSE 0 END as YourColumnName
FROM YourTable
``````
This assumes..
• The values are numeric, and not a char type with the \$ in them.
• All columns are in a single table, and you can change the names to work for your situation.
• We don't have to deal with duplicates, or returning values for people not in this table.
4
Information Technology SpecialistCommented:
select *
from table
where [net charge amount] / [charge amount] >= .8
and [net charge amount] / [charge amount] < 1
2
While the above will certainly work for you, I am not sure about your "EXISTS" requirement and is possibly combining both examples above

``````select  ... [charge amount] ,[scholarship amount],[net charge amount],
IIF([charge amount] > 0 and  [net charge amount] < [charge amount] ,
IIF( [net charge amount] / [charge amount] >= .8 , 'True', 'False') , 'False') AS ScholarShip_Test
where ...
``````

And just to be different, I used the IIF(condition,true result,false result) available from SQL2012 - same as CASE, but sometimes easier to read :)  CASE does have the added benefit of exiting after fulfilling the first 'True'  WHEN condition. Can be used in the 'where ...' as well.
0
Not entirely happy with my post above because I didnt really address my concern about "EXISTS" requirement.

If you want to see the information - then include in the SELECT
If you want to filter the information - then include in the WHERE

Which is why I endorsed the first two posts...

And as a better example of what I was trying to say above:
``````select  StudentID -- and / or whatever else you want
, [charge amount] ,[scholarship amount],[net charge amount]
, IIF([charge amount] > 0 and  [net charge amount] < [charge amount] ,
([scholarship amount] *100 / [charge amount]) ,0.00) AS ScholarShip_Percent

where ([charge amount] > 0 and  ([net charge amount] < [charge amount] or [scholarship amount] > 0))

And 'False' = IIF([charge amount] > 0 and  [net charge amount] < [charge amount] ,
IIF( [net charge amount] / [charge amount] >= .8 , 'True', 'False') , 'False')

And 1 = CASE when [charge amount] = 0 then 0
when [scholarship amount] = 0 then 0
when [charge amount] <= [net charge amount] then 0
ELSE 1
END
``````
You can of course mix-n-match whatever columns you need / prefer for calculations which might be significantly different. So, I also tried to mix-n-match some aspects by way of example. Including the CASE (an option to IIF, or, earlier than SQL2012)

Now, those filters in the above example are over the top, you wouldnt really be that excessive....
2
Senior Database ArchitectCommented:
not sure what you are doing that you need an Exists statement?
how do i write an Exists statement that gives me the true results
And you have not given us specifics of the tables, columns and data types, but there are several ways to get the "true" results.  Those above could work or here is another made up example with an Exists
``````CREATE TABLE Student (Id INT, Name VARCHAR(20));
CREATE TABLE Scholarship (Id INT, StudentId INT, [Scholarship Amount] MONEY, [Net Charge Amount] MONEY, [Charge Amount] MONEY);
INSERT INTO dbo.Student ( Id, Name )
VALUES (1, 'Sam'),(2, 'Sally'),(3,'Joe'),(4,'Jane');
INSERT INTO dbo.Scholarship ( Id, StudentId, [Scholarship Amount], [Net Charge Amount], [Charge Amount] )
VALUES	(1, 1, 5, 95, 100), -- true 80 <= x < 100
(2, 2, 35, 65, 100), -- false x < 80
(3, 3, 0, 100, 100), -- false S\$ = 0
(4, 4,null, 100, 100) -- false S\$ is null

SELECT *
FROM Student
WHERE EXISTS (
SELECT *
FROM Scholarship
WHERE Scholarship.StudentId = Student.Id
AND [Scholarship Amount] > 0
AND [Net Charge Amount] / [Charge Amount] >= .8);

DROP TABLE dbo.Student;
DROP TABLE dbo.Scholarship;
``````
2
Author Commented:
I apologize.  I was focused on the Exists statement but I do not need it.
0
Author Commented:
This is what i want but i get a Divide By Zero Error
((AdjustedChargeAmount / ChargeAmount) *100) >= 80 and ((AdjustedChargeAmount / ChargeAmount) *100) < 100
0
Well, there have been a few posts as to how you can check for ZERO.

Either the IIF(condirion,true,false) or the CASE block.

You havent yet told us if :

If you want to see the information - then include in the SELECT
If you want to filter the information - then include in the WHERE

To visualise :
``````        , IIF([Charge Amount]<>0 and [Charge Amount] <> [Net Charge Amount], [Net Charge Amount]/[Charge Amount] * 100 ,0.00) as perc
``````
To Filter :
``````WHERE (IIF([Charge Amount]<>0 and [Charge Amount] <> [Net Charge Amount], [Net Charge Amount]/[Charge Amount] * 100 ,0.00)) >= 80
``````

Or to use the new column names...
``````IIF(ChargeAmount<>0 and ChargeAmount <> AdjustedChargeAmount, AdjustedChargeAmount/ChargeAmount * 100 ,0)
``````

And if doing a filter (ie using a WHERE clause), then maybe eliminate predictable exceptions like ChargeAmount <> 0 and ChargeAmount <> AdjustedChargeAmount
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Information Technology SpecialistCommented:
>>This is what i want but i get a Divide By Zero Error
((AdjustedChargeAmount / ChargeAmount) *100) >= 80 and ((AdjustedChargeAmount / ChargeAmount) *100) < 100<<
As Mark Wills wants to know, are you wanting to use this in the select statement or the where clause? Also, in the case where the charge amount is zero (or perhaps null), what do you want to return (e.g. 'False' or something like 'No Amount Charged' or what)?
0
Author Commented:
thank you everyone for helping
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.