Click here to Skip to main content
15,901,666 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have two tables one is batches
Batch_Name      ||           Scan_Folder
EMPO-002-091015	||   \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\          
EMPO-003-091015	||   \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\          

IRN          ||      Customer Name      ||     DTL_Status      || Batch_Name
1111         ||       SAM               ||        NI           || EMPO-002-091015
2222         ||       CLIFF             ||        NI           || EMPO-002-091015
3333         ||       MARY              ||        CO           || EMPO-003-091015
4444         ||       SANJAY            ||        NI           || EMPO-003-091015

I want Batch_Name, Scan_Folder of those batches who has all invoices in NI Status.
Ans: for above table should be
EMPO-002-091015 || \\ldatasrv\Demo1\Genric_EmailImptr_Demo\EmailBackupPath\20150910\
because EMPO-002-091015 has all invoices against it in NI Status.

Please help me for this query i am not able to understand how to initiate it.

Thanks & Regards,
Sunil Mali.

What I have tried:

select b.* from batches b,invoice i
where i.Batch_name=b.Batch_name
and b.DTL....
I am confused...

select distinct batch_name,count(DTL_Status),DTL_Status
from invpagebkp
group by Batch_Name,DTL_Status
having Batch_name='MRAB-0010-011116' order by batch_name
Updated 8-Feb-16 1:41am

1 solution

There are a couple of ways you can do this...
This query will get the number of dtl_status for each batch_name
SELECT Batch_Name, DTL_Status, Count(*) as NIcount from invoices
where DTL_Status = 'NI'
group by Batch_Name, DTL_Status
and this one will get the total number of invoices per batch_name
SELECT Batch_Name, Count(*) as AllCount from invoices
	group by Batch_Name
You can combine these two queries (I'm using two Common Table Expressions below - see CTE In SQL Server[^]), and join back to your Batches table to get the rest of the data when the count of NI status is the same as the total number of invoices...
;with t1 as (
	SELECT Batch_Name, DTL_Status, Count(*) as NIcount from invoices
	where DTL_Status = 'NI'
	group by Batch_Name, DTL_Status
, t2 as (SELECT Batch_Name, Count(*) as AllCount from invoices
	group by Batch_Name)
select b.Batch_Name, b.Scan_Folder
from t1
join t2 on t1.Batch_Name = t2.Batch_Name
join batches b on t1.Batch_Name = b.Batch_Name
where t1.NICount = t2.AllCount

This can be done even more neatly (if you have SQL Server 2005 or later) by using the OVER[^] clause like this
;with temp as 
	Select Distinct Batch_Name, Count(*) OVER(PARTITION BY Batch_Name) AS Total
		, count(*) OVER(PARTITION BY Batch_Name, DTL_Status) AS NIStatus
	from invoices
FROM batches b 
join temp on b.Batch_Name = temp.Batch_Name
where Total = NIStatus
Share this answer
CHill60 8-Feb-16 10:39am    
Joy - I point out the mess in Solution 1 so the poster down-votes my solution in retaliation before deleting their rubbish!
Maciej Los 8-Feb-16 15:50pm    
CHill60 9-Feb-16 3:32am    
Thank you! Long time no see, hope you are well.
Maciej Los 9-Feb-16 12:50pm    
Thank you, Caroline. I'm fine. I spend winter holidays in Cavalese (Italia). It was great!
CHill60 9-Feb-16 19:18pm    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900