How to join minus function in query.

Database

How to join minus function in query.

Hi, all 

My query is

What was the date of the earliest sale in the database where the sale price of the item did not equal the original price of the item, and what was the largest margin (original price minus sale price) of an item sold on that earliest date?

Table is trnsact

And columns are original price-->orgprice

                             sale price--->sprice

                                date-->saledate

for earliest sale I use

select top 1 saledate
from trnsact
where sprice<>orgprice
order by saledate asc;

and for Largest margin is

select sprice from trnsact
minus
select orgprice from trnsact

how to make it

1 REPLY
Junior Contributor

Re: How to join minus function in query.

MINUS is a set operator, you need a simple calculation:

select item, saledate,
max(orgprice-sprice)
from
(
select *
from trnsact
qualify -- earliest date where the sale price did not equal the original price
saledate = min(case when sprice<>orgprice then saledate end)
over (partition by item)
) as dt
group by 1,2