max invoice date and max sales amount

Question

max invoice date and max sales amount

Shambhu Rai 1,411

Hi Expert,

I am having below table and expected max invoice and sales date as output here is sql query

create table table1
(id int, sales int, Saledate date)

create table table2
(id int, sales int, invoicedate date)

insert into table1
values(1, 23, '2022-12-23'),
(2, 24, '2022-12-24'),
(3, 25, '2022-12-25')

insert into table2
values(1, '2022-12-25'),
(2, '2022-12-26'),
(3, '2022-12-27')
![276440-image.png ]1

Olaf Helper 47,441 Reputation points

2023-01-05T13:15:11.33+00:00

And what's the logic for the expected result? Just by having a look at, I don't get it.
Shambhu Rai 1,411 Reputation points

2023-01-05T13:27:00.507+00:00

output is expected results
Olaf Helper 47,441 Reputation points

2023-01-05T13:48:25.817+00:00

That was the only clear point.
I asked for the logic of the expected result?

Accepted answer

2 additional answers

Your answer

Olaf Helper 47,441 Reputation points

2023-01-05T13:15:11.33+00:00

And what's the logic for the expected result? Just by having a look at, I don't get it.
Shambhu Rai 1,411 Reputation points

2023-01-05T13:27:00.507+00:00

output is expected results
Olaf Helper 47,441 Reputation points

2023-01-05T13:48:25.817+00:00

That was the only clear point.
I asked for the logic of the expected result?

Answer 1

Jingyang Li 5,896 Volunteer Moderator

create table table1  
(id int, sales int, Saledate date)  
  
  
create table table2  
(id int, sales int, invoicedate date)  
  
insert into table1  
values(1, 23, '2022-12-23'),  
(2, 24, '2022-12-24'),  
(3, 25, '2022-12-25')  
  
insert into table2  
values(1,0, '2022-12-25'),  
(2, 0,'2022-12-26'),  
(3, 0,'2022-12-27')  
  
Select a.id,a.sales,  
Case when A.Saledate=MAX(A.Saledate) Over() then A.Saledate else null End MaxSaledate,  
Case when B.invoicedate=MAX(B.invoicedate) Over() then B.invoicedate else null End Maxinvoicedate   
  
from table1 AS A  
inner join table2 AS B  
on A.id = B.id  
  
   
  
  
  
drop table table1 ,table2

Shambhu Rai 1,411 Reputation points

2023-01-05T14:59:03.103+00:00

Thank you
Shambhu Rai 1,411 Reputation points

2023-01-10T16:02:25.597+00:00

getting duplicate data if i am taking multiple same ids

like id 1 1 1 2 2
Jingyang Li 5,896 Reputation points Volunteer Moderator

2023-01-10T16:08:20.167+00:00

Your tables should have a primary key and you need to define relationship between these tables. Need to see your new data cases to see whether we still have a patch to your query.
Shambhu Rai 1,411 Reputation points

2023-01-10T16:12:27+00:00

Actually the issue is i am having 2 same sale date for id1 so it is taking both for an example

id saledate id 1 2022-12-23 1 2022-12-23

how to take only one sale date id

Jingyang Li 5,896 Volunteer Moderator

You can use a DISTINCT in this case but you need to have better designed table.

 Select 
 DISTINCT 
 a.id,a.sales,
 Case when A.Saledate=MAX(A.Saledate) Over() then A.Saledate else null End MaxSaledate,
 Case when B.invoicedate=MAX(B.invoicedate) Over() then B.invoicedate else null End Maxinvoicedate 
    
 from table1 AS A
 inner join table2 AS B
 on A.id = B.id

Answer 2

Subhrajyoti Ghosh 1

@Shambhu Rai
You did not specify the foreign key to join these two tables. I am assuming the joining column is id. Hence the query should be as follows

Select MAX(A.Saledate), MAX(B.invoicedate)
from table1 AS A
inner join table2 AS B
on A.id = B.id

Shambhu Rai 1,411 Reputation points

2023-01-05T13:52:00.283+00:00

WRONG output
Subhrajyoti Ghosh 1 Reputation point

2023-01-05T13:59:16.14+00:00

Core is correct. Please check below screenshot.

I found one issue in your insert statement for table2. You created table 2 with 3 cols but inserted 2 values. So, I corrected the insert statement.
Subhrajyoti Ghosh 1 Reputation point

2023-01-05T14:04:31.81+00:00

Adding one more thought. I was assuming there is a relationship between those two tables. But if you just want to retrieve the max from each table, you can use two separate queries as well.

SELECT MAX(Saledate) FORM table1

SELECT MAX(invoicedate) FORM table2

Answer 3

Shambhu Rai 1,411

tried to add all columns in table but unable to get right output

Select a.id,a.sales,MAX(A.Saledate), MAX(B.invoicedate)
from table1 AS A
inner join table2 AS B
on A.id = B.id

group by a.id,a.sales

Share via

max invoice date and max sales amount

2 additional answers

Your answer