SQL query Find all product which has increased value at certain 2 dates

Question

I am wondering if I can do this using SQL query. I have a table called "data" which contains the product name, date and sale number.

my table look like this:

Product         date       Sale

apple           1/1/2019   5
apple           2/1/2019   4
apple           3/1/2019   3
apple           4/1/2019   2
apple           5/1/2019   1
orange          1/1/2019   1
orange          2/1/2019   2
orange          3/1/2019   3
orange          4/1/2019   4
orange          5/1/2019   5
pear            1/1/2019   6
pear            2/1/2019   4
pear            3/1/2019   3
pear            4/1/2019   2
pear            5/1/2019   5
strawberry      1/1/2019   6
strawberry      2/1/2019   3 
strawberry      3/1/2019   7  
strawberry      4/1/2019   4
strawberry      5/1/2019   2

I would like to set a SQL query to find the product(s) which have increase sale number at certain 2 dates.

e.g. find all product which the sale number on 3/1/2019 is greater then 1/1/2019 and it should return orange and strawberry.

I am quite new into the programming world and any help is appreciated. Thanks in advance!

It's not a free coding site. Kindly share your effort first then the community will contribute to help you out for specific problem. — Muhammad Waheed, Feb 11 '19 at 06:51
I am not familiar with sql. But my best try is to SELECT product, date FROM data WHERE date = xxxx OR date = xxxx. It gives a table but I dont know how to make the comparison afterwards. — Max Cheung, Feb 11 '19 at 06:54
It's ok. let me know about which database server are you using ? (Oracle,SQL Server,MySQL?) — Muhammad Waheed, Feb 11 '19 at 06:57
1. Fix your schema. Store dates as dates. Then see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 11 '19 at 08:30

Fahmi · Answer 1 · 2019-02-11T07:07:26.837

1

You can try using correlated subquery

DEMO

select name,sdate,amount from data a where exists 
  (
   select 1 from data b where a.name=b.name and b.sdate in ('1/1/2019','3/1/2019') and b.amount>a.amount
   and a.sdate<b.sdate
  ) 
and a.sdate in ('1/1/2019','3/1/2019')

OUTPUT:

name        sdate               amount
orange      01/01/2019 00:00:00   1
strawberry  01/01/2019 00:00:00   6

edited Feb 11 '19 at 07:07

answered Feb 11 '19 at 06:58

Fahmi

37,315
5
22
31

thanks for your answer. I dont quite understand what is t1 a and t1 b. I will check the correlated subquery out and study a bit on your suggested code. – Max Cheung Feb 11 '19 at 07:06
In what sense is one date greater than another (with the obvious exception of my birthday)? – Strawberry Feb 11 '19 at 08:29

score 0 · Answer 2 · answered Feb 11 '19 at 07:14

DECLARE @FasatDate Date ='3-1-2019'
DECLARE @SecondDate Date ='1-1-2019'

SELECT T1.Product,T1.[date] FasatDate,T1.Sale FasatDateSale,T2.[date] SecondDate,T2.Sale SecondDateSale FROM (
SELECT * FROM DATA AS [DA]
WHERE [DA].[date]=@FasatDate) T1

INNER JOIN  (
SELECT * FROM DATA AS [DA]
WHERE [DA].[date]=@SecondDate)T2
on
t1.Product = t2.Product AND t1.Sale>t2.Sale

score 0 · Answer 3 · answered Feb 11 '19 at 13:00

You can do this as a JOIN, but I would recommend:

select t1.*, t2.sales
from t t1 join
     t t2
     on t2.product = t1.product and
        t2.sales > t1.sales
where t1.date = '1/1/2019'
      t2.date = '3/1/2019';

Note: You should use standard date formats for your date constants -- either '2019-01-03' or '2019-03-01' depending on what you mean.

You can also do this using aggregation:

select product
from t
where t.date in ('1/1/2019', '3/1/2019')
group by product
having (sum(case when t.date = '1/1/2019' then sales end) <
        sum(case when t.date = '3/1/2019' then sales end)
       );

SQL query Find all product which has increased value at certain 2 dates

3 Answers3