question

David 2 1 avatar image
David 2 1 asked

Rewritting A Query To Remove UNIONs

I have a MASSIVE table which we currently query a number of times using UNION joins such as below, however would it be possible to remove the UNIONS from the query so that the table is only queried once whilst still outputing the same results as if using the UNIONS?

For example, consider the following table and data in it, and the query result:

create table test_tbl(                    
outcome varchar(100),                    
createdate datetime)                        
                    
insert into test_tbl                    
values('UNSUCCESS','2010-06-01')                    
insert into test_tbl                    
values('UNSUCCESS','2010-05-01')                    
insert into test_tbl                    
values('UNSUCCESS','2010-05-01')                    
insert into test_tbl                    
values('UNSUCCESS','2010-04-01')                    
insert into test_tbl                    
values('UNSUCCESS','2010-03-01')                    
insert into test_tbl                    
values('UNSUCCESS','2010-02-01')                    
insert into test_tbl                    
values('UNSUCCESS','2010-06-01')                    
insert into test_tbl                    
values('UNSUCCESS','2010-04-01')                    
insert into test_tbl                    
values('UNSUCCESS','2010-05-01')                    
insert into test_tbl                    
values('SUCCESS','2010-03-01')                    
insert into test_tbl                    
values('SUCCESS','2010-02-01')                    
insert into test_tbl                    
values('SUCCESS','2010-06-01')                    
insert into test_tbl                    
values('SUCCESS','2010-03-01')                      
                    
                    
                    
-- 1 MONTH SUCCESSFULL CONTACT                    
select count(outcome) as cnt, '01 MONTHS' as time, 'SUCCESS' as outcome                    
from test_tbl                    
where createdate >= dateadd(m,-1,convert(varchar(15), getdate(),102))                    
and outcome <> 'UNSUCCESS'                    
union                    
-- 3 MONTHS SUCCESSFULL CONTACT                    
select count(outcome) as cnt, '03 MONTHS' as time, 'SUCCESS' as outcome                    
from test_tbl                    
where createdate >= dateadd(m,-3,convert(varchar(15), getdate(),102))                    
and outcome <> 'UNSUCCESS'                    
union                    
-- 6 MONTHS SUCCESSFULL CONTACT                    
select count(outcome) as cnt, '06 MONTHS' as time, 'SUCCESS' as outcome                    
from test_tbl                    
where createdate >= dateadd(m,-6,convert(varchar(15), getdate(),102))                    
and outcome <> 'UNSUCCESS'                    
union                    
-- 1 MONTH UNSUCCESSFULL CONTACT                    
select count(outcome) as cnt, '01 MONTHS' as time, 'UNSUCCESS' as outcome                    
from test_tbl                    
where createdate >= dateadd(m,-1,convert(varchar(15), getdate(),102))                    
and outcome = 'UNSUCCESS'                    
union                    
-- 3 MONTHS UNSUCCESSFULL CONTACT                    
select count(outcome) as cnt, '03 MONTHS' as time, 'UNSUCCESS' as outcome                    
from test_tbl                    
where createdate >= dateadd(m,-3,convert(varchar(15), getdate(),102))                    
and outcome = 'UNSUCCESS'                    
union                    
-- 6 MONTHS UNSUCCESSFULL CONTACT                    
select count(outcome) as cnt, '06 MONTHS' as time, 'UNSUCCESS' as outcome                    
from test_tbl                    
where createdate >= dateadd(m,-6,convert(varchar(15), getdate(),102))                    
and outcome = 'UNSUCCESS'                    
order by 3,2                      
                    
                    
cnt         time      outcome                       
----------- --------- ---------                     
1           01 MONTHS SUCCESS                    
1           03 MONTHS SUCCESS                    
4           06 MONTHS SUCCESS                    
3           01 MONTHS UNSUCCESS                    
8           03 MONTHS UNSUCCESS                    
10          06 MONTHS UNSUCCESS                    
                    
(6 row(s) affected)                    

Can this query be rewritten to produce the same result yet only be accessed once?

TIA.

t-sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered

try

select  count(outcome) as cnt,            
    [time],            
    outcome            
from            
(            
    select  case    when createdate >= dateadd(m,-1,convert(varchar(15), getdate(),102))            
            then '01 MONTHS'            
            when createdate >= dateadd(m,-3,convert(varchar(15), getdate(),102))            
            then '03 MONTHS'            
            when createdate >= dateadd(m,-6,convert(varchar(15), getdate(),102))            
            then '06 MONTHS'            
            end as [time],            
        outcome            
    from    test_tbl            
) t            
group by [time], outcome            
order by outcome, [time]            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 2 1 avatar image
David 2 1 answered

Thank you Mr Squirrel.

Your query results...

cnt         time          outcome                                                                                                          
----------- --------- --------             
1           01 MONTHS SUCCESS            
3           06 MONTHS SUCCESS            
3           01 MONTHS UNSUCCESS            
5           03 MONTHS UNSUCCESS            
2           06 MONTHS UNSUCCESS            
            
(5 row(s) affected)            

My results from UNION...

cnt         time      outcome             
----------- --------- -------             
1           01 MONTHS SUCCESS            
1           03 MONTHS SUCCESS            
4           06 MONTHS SUCCESS            
            
(3 row(s) affected)            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
            
SELECT SUM(Cnt)[Cnt],[Time],[OutCome]             
FROM(            
    SELECT * FROM(            
             SELECT            
             CASE WHEN OutCome <> 'UNSUCCESS'             
                  THEN 'SUCCESS'             
                  ELSE 'UNSUCCESS' END [OutCome],            
             CASE WHEN CreateDate >= DATEADD(m,-1,CONVERT(varchar(15), GETDATE(),102))             
                  THEN 1             
                  ELSE 0 END [01 MONTHS],            
             CASE WHEN CreateDate >= DATEADD(m,-3,CONVERT(varchar(15), GETDATE(),102))             
                  THEN 1             
                  ELSE 0 END [03 MONTHS],            
             CASE WHEN CreateDate >= DATEADD(m,-6,CONVERT(varchar(15), GETDATE(),102))             
                  THEN 1             
                  ELSE 0 END [06 MONTHS]            
             FROM TEST_TBL)p            
             UNPIVOT (Cnt FOR [Time] IN([01 MONTHS],[03 MONTHS],[06 MONTHS])            
             )unpvt            
    )d            
GROUP BY [OutCome],[Time]            
ORDER BY [OutCome],[Time]            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.