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?
 
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
 
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
 
awking00Commented:
select *
from table
where [net charge amount] / [charge amount] >= .8
and [net charge amount] / [charge amount] < 1
2
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
 
awking00Commented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.