SQL Query help needed

Question

Hi All, Could you help me to get the expected output using SQL Query?

Table Records
Product Version Status
P1 QV1.1 In progress
P1 QV1.2 Expired
P1 QV1.3 Expired
P1 QV1.4 Expired
P1 QV1.5 Cancelled
P2 QV2.1 - Valdiated (Inactive)
P2 QV2.2 - Deactivated
P2 QV2.3 - Expired Expired
P2 QV2.4 - Expired Expired
P2 QV2.5 - Cancelled Cancelled
P3 P3.1 Cancelled
P4 P4.1 Expired
P5 P5.1 Deactivated

Expected Output:
Product Version Status
P1 QV1.1 In progress
P1 QV1.2 Expired
P2 QV2.1 - Valdiated (Inactive)
P2 QV2.2 - Deactivated
P4 P4.1 Expired
P5 P5.1 Deactivated

Thank you for contacting.

1. Only Cancelled status -> Not considered
2. Cancelled and Expired version -> Expired
3. Cancelled and expired and deactived product -> expired or deactivated which is latest
4. Cancelled and expired and deactivate and Validated (active) and Validated(Inactive) -> Validated(Inactive)+Validated(active)
5. Cancelled and expired and deactivate and and Validated(Inactive) ->deactivate and and Validated(Inactive)

Answer 1

Hi @Srikanth Kota ,

Welcome to the Microsoft TSQL Q&A Forum!

You seem to want to output a different status for each product (cancelled,not output).But your rule description and data do not seem to correspond. As far as I understand , it seems that certain status can be classified as another type of status, such as Cancelled and Expired version as Expired.

You can try:

create table #test(Product char(15), Version char(25), Status char(25))  
insert into #test values('P1', 'QV1.1', 'In progress'),  
('P1', 'QV1.2', 'Expired'),('P1', 'QV1.3', 'Expired'),('P1', 'QV1.4' ,'Expired'),  
('P1', 'QV1.5', 'Cancelled'),('P2', 'QV2.1', '- Valdiated (Inactive)'),  
('P2' ,'QV2.2', '- Deactivated'),('P2', 'QV2.3', '- Expired Expired'),  
('P2' ,'QV2.4', '- Cancelled Expired'),('P2', 'QV2.5', '- Cancelled Cancelled'),  
('P3' ,'P3.1', 'Cancelled'),('P4', 'P4.1', 'Expired'),('P5' ,'P5.1' ,'Deactivated')  
  
  
;with cte  
as(select Product, [Version],   
case when [Status] ='- Cancelled Expired' then 'Expired' else [Status] end [Status]  
from #test  
where [status]<>'Cancelled')  
,cte2 as(select *,row_number() over(partition by Product,[Status] order by [Status]) rr from cte)  
  
select Product, [Version],[status] from cte2  
where rr<2  

To illustrate the calculation rules, I have slightly changed your data.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

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.