Search code examples
sqlexcelvba

Excel VBA create 2 temp tables and enter the output into a third temp table and output to excel sheet

Outputting data from sql query as below:

//Comments: Create first temp table #qcdbTemp1 for test replicate 1

select s1.TEXT_ID as [OrigSamp] ,s2.TEXT_ID as [DupSamp1], cast(r1.FORMATTED_ENTRY as float) as [OrigRes], cast(r2.FORMATTED_ENTRY as float) as [DupRes1], 
(ROW_NUMBER()over(partition by s1.TEXT_ID order by s1.TEXT_ID desc)) as ID  
INTO #qcdbTemp1
from SAMPLE s1 
join SAMPLE s2 on s1.SAMPLE_NUMBER = s2.SAMPLE_NUMBER 
join TEST t1 on t1.SAMPLE_NUMBER = s1.SAMPLE_NUMBER  and t1.REPLICATE_COUNT = 1 
join RESULT r1 on r1.TEST_NUMBER = t1.TEST_NUMBER and r1.REPLICATE_COUNT = 1 
join RESULT r2 on r2.TEST_NUMBER = t1.TEST_NUMBER and r1.NAME = r2.NAME and r2.REPLICATE_COUNT > 1 
where s2.SAMPLE_NAME <> 'DUP' and s1.SAMPLE_NAME <> 'DUP'  and s1.TEXT_ID = 'A' 
and r1.ANALYSIS in ('Density')  and r2.ANALYSIS in ('Density') and r1.REPORTABLE = 'T' 
and r1.NAME in ('BSG') and r2.NAME in ('BSG')

//Comments: Create second temp table #qcdbTemp2 for test replicate 2

select s1.TEXT_ID as [OrigSamp] ,s2.TEXT_ID as [DupSamp1], cast(r1.FORMATTED_ENTRY as float) as [OrigRes], cast(r2.FORMATTED_ENTRY as float) as [DupRes1], 
(ROW_NUMBER()over(partition by s1.TEXT_ID order by s1.TEXT_ID desc)) as ID  
INTO #qcdbTemp2
from SAMPLE s1 
join SAMPLE s2 on s1.SAMPLE_NUMBER = s2.SAMPLE_NUMBER 
join TEST t1 on t1.SAMPLE_NUMBER = s1.SAMPLE_NUMBER  and t1.REPLICATE_COUNT = 2 
join RESULT r1 on r1.TEST_NUMBER = t1.TEST_NUMBER and r1.REPLICATE_COUNT = 1 
join RESULT r2 on r2.TEST_NUMBER = t1.TEST_NUMBER and r1.NAME = r2.NAME and r2.REPLICATE_COUNT > 1 
where s2.SAMPLE_NAME <> 'DUP' and s1.SAMPLE_NAME <> 'DUP'  and s1.TEXT_ID = 'A' 
and r1.ANALYSIS in ('Density') and r2.ANALYSIS in ('Density')  and r1.REPORTABLE = 'T' 
and r1.NAME in ('BSG') and r2.NAME in ('BSG')

//Combine the output from the temp tables to the excel sheet and then drop the two temp tables.

Select   distinct OrigSamp ,
(select OrigRes from #qcdbTemp1 r0 where r0.OrigSamp = #qcdbTemp1.OrigSamp and ID=1 ) res0 , 
 (select DupRes1 from #qcdbTemp1 r1 where r1.OrigSamp = #qcdbTemp1.OrigSamp and ID=1 ) res1 ,
  (select DupRes1 from #qcdbTemp1 r2 where r2.OrigSamp = #qcdbTemp1.OrigSamp and ID=2 ) res2 

 from #qcdbTemp1  where 
 (select DupSamp1 from #qcdbTemp1 a1 where a1.OrigSamp = #qcdbTemp1.OrigSamp and ID=1) is not null 

 union all 

 Select   distinct OrigSamp ,
(select OrigRes from #qcdbTemp2 r0 where r0.OrigSamp = #qcdbTemp2.OrigSamp and ID=1 ) res0 , 
 (select DupRes1 from #qcdbTemp2 r1 where r1.OrigSamp = #qcdbTemp2.OrigSamp and ID=1 ) res1,
 (select DupRes1 from #qcdbTemp2 r2 where r2.OrigSamp = #qcdbTemp2.OrigSamp and ID=2 ) res2 

 from #qcdbTemp2  where 
 (select DupSamp1 from #qcdbTemp2 a1 where a1.OrigSamp = #qcdbTemp2.OrigSamp and ID=1) is not null 
drop table #qcdbTemp1 
drop table #qcdbTemp2

enter image description here

NEED HELP with the following:

  1. sql query solution to get unique IDs so that I can manage with one temp table instead of 2. This will make it easier to get the final output.
Solution

  • Here's how you can get all the temp table data into one table.

    Add t1.REPLICATE_COUNT to the row number partition...

    (ROW_NUMBER() over (partition by t1.REPLICATE_COUNT, s1.TEXT_ID order by s1.TEXT_ID desc)) as ID,

    And to the SELECT list...

    t1.REPLICATE_COUNT as TestReplicateCount

    Then change the join of TEST to...

    join TEST t1 
    on t1.SAMPLE_NUMBER = s1.SAMPLE_NUMBER  
    and t1.REPLICATE_COUNT IN (1, 2)  -- Assuming there are other values that you don't want

    Full query...

    select 
    s1.TEXT_ID as [OrigSamp], 
    s2.TEXT_ID as [DupSamp1], 
    cast(r1.FORMATTED_ENTRY as float) as [OrigRes], 
    cast(r2.FORMATTED_ENTRY as float) as [DupRes1], 
    (ROW_NUMBER() over (partition by t1.REPLICATE_COUNT, s1.TEXT_ID order by s1.TEXT_ID desc)) as ID,
    t1.REPLICATE_COUNT as TestReplicateCount
INTO #qcdbTemp
from SAMPLE s1 
join SAMPLE s2 
    on s1.SAMPLE_NUMBER = s2.SAMPLE_NUMBER 
join TEST t1 
    on t1.SAMPLE_NUMBER = s1.SAMPLE_NUMBER  
    and t1.REPLICATE_COUNT IN (1, 2) 
join RESULT r1 
    on r1.TEST_NUMBER = t1.TEST_NUMBER 
    and r1.REPLICATE_COUNT = 1 
join RESULT r2 
    on r2.TEST_NUMBER = t1.TEST_NUMBER 
    and r1.NAME = r2.NAME 
    and r2.REPLICATE_COUNT > 1 
where s2.SAMPLE_NAME <> 'DUP' 
and s1.SAMPLE_NAME <> 'DUP'  
and s1.TEXT_ID = 'A' 
and r1.ANALYSIS in ('Density')  
and r2.ANALYSIS in ('Density') 
and r1.REPORTABLE = 'T' 
and r1.NAME in ('BSG') 
and r2.NAME in ('BSG')

    You'll then need to change your output query to use the new TestReplicateCount column.