Select

Transact-SQL
1

Hello,
I would like to have a select query to show all rows except the ones that have 0.00 balance and null payments and null Receipts and narrative = 'opening balance' and are single rows. for example in the above sample data, I do not want to show the rows with A_Code of Code_1 or Code_2 because they do not belong to any other batch in the data. StatementId shows which rows belong to eachother. Code-1 and Code-2 as you see, only have StatementId = 1 and so need to be removed.

sample SQL

create table #tblResult(P_Code varchar(20), A_Code varchar(20), Narrative varchar(100), StatementId int, Payments int null, Receipts int null, Balance float)
insert into #tblResult values('P123', 'xyz', 'some text', 1, 15, NULL, 675)
insert into #tblResult values('P123', 'xyz' , 'some text2' , 2, 8 , 15 , 0.00)
insert into #tblResult values('P123', 'Code-1', 'Opening Balance' , 1, NULL, NULL , 0.00)
insert into #tblResult values('P123', 'code-2', 'Opening Balance' , 1, NULL, NULL , 0.00)
insert into #tblResult values('P123', 'dev1' , 'Opening Balance' , 1, NULL, NULL , 432)
insert into #tblResult values('P123', 'dev1' , 'Opening Balance' , 2, NULL, NULL , 675)
insert into #tblResult values('P765', 'data1' , 'sometext trns' , 1, NULL, 99 , 0.00)
insert into #tblResult values('P765', 'data1' , 'sometext trns54' , 2, 8 , 6 , 43)

select * from #tblResult
drop table #tblResultPreformatted text

Thank you

2

how about this ??

select * from #tblResult
where Narrative = 'Opening Balance' and StatementId = 2
union all
select * from #tblResult where balance <> 0 and Payments is not null and receipts is not null

image

3

Hi. Thanks for the message but it is not what I am after.
As you see in my post I mentioned that the rows 'Code-1' and 'Code-2' for column A_Code should not be shown. Everything else to be shown.
Thank you

4 
select 
  * 
from 
  #tblResult
where 
  P_Code+A_Code 
      in 
 ( select   P_Code+A_Code   from #tblResult where  StatementId = 2 )

image

5

Thank you

6

That's an inefficient and risky way to do this. This method is cleaner:

SELECT *
FROM #tblResult TR
WHERE 
    EXISTS(
        SELECT 1
        FROM #tblResult TR2
        WHERE TR2.P_Code = TR.P_Code AND TR2.A_Code = TR.A_Code AND 
            TR2.StatementId = 2
    )