May 5, 2008 at 7:16 am
Thanks for the help. 'get_total_numbers_total' is a whole bunch of union statements that summarize different pieces of information from different tables, etc. I tried to add a unique ID to each select statement, but it doesn't work, I need a unique ID on the result set.
I also tried using the "OPENQUERY" method to open the original stored procedure and add a unique ID, this works great in the SQL Query Analyzer, but when I try to save it as a view, I get errors saying that I cannot save it as a view.
Any help is appreciated, thanks!
'get_total_numbers_total':
CREATE PROCEDURE [dbo].[get_total_numbers_Total] AS
select distinct Trust, InitialReview = ISNULL(convert(varchar(10),initialReview),0)
,ReReview = ISNULL(convert(varchar(10),reReview),0)
,TotalReview = ISNULL(convert(varchar(10),TotalReview),0)
--,Approval = ISNULL(convert(varchar(10),Approval),0)
--,QA = ISNULL(convert(varchar(10),QA),0)
,[TotalQAApproval] = ISNULL(convert(varchar(10),[TotalQA/Approval]),0)
,[TotalProcessed] = ISNULL(convert(varchar(10),TotalProcessed),0)
from vW_total_numbers
--where left(trust,8) not in ('diia - d','oc - lan','usg - cp')
union all
select Trust = 'AWI Goal(%)'
,InitialReview =
convert(varchar(20),convert(money,((select sum(initialreview) from vw_total_numbers_0 where trust = 'awi actual')/(select sum(initialreview) from vw_total_numbers_0 where trust = 'awi goal')) * 100 )) + '%'
,ReReview =
convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_0 where trust = 'awi actual')/(select Rereview from vw_total_numbers_0 where trust = 'awi goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_0 where trust = 'awi actual')/(select initialreview+rereview from vw_total_numbers_0 where trust = 'awi goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'awi actual')/(select approval from vw_total_numbers_0 where trust = 'awi goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'awi actual')/(select QA from vw_total_numbers_0 where trust = 'awi goal')) * 100)) + '%'
,[TotalQAApproval]=convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_0 where trust = 'awi actual')/(select approval+qa from vw_total_numbers_0 where trust = 'awi goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'awi actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'awi goal'))* 100)) + '%'
union all
select Trust = 'B&W Goal(%)'
,InitialReview =convert(varchar(20),convert(money,((select initialreview from vw_total_numbers_0 where trust = 'B&W actual')/(select initialreview from vw_total_numbers_0 where trust = 'B&W goal')) * 100)) + '%'
,ReReview =convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_0 where trust = 'B&W actual')/(select Rereview from vw_total_numbers_0 where trust = 'B&W goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_0 where trust = 'B&W actual')/(select initialreview+rereview from vw_total_numbers_0 where trust = 'B&W goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'B&W actual')/(select approval from vw_total_numbers_0 where trust = 'B&W goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'B&W actual')/(select QA from vw_total_numbers_0 where trust = 'B&W goal')) * 100)) + '%'
,[TotalQAApproval]=convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_0 where trust = 'B&W actual')/(select approval+qa from vw_total_numbers_0 where trust = 'B&W goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'B&W actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'B&W goal'))* 100)) + '%'
union all
select Trust = 'CLTX Goal(%)'
,InitialReview =convert(varchar(20),convert(money,((select initialreview from vw_total_numbers_0 where trust = 'CLTX actual')/(select initialreview from vw_total_numbers_0 where trust = 'CLTX goal')) * 100 )) + '%'
,ReReview =convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_0 where trust = 'CLTX actual')/(select Rereview from vw_total_numbers_0 where trust = 'CLTX goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_0 where trust = 'CLTX actual')/(select initialreview+rereview from vw_total_numbers_0 where trust = 'CLTX goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'CLTX actual')/(select approval from vw_total_numbers_0 where trust = 'CLTX goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'CLTX actual')/(select QA from vw_total_numbers_0 where trust = 'CLTX goal')) * 100)) + '%'
,[TotalQAApproval]=convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_0 where trust = 'CLTX actual')/(select approval+qa from vw_total_numbers_0 where trust = 'CLTX goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'CLTX actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'CLTX goal'))* 100)) + '%'
union all
select Trust = 'DIIA Goal(%)'
,InitialReview =convert(varchar(20),convert(money,((select initialreview from vw_total_numbers_0 where trust = 'DIIA actual')/(select initialreview from vw_total_numbers_0 where trust = 'DIIA goal')) * 100 )) + '%'
,ReReview =convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_0 where trust = 'DIIA actual')/(select Rereview from vw_total_numbers_0 where trust = 'DIIA goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_0 where trust = 'DIIA actual')/(select initialreview+rereview from vw_total_numbers_0 where trust = 'DIIA goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'DIIA actual')/(select approval from vw_total_numbers_0 where trust = 'DIIA goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'DIIA actual')/(select QA from vw_total_numbers_0 where trust = 'DIIA goal')) * 100)) + '%'
,[TotalQAApproval]=convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_0 where trust = 'DIIA actual')/(select approval+qa from vw_total_numbers_0 where trust = 'DIIA goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'DIIA actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'DIIA goal'))* 100)) + '%'
union all
select Trust = 'USG Goal(%)'
,InitialReview =convert(varchar(20),convert(money,((select initialreview from vw_total_numbers_0 where trust = 'USG actual')/(select initialreview from vw_total_numbers_0 where trust = 'USG goal')) * 100) ) + '%'
,ReReview =convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_0 where trust = 'USG actual')/(select Rereview from vw_total_numbers_0 where trust = 'USG goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_0 where trust = 'USG actual')/(select initialreview+rereview from vw_total_numbers_0 where trust = 'USG goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'USG actual')/(select approval from vw_total_numbers_0 where trust = 'USG goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'USG actual')/(select QA from vw_total_numbers_0 where trust = 'USG goal')) * 100)) + '%'
,[TotalQAApproval]=convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_0 where trust = 'USG actual')/(select approval+qa from vw_total_numbers_0 where trust = 'USG goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'USG actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'USG goal'))* 100)) + '%'
union all
select Trust = 'OC Goal(%)'
,InitialReview =convert(varchar(20),convert(money,((select initialreview from vw_total_numbers_0 where trust = 'OC actual')/(select initialreview from vw_total_numbers_0 where trust = 'OC goal')) * 100) ) + '%'
,ReReview =convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_0 where trust = 'OC actual')/(select Rereview from vw_total_numbers_0 where trust = 'OC goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_0 where trust = 'OC actual')/(select initialreview+rereview from vw_total_numbers_0 where trust = 'OC goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'OC actual')/(select approval from vw_total_numbers_0 where trust = 'OC goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'OC actual')/(select QA from vw_total_numbers_0 where trust = 'OC goal')) * 100)) + '%'
,[TotalQAApproval]=convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_0 where trust = 'OC actual')/(select approval+qa from vw_total_numbers_0 where trust = 'OC goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'OC actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'OC goal'))* 100)) + '%'
union all
select Trust = 'FB Goal(%)'
,InitialReview =convert(varchar(20),convert(money,((select initialreview from vw_total_numbers_0 where trust = 'FB actual')/(select initialreview from vw_total_numbers_0 where trust = 'FB goal')) * 100) ) + '%'
,ReReview =convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_0 where trust = 'FB actual')/(select Rereview from vw_total_numbers_0 where trust = 'FB goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_0 where trust = 'FB actual')/(select initialreview+rereview from vw_total_numbers_0 where trust = 'FB goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'FB actual')/(select approval from vw_total_numbers_0 where trust = 'FB goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'FB actual')/(select QA from vw_total_numbers_0 where trust = 'FB goal')) * 100)) + '%'
,[TotalQAApproval]=convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_0 where trust = 'FB actual')/(select approval+qa from vw_total_numbers_0 where trust = 'FB goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'FB actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_0 where trust = 'FB goal'))* 100)) + '%'
union all
select Trust = 'DIIA - Dallas Goal(%)'
,InitialReview =
convert(varchar(20),convert(money,((select sum(initialreview) from vw_total_numbers_1 where trust = 'DIIA - Dallas actual')/(select sum(initialreview) from vw_total_numbers_1 where trust = 'DIIA - Dallas goal')) * 100 )) + '%'
,ReReview = '-'
--convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_1 where trust = 'DIIA - Dallas actual')/(select Rereview from vw_total_numbers_1 where trust = 'DIIA - Dallas goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_1 where trust = 'DIIA - Dallas actual')/(select initialreview+rereview from vw_total_numbers_1 where trust = 'DIIA - Dallas goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'awi actual')/(select approval from vw_total_numbers_0 where trust = 'awi goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'awi actual')/(select QA from vw_total_numbers_0 where trust = 'awi goal')) * 100)) + '%'
,[TotalQAApproval]='-'--convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_1 where trust = 'DIIA - Dallas actual')/(select approval+qa from vw_total_numbers_1 where trust = 'DIIA - Dallas goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_1 where trust = 'DIIA - Dallas actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_1 where trust = 'DIIA - Dallas goal'))* 100)) + '%'
union all
select Trust = 'USG - CPF Goal(%)'
,InitialReview =convert(varchar(20),convert(money,((select initialreview from vw_total_numbers_1 where trust = 'USG - CPF actual')/(select initialreview from vw_total_numbers_1 where trust = 'USG - CPF goal')) * 100)) + '%'
,ReReview ='-'--convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_1 where trust = 'USG - CPF actual')/(select Rereview from vw_total_numbers_1 where trust = 'USG - CPF goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_1 where trust = 'USG - CPF actual')/(select initialreview+rereview from vw_total_numbers_1 where trust = 'USG - CPF goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'B&W actual')/(select approval from vw_total_numbers_0 where trust = 'B&W goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'B&W actual')/(select QA from vw_total_numbers_0 where trust = 'B&W goal')) * 100)) + '%'
,[TotalQAApproval]='-'--convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_1 where trust = 'USG - CPF actual')/(select approval+qa from vw_total_numbers_1 where trust = 'USG - CPF goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_1 where trust = 'USG - CPF actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_1 where trust = 'USG - CPF goal'))* 100)) + '%'
union all
select Trust = 'OC - Langhorne Goal(%)'
,InitialReview =convert(varchar(20),convert(money,((select initialreview from vw_total_numbers_1 where trust = 'OC - Langhorne actual')/(select initialreview from vw_total_numbers_1 where trust = 'OC - Langhorne goal')) * 100 )) + '%'
,ReReview ='-'--convert(varchar(20),convert(money,((select Rereview from vw_total_numbers_1 where trust = 'OC - Langhorne actual')/(select Rereview from vw_total_numbers_1 where trust = 'OC - Langhorne goal')) * 100)) + '%'
,TotalReview =convert(varchar(20),convert(money,((select Initialreview+rereview from vw_total_numbers_1 where trust = 'OC - Langhorne actual')/(select initialreview+rereview from vw_total_numbers_1 where trust = 'OC - Langhorne goal')) * 100)) + '%'
--,Approval =convert(varchar(20),convert(money,((select approval from vw_total_numbers_0 where trust = 'CLTX actual')/(select approval from vw_total_numbers_0 where trust = 'CLTX goal')) * 100)) + '%'
--,QA =convert(varchar(20),convert(money,((select QA from vw_total_numbers_0 where trust = 'CLTX actual')/(select QA from vw_total_numbers_0 where trust = 'CLTX goal')) * 100)) + '%'
,[TotalQAApproval]='-'--convert(varchar(20),convert(money,((select approval+qa from vw_total_numbers_1 where trust = 'OC - Langhorne actual')/(select approval+qa from vw_total_numbers_1 where trust = 'OC - Langhorne goal'))* 100)) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select initialreview+rereview + approval+qa from vw_total_numbers_1 where trust = 'OC - Langhorne actual')/(select initialreview+rereview + approval+qa from vw_total_numbers_1 where trust = 'OC - Langhorne goal'))* 100)) + '%'
union all
select '(Total Actual)' as trust,
InitialReview = convert(varchar(20),sum(initialreview)), ReReview = convert(varchar(20),sum(rereview)), TotalReview = convert(varchar(20),sum(totalreview))
--,approval = convert(varchar(20),sum(approval)), qa = convert(varchar(20), sum(qa))
,[totalqa/approval] = convert(varchar(20),sum([totalqa/approval]))
,totalproceseed = convert(varchar(20), sum(totalprocessed))
from vw_total_numbers_0 where right(trust,4) <> 'goal'
--and left(trust,8) not in ('diia - d','oc - lan','usg - cp')
union all
select '(Total Goal)' as trust,
InitialReview = convert(varchar(20),sum(initialreview)), ReReview = convert(varchar(20),sum(rereview)), TotalReview = convert(varchar(20),sum(totalreview))
--,approval = convert(varchar(20), sum(approval)), qa = convert(varchar(20), sum(qa))
,[totalqa/approval] = convert(varchar(20),sum([totalqa/approval]))
,totalproceseed = convert(varchar(20), sum(totalprocessed))
from vw_total_numbers_0 where right(trust,4) = 'goal'
--and left(trust,8) not in ('diia - d','oc - lan','usg - cp')
union all
select Trust = '(Total Goal)(%)'
,InitialReview =convert(varchar(20),convert(money,((select sum(initialreview) from vw_total_numbers_0 where right(trust,4) <> 'goal')/(select sum(initialreview) from vw_total_numbers_0 where right(trust,4) = 'goal')) * 100) ) + '%'
,ReReview =convert(varchar(20),convert(money,((select sum(Rereview) from vw_total_numbers_0 where right(trust,4) <> 'goal')/(select sum(Rereview) from vw_total_numbers_0 where right(trust,4) = 'goal')) * 100 )) + '%'
,TotalReview =convert(varchar(20),convert(money,((select sum(Initialreview+rereview) from vw_total_numbers_0 where right(trust,4) <> 'goal')/(select sum(initialreview+rereview) from vw_total_numbers_0 where right(trust,4) = 'goal')) * 100 )) + '%'
--,Approval =convert(varchar(20),convert(money,((select sum(approval) from vw_total_numbers_0 where right(trust,4) <> 'goal')/(select sum(approval) from vw_total_numbers_0 where right(trust,4) = 'goal')) * 100) ) + '%'
--,QA =convert(varchar(20),convert(money,((select sum(QA) from vw_total_numbers_0 where right(trust,4) <> 'goal')/(select sum(QA) from vw_total_numbers_0 where right(trust,4) = 'goal')) * 100 )) + '%'
,[TotalQAApproval]=convert(varchar(20),convert(money,((select sum(approval+qa) from vw_total_numbers_0 where right(trust,4) <> 'goal')/(select sum(approval+qa) from vw_total_numbers_0 where right(trust,4) = 'goal')) * 100 )) + '%'
,[TotalProcessed]=convert(varchar(20),convert(money,((select sum(initialreview+rereview + approval+qa) from vw_total_numbers_0 where right(trust,4) <> 'goal')/(select sum(initialreview+rereview + approval+qa) from vw_total_numbers_0 where right(trust,4) = 'goal')) * 100)) + '%'
union all
select Trust, InitialReview = convert(varchar(10),initialReview)
,ReReview = convert(varchar(10),reReview)
,TotalReview = convert(varchar(10),TotalReview)
--,Approval = convert(varchar(10),Approval)
--,QA = convert(varchar(10),QA)
,[TotalQAApproval] = convert(varchar(10),[TotalQA/Approval])
,[TotalProcessed] = convert(varchar(10),TotalProcessed)
from ProductivityNumbers_daily_total order by trust
GO
May 5, 2008 at 7:42 am
juts a first glimps:
- alter
select distinct Trust, InitialReview = ISNULL(convert(varchar(10),initialReview),0)
,ReReview = ISNULL(convert(varchar(10),reReview),0)
,TotalReview = ISNULL(convert(varchar(10),TotalReview),0)
--,Approval = ISNULL(convert(varchar(10),Approval),0)
--,QA = ISNULL(convert(varchar(10),QA),0)
,[TotalQAApproval] = ISNULL(convert(varchar(10),[TotalQA/Approval]),0)
,[TotalProcessed] = ISNULL(convert(varchar(10),TotalProcessed),0)
from vW_total_numbers
--where left(trust,8) not in ('diia - d','oc - lan','usg - cp')
to
select distinct Trust, InitialReview = convert(varchar(10),ISNULL(initialReview,0))
,ReReview = convert(varchar(10),ISNULL(reReview,0) )
,TotalReview = convert(varchar(10),ISNULL(TotalReview,0) )
--,Approval = ISNULL(convert(varchar(10),Approval),0)
--,QA = ISNULL(convert(varchar(10),QA),0)
,[TotalQAApproval] = convert(varchar(10),ISNULL([TotalQA/Approval],0) )
,[TotalProcessed] = convert(varchar(10),ISNULL(TotalProcessed,0))
from vW_total_numbers
--where left(trust,8) not in ('diia - d','oc - lan','usg - cp')
Check the resulting datatype. IMO you want varchar(10) in stead of int.
- maybe this can be rewritten into a couple of case statements in stead of the whole bunch of "union all"s. Still just a first glimps..
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 7, 2008 at 6:35 am
IMO this union-part is far better readable/interpretable and less fault sensitive than the total of union parts provided in your script.
What you need to figure out is if the sum-operation is actualy needed in these union parts, and if it is , why isn't it applied to alll selected columns.
declare @vw_total_numbers_0 table
(
trust varchar(128)
, initialreview decimal(9, 2)
, Rereview decimal(9, 2)
, approval decimal(9, 2)
, qa decimal(9, 2)
)
set nocount on
insert into @vw_total_numbers_0
values ('awi actual', 10, 20, 30, 40)
insert into @vw_total_numbers_0
values ( 'awi goal', 15, 25, 35, 45 )
set nocount off
SELECT Goal.Trust + '(%)'
-- SUM removed !! because all other nested selects didn't use sum to give the result.
, InitialReview = CONVERT(VARCHAR(20), CONVERT(MONEY, ( ( (Actual.initialreview) ) / ( (Goal.initialreview) ) ) * 100)) + '%'
, ReReview = CONVERT(VARCHAR(20), CONVERT(MONEY, ( ( Actual.Rereview ) / ( Goal.Rereview ) ) * 100)) + '%'
, TotalReview = CONVERT(VARCHAR(20), CONVERT(MONEY, ( ( Actual.Initialreview + Actual.rereview ) / ( Goal.initialreview + Goal.rereview ) ) * 100)) + '%'
, [TotalQAApproval] = CONVERT(VARCHAR(20), CONVERT(MONEY, ( ( Actual.approval + Actual.qa ) / ( Goal.approval + Goal.qa ) ) * 100)) + '%'
, [TotalProcessed] = CONVERT(VARCHAR(20), CONVERT(MONEY, ( ( Actual.initialreview + Actual.rereview + Actual.approval + Actual.qa ) / ( Goal.initialreview + Goal.rereview + Goal.approval + Goal.qa ) ) * 100)) + '%'
from @vw_total_numbers_0 Actual
inner join @vw_total_numbers_0 Goal
on substring(Actual.trust , 1, charindex(' ',Actual.trust,1) )
= substring(Goal.trust , 1, charindex(' ',Goal.trust,1) )
where Actual.trust like '% actual'
and Goal.trust like '% goal'
/* test to filter just one case */
and Goal.trust = 'AWI Goal'
As you can see with this example, maybe your # union alls may be replaces by a single group by on the Goal.trust column.
Once again, I don't know the symantics or composition of your vw_total_numbers_0, so maybe I'm way off. :ermm:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply