# Calculation Value based on two columns

21 Reputation points
2021-02-24T21:24:16.677+00:00

Hi,

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions

1. 10,766 Reputation points
2021-02-24T22:07:49.187+00:00

Hi @Zaran ,

``````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
``````

----------

2. 24,176 Reputation points
2021-02-25T01:39:13.467+00:00

Hi @Zaran ,

Welcome to Microsoft Q&A!

``````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