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
jplatovskyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Jim HornMicrosoft 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

Open in new window

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
awking00Information Technology SpecialistCommented:
select *
from table
where [net charge amount] / [charge amount] >= .8
and [net charge amount] / [charge amount] < 1
2
Mark WillsTopic AdvisorCommented:
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
From <your table>
where ...

Open in new window


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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Mark WillsTopic AdvisorCommented:
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

From < your table >

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 

Open in new window

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
Chris LuttrellSenior 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;

Open in new window

2
jplatovskyAuthor Commented:
I apologize.  I was focused on the Exists statement but I do not need it.
0
jplatovskyAuthor Commented:
This is what i want but i get a Divide By Zero Error
((AdjustedChargeAmount / ChargeAmount) *100) >= 80 and ((AdjustedChargeAmount / ChargeAmount) *100) < 100
0
Mark WillsTopic AdvisorCommented:
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

Open in new window

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

Open in new window


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

Open in new window


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

Your issues matter to us.

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

Start your 7-day free trial
awking00Information 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
jplatovskyAuthor 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.