Click here to Skip to main content
15,436,916 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have two tables one is batches
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\          




Invoices
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
Posted
Updated 8-Feb-16 1:41am
v2

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
SQL
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
SQL
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...
SQL
;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
SQL
;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
)
SELECT b.*
FROM batches b 
join temp on b.Batch_Name = temp.Batch_Name
where Total = NIStatus
 
Share this answer
 
v2
Comments
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    
Upvoted!
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    
*jealous*

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