I have a table disp table. Which has 3 columns, service_code, row_status and business_unit_id.
service_code | row_status | business_unit_id |
---|---|---|
A | 1 | BU1 |
A | 0 | BU2 |
A | 0 | BU3 |
B | 1 | BU1 |
B | 0 | BU2 |
C | 0 | BU2 |
C | 0 | BU3 |
I need a query that will fetch me service_code which is 100% row_status = 0 across all the business_unit_id. So in the above case the answer should be C. The table that i am working on has millions of records. How can i figure out which service_code has row_status = 0 at every business_unit_id? I have written a query but it seems its returning A, B, C. That is not correct.
create table #tmpTable (pc_ccs_9xx_service_code varchar(10));
create table #tmpTable1 (pc_ccs_9xx_service_code varchar(10));
insert into #tmpTable
select top 500
pcdis.service_code
from pc_ccs_9xx_deposit_install_service pcdis
where pcdis.row_status = 1
order by pcdis.service_code asc
select * from #tmpTable
insert into #tmpTable1
select top 500 pcdis.service_code from pc_ccs_9xx_deposit_install_service pcdis
join #tmpTable tt on tt.pc_ccs_9xx_service_code = pcdis.service_code and pcdis.row_status = 1
order by pcdis.service_code asc
select top 500 tt.pc_ccs_9xx_service_code from #tmpTable tt
join #tmpTable1 tt1 on --tt1.pc_ccs_9xx_service_code = tt.pc_ccs_9xx_service_code and
tt1.pc_ccs_9xx_service_code not in (select top 100 pc_ccs_9xx_service_code from #tmpTable)
order by tt.pc_ccs_9xx_service_code asc
DROP table #tmpTable
DROP table #tmpTable1
WITH YOUR_TABLE_DATA (service_code,row_status,business_unit_id)AS
(
SELECT 'A', 1, 'BU1' UNION ALL
SELECT 'A' , 0, 'BU2' UNION ALL
SELECT 'A' , 0, 'BU3' UNION ALL
SELECT 'B', 1, 'BU1' UNION ALL
SELECT 'B', 0, 'BU2' UNION ALL
SELECT 'C', 0, 'BU2' UNION ALL
SELECT 'C' , 0, 'BU3'
)
SELECT C.service_code
FROM YOUR_TABLE_DATA AS C
GROUP BY C.service_code
HAVING MIN(C.row_status)=MAX(C.row_status)
AND MIN(C.row_status)=0
Please try this query.