sqlsql-servertemp-tables

How to fetch records having multiple rows with different column values


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

Solution

  • 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.