Calculation Value based on two columns

Question

Hi,

Could you please help to get the below result?
find the maximum [date] for each item, store and f_date when value<>0

Create table test ( Date date, Item varchar(10), store varchar(10), value int,f_date date)
values (2021-02-15','C001','17A',4,'2021-02-21')
,('2021-02-16','C001','17A',4,'2021-02-21')
,('2021-02-17','C001','17A',4,'2021-02-21')
,('2021-02-18','C001','17A',3,'2021-02-21')
,('2021-02-19','C001','17A',0,'2021-02-21')
,('2021-02-20','C001','17A',0,'2021-02-21')
,('2021-02-21','C001','17A',0,'2021-02-21')
,('2021-02-17','B014','17A',9,'2021-02-21')
,('2021-02-18','B014','17A',5,'2021-02-21')
,('2021-02-19','B014','17A',5,'2021-02-21')
,('2021-02-20','B014','17A',4,'2021-02-21')
,('2021-02-21','B014','17A',0,'2021-02-21')
,('2021-02-15','BE50','13B',10,'2021-02-21')
,('2021-02-16','BE50','13B',0,'2021-02-21')
,('2021-02-17','BE50','13B',0,'2021-02-21')
,('2021-02-18','BE50','13B',6,'2021-02-21')
,('2021-02-19','BE50','13B',6,'2021-02-21')
,('2021-02-20','BE50','13B',0,'2021-02-21')
,('2021-02-21','BE50','13B',0,'2021-02-21')
,('2021-02-15','C001','13B',1,'2021-02-21')
,('2021-02-16','C001','13B',1,'2021-02-21')
,('2021-02-17','C001','13B',0,'2021-02-21')
,('2021-02-18','C001','13B',1,'2021-02-21')
,('2021-02-19','C001','13B',1,'2021-02-21')
,('2021-02-20','C001','13B',0,'2021-02-21')
,('2021-02-21','C001','13B',1,'2021-02-21')
,('2021-02-08','BE50','13B',10,'2021-02-14')
,('2021-02-09','BE50','13B',0,'2021-02-14')
,('2021-02-10','BE50','13B',0,'2021-02-14')
,('2021-02-11','BE50','13B',6,'2021-02-14')
,('2021-02-12','BE50','13B',12,'2021-02-14')
,('2021-02-13','BE50','13B',0,'2021-02-14')
,('2021-02-14','BE50','13B',0,'2021-02-14')

result:

Date,Item,Store,Value,f_date

2021-02-18, C001,17A,3,2021-02-21
2021-02-20,B014,17A,4,2021-02-21
2021-02-19,BE50,13B,6,2021-02-21
2021-02-21,C001,13B,1,2021-02-21
2021-02-12,BE50,13B,12,2021-02-14

Thank you

Answer 1

Hi @Zaran ,

Please try the following. Thanks!

SELECT FORMAT(max(date),'yyyy-MM-dd') as max_date,   
 item,   
 store,   
 sum(value) as total_value,   
 FORMAT(f_date, 'yyyy-MM-dd') as f_date  
FROM test  
WHERE value <> 0  
GROUP BY item, store, f_date  
ORDER BY max_date ASC  

----------

If the above response is helpful, please accept as answer and up-vote it. Thanks!

Answer 2

Hi @Zaran ,

Welcome to Microsoft Q&A!

Please refer below:

select a.*   
from test a  
inner join   
(select max(date) date,item,store,f_date from test  
where value<>0 group by item,store,f_date) b   
on a.Item=b.Item and a.store=b.store and a.Date=b.date  

Output:

Date	Item	store	value	f_date  
2021-02-18	C001	17A	3	2021-02-21  
2021-02-20	B014	17A	4	2021-02-21  
2021-02-19	BE50	13B	6	2021-02-21  
2021-02-21	C001	13B	1	2021-02-21  
2021-02-12	BE50	13B	12	2021-02-14  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.