Calculation Value based on two columns

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

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

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

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nasreen Akter 10,756 Reputation points
    2021-02-24T22:07:49.187+00:00

    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

    71660-img17.jpg

    ----------

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

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

    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.

    0 comments