sql logic query

Question

Hi All,

I have a sql query which is giving all the transaction ids value, now we have one transaction id which is having four rows data and in the output instead of four rows it should be two row for that specific transaction only.

Client is asking to get all the data as well as that specific transaction id which is having two rows instead of four rows.

I can get two rows using separate query but not sure how to add this condition on the original query.

Please help

Answer 1

Hi @deepika omer ,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

You could refer below simple example:

Suppose we have one table with two columns (transactionid and transactionname) and the transaction id 1001 could output four rows. And we have another table with one column transactionname and another column createdate.

We could output only two rows in the separate query with the condition that the creation date is within past three days.

declare @t1 table  
(transactionid int,  
transactionname varchar(10))  
  
insert into @t1 values  
(1001,'A'),  
(1001,'B'),  
(1001,'C'),  
(1001,'D')  
  
declare @t2 table  
(  
transactionname varchar(10),  
createdate date)  
  
insert into @t2 values  
('A','2021-05-10'),  
('B','2021-05-19'),  
('C','2021-05-18'),  
('D','2021-05-16')  
  
select * from @t1   
where transactionname in (  
select transactionname from @t2 where DATEDIFF(DAY,createdate,GETDATE())<=3)  

OR:

select a.*   
from @t1 a  
left join @t2 b  
on a.transactionname=b.transactionname  
where DATEDIFF(DAY,createdate,GETDATE())<=3  

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.