Click here to Skip to main content
14,971,553 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
   
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*
Maciej Los 10-Feb-16 2:29am
   
;)
sunil mali 9-Feb-16 2:37am
   
Hi Chill60,
Thanks for the quickest solution.
Your first solution is working fine but second one is not working and giving me expected results can you please try to correct it.
I have already accepted your solution.
But it would be great if you can make second query work as it looks optimized.
CHill60 9-Feb-16 3:32am
   
I've just retested the 2nd query and it is working fine on SQL Server 2012 Express. What version of SQL Server are you using? It may be that the SQL Windows functions (OVER) aren't available in your version. In which case use the first query, it's about as optimised as you can get it.

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