How to add case condition in sql sub queries

Category: sql server datamining

Question

hi_khan on Wed, 20 Nov 2019 07:13:18


Guys i have a problem in my Sql server sub queries. 

i have two table one is invoice and other is receipt

1)i want to get those invoice_id which is not in receipt as well as their invoice.amount = receipt.paid_amount

2)Also those invoice_id which is in receipt but invoice.Amount > receipt.paid_amount

select student.Student_id as [Ac #],
		 case when invoice.invoice_id in (select Recipt.invoice_id from Recipt) and invoice.Amount>Recipt.Paid_amount 
				THEN (select invoice.Amount-Recipt.Paid_amount from Recipt)
					 ELSE invoice.invoice_id not in (select Recipt.invoice_id from Recipt)
						END as amount,Fee_description as [description],invoice.Concession_amount as [conc.], invoice.month from student
						join 
                        allocate_class on
                        student.Student_id = allocate_class.Student_id
                        join 
                        session on
						allocate_class.session_id = session.session_id
					    right outer join invoice on
					    student.Student_id = invoice.Student_id
						right outer join Recipt on
						invoice.Student_id = Recipt.Student_id
						where invoice.Student_id =115 and session.session_id= 1 and student.status = 'active'