Filter condition on a calculated column

Other SQL Server Topics
,
1

I have created a column in a query within SQL server 2016 which is called "DiscPercent" - other words: Discount%
The data/output is working fine but I want to know how I can filter out this column to show only DiscPercent greater than 50%. As this is a calculated column, not sure how to create this condition.
Note: There are multiple where conditions with AND operator and this will make it the 4th condition

2
Date ID Brand Product Sales Disc DiscPercent
01/01/2022 aaa YYY YYYY IDN 72.86 13.5 0.156322
01/01/2022 bbb ZZZ ZZZ EWER 614.3 105 0.729875
01/01/2022 ccc TRRRR TRRRR Life 4457.1 3120 12.35301

This is how my data looks but I just realized that my discpercentage is not calculating correctly

3

I love CROSS APPLY for creating the value, because it allows you to assign a name to that value, just like any other name, i.e., it can be used in a WHERE clause (or join or whatever).


CREATE TABLE #data ( 
Date date NOT NULL,
ID varchar(10) NOT NULL,
Brand varchar(30) NULL,
Product varchar(30) NOT NULL,
Sales decimal(9, 2) NULL,
Disc decimal(9, 2) NULL
)

INSERT INTO #data VALUES
('01/01/2022', 'aaa', 'YYY', 'YYYY IDN', 72.86, 13.5),
('01/01/2022', 'bbb', 'ZZZ', 'ZZZ EWER', 614.3, 105),
('01/01/2022', 'ccc', 'TRRRR', 'TRRRR Life', 4457.1, 3120)


SELECT Date, ID, Brand, Product, Sales, Disc, [Discount%]
FROM #data
CROSS APPLY (
    SELECT CAST(Disc * 100.00 / Sales AS decimal(4, 1)) AS [Discount%]
) AS calc1
WHERE [Discount%] > 50.0
4

Thank you for this... makes sense now

5

You're welcome. Thanks for the feedback.