ORALCE TO SQL SERVER MIGRATION Query performance Tuning

Question

ORALCE TO SQL SERVER MIGRATION Query performance Tuning

sreesree.96

SSC-Addicted

Points: 468
More actions
April 20, 2017 at 1:03 pm

#401973

Hi Experts,
We are migrated Oracle to SQL Server 13.0
using the Cognos Reporting
When we run the the below Report query using oracle Database. report will retrieve the results in about 2 mini
after migrating to SQL server , Report is taking about 8 mini to run the same query.
Please see the Oracle and SQL Server Report query's. and suggest me what might have causing the performance.
Appreciate your help.!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Are you able to post a execution plan as a .SQLPLAN file? You should be able to view and save the execution plan from within Management Studio. Just working from the attached query will be difficult to do without knowing the table structures involved, indexes on the tables, and some sample data.
http://www.sqlservercentral.com/articles/Best+Practices/61537/

sreesree.96 SSC-Addicted Points: 468 More actions · Answer 2

Thank you For your Reply.!
Now I attached The Excplan. under main post.

Thank you for your Time.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Table definitions and index definitions?

And an actual execution plan would be much more useful. Run the query with the 'include actual execution plan' option turned on (button on SSMS's toolbar), and include that plan.

And for anyone interested, here's the query formated to be readable

SELECT Re_org.Reorg_ID AS Reorg_ID, Revenue_WorkSheet_Final.Subtotal_Type_Number AS Subtotal_Type_Number, Revenue_WorkSheet_Final.Subtotal_Type_Desc AS Subtotal_Type_Desc, Revenue_WorkSheet_Final.c4 AS c4, Revenue_WorkSheet_Final.c5 AS c5, Revenue_WorkSheet_Final.Account_ID_Level AS Account_ID_Level, Revenue_WorkSheet_Final.Project_ID AS Project_ID, Revenue_WorkSheet_Final.TopGroup AS TopGroup, Revenue_WorkSheet_Final.RevExp AS RevExp, Revenue_WorkSheet_Final.Company_ID AS Company_ID, Revenue_WorkSheet_Final.Account_ID AS Account_ID, Revenue_WorkSheet_Final.Full_Name AS Full_Name, Revenue_WorkSheet_Final.Category_Acct AS Category_Acct, Revenue_WorkSheet_Final.Category_Acct_Desc AS Category_Acct_Desc, Revenue_WorkSheet_Final.Pool_Number AS Pool_Number, Revenue_WorkSheet_Final.Period AS Period, Revenue_WorkSheet_Final.Fiscal_Year AS Fiscal_Year, Revenue_WorkSheet_Final.ITD_Revenue_Rate AS ITD_Revenue_Rate, Revenue_WorkSheet_Final.YTD_Revenue_Rate AS YTD_Revenue_Rate, Revenue_WorkSheet_Final.PTD_Revenue_Rate AS PTD_Revenue_Rate, SUM(Revenue_WorkSheet_Final.ITD_Total_Hours_Cost) AS ITD_Total_Hours_Cost, SUM(Revenue_WorkSheet_Final.YTD_Total_Hours_Cost) AS YTD_Total_Hours_Cost, SUM(Revenue_WorkSheet_Final.PTD_Total_Hours_Cost) AS PTD_Total_Hours_Cost, SUM(Revenue_WorkSheet_Final.ITD_Allowable_Hours_Cost) AS ITD_Allowable_Hours_Cost, SUM(Revenue_WorkSheet_Final.YTD_Allowable_Hours_Cost) AS YTD_Allowable_Hours_Cost, SUM(Revenue_WorkSheet_Final.PTD_Allowable_Hours_Cost) AS PTD_Allowable_Hours_Cost, SUM(Revenue_WorkSheet_Final.c27) AS c27, SUM(Revenue_WorkSheet_Final.c28) AS c28, SUM(Revenue_WorkSheet_Final.c29) AS c29, SUM(Revenue_WorkSheet_Final.ITD_Fee) AS ITD_Fee, SUM(Revenue_WorkSheet_Final.YTD_Fee) AS YTD_Fee, SUM(Revenue_WorkSheet_Final.PTD_Fee) AS PTD_Fee, SUM(Revenue_WorkSheet_Final.ITD_Fee_On_Hours) AS ITD_Fee_On_Hours, SUM(Revenue_WorkSheet_Final.YTD_Fee_On_Hours) AS YTD_Fee_On_Hours, SUM(Revenue_WorkSheet_Final.PTD_Fee_On_Hours) AS PTD_Fee_On_Hours, SUM(Revenue_WorkSheet_Final.ITD_Revenue) AS ITD_Revenue, SUM(Revenue_WorkSheet_Final.YTD_Revenue) AS YTD_Revenue, SUM(Revenue_WorkSheet_Final.PTD_Revenue) AS PTD_Revenue, SUM(Revenue_WorkSheet_Final.ITD_Revenue_Adjustment_Amount) AS ITD_Revenue_Adjustment_Amount, SUM(Revenue_WorkSheet_Final.YTD_Revenue_Adjustment_Amount) AS YTD_Revenue_Adjustment_Amount, SUM(Revenue_WorkSheet_Final.PTD_Revenue_Adjustment_Amount) AS PTD_Revenue_Adjustment_Amount, SUM(Revenue_WorkSheet_Final.ITD_Revenue_Units_Amount) AS ITD_Revenue_Units_Amount, SUM(Revenue_WorkSheet_Final.YTD_Revenue_Units_Amount) AS YTD_Revenue_Units_Amount, SUM(Revenue_WorkSheet_Final.PTD_Revenue_Units_Amount) AS PTD_Revenue_Units_Amount, SUM(Revenue_WorkSheet_Final.ITD_Subtotal_Revenue) AS ITD_Subtotal_Revenue, SUM(Revenue_WorkSheet_Final.YTD_Subtotal_Revenue) AS YTD_Subtotal_Revenue, SUM(Revenue_WorkSheet_Final.PTD_Subtotal_Revenue) AS PTD_Subtotal_Revenue, SUM(Revenue_WorkSheet_Final.ITD_Other_Fee) AS ITD_Other_Fee, SUM(Revenue_WorkSheet_Final.YTD_Other_Fee) AS YTD_Other_Fee, SUM(Revenue_WorkSheet_Final.PTD_Other_Fee) AS PTD_Other_Fee, SUM(Revenue_WorkSheet_Final.ITD_Reallocation_Amount) AS ITD_Reallocation_Amount, SUM(Revenue_WorkSheet_Final.YTD_Reallocation_Amount) AS YTD_Reallocation_Amount, SUM(Revenue_WorkSheet_Final.PTD_Reallocation_Amount) AS PTD_Reallocation_Amount, SUM(Revenue_WorkSheet_Final.PTD_Retroactive_Revenue) AS PTD_Retroactive_Revenue, SUM(Revenue_WorkSheet_Final.c55) AS c55, SUM(Revenue_WorkSheet_Final.c56) AS c56, SUM(Revenue_WorkSheet_Final.c57) AS c57, SUM(Revenue_WorkSheet_Final.ITD_Less__Cost_in_Excess) AS ITD_Less__Cost_in_Excess, SUM(Revenue_WorkSheet_Final.YTD_Less__Cost_in_Excess) AS YTD_Less__Cost_in_Excess, SUM(Revenue_WorkSheet_Final.PTD_Less__Cost_in_Excess) AS PTD_Less__Cost_in_Excess, SUM(Revenue_WorkSheet_Final.ITD_Less__Total_in_Excess) AS ITD_Less__Total_in_Excess, SUM(Revenue_WorkSheet_Final.YTD_Less__Total_in_Excess) AS YTD_Less__Total_in_Excess, SUM(Revenue_WorkSheet_Final.PTD_Less__Total_in_Excess) AS PTD_Less__Total_in_Excess, SUM(Revenue_WorkSheet_Final.ITD_Less__Discount_Amount) AS ITD_Less__Discount_Amount, SUM(Revenue_WorkSheet_Final.YTD_Less__Discount_Amount) AS YTD_Less__Discount_Amount, SUM(Revenue_WorkSheet_Final.PTD_Less__Discount_Amount) AS PTD_Less__Discount_Amount, SUM(Revenue_WorkSheet_Final.ITD_Total_Revenue) AS ITD_Total_Revenue, SUM(Revenue_WorkSheet_Final.YTD_Total_Revenue) AS YTD_Total_Revenue, SUM(Revenue_WorkSheet_Final.PTD_Total_Revenue) AS PTD_Total_Revenue, Revenue_WorkSheet_Final.Account_Name_Level AS Account_Name_Level, SUM(Revenue_WorkSheet_Final.Total_ITD_Total_Hours_Cost_) AS Total_ITD_Total_Hours_Cost_, SUM(Revenue_WorkSheet_Final.c72) AS c72, SUM(Revenue_WorkSheet_Final.c73) AS c73, SUM(Revenue_WorkSheet_Final.Total_ITD_Fee_) AS Total_ITD_Fee_, SUM(Revenue_WorkSheet_Final.Total_ITD_Fee_On_Hours_) AS Total_ITD_Fee_On_Hours_, SUM(Revenue_WorkSheet_Final.Total_ITD_Revenue_) AS Total_ITD_Revenue_, SUM(Revenue_WorkSheet_Final.Total_YTD_Total_Hours_Cost_) AS Total_YTD_Total_Hours_Cost_, SUM(Revenue_WorkSheet_Final.c78) AS c78, SUM(Revenue_WorkSheet_Final.c79) AS c79, SUM(Revenue_WorkSheet_Final.Total_YTD_Fee_) AS Total_YTD_Fee_, SUM(Revenue_WorkSheet_Final.Total_YTD_Fee_On_Hours_) AS Total_YTD_Fee_On_Hours_, SUM(Revenue_WorkSheet_Final.Total_YTD_Revenue_) AS Total_YTD_Revenue_, SUM(Revenue_WorkSheet_Final.Total_PTD_Total_Hours_Cost_) AS Total_PTD_Total_Hours_Cost_, SUM(Revenue_WorkSheet_Final.c84) AS c84, SUM(Revenue_WorkSheet_Final.c85) AS c85, SUM(Revenue_WorkSheet_Final.Total_PTD_Fee_) AS Total_PTD_Fee_, SUM(Revenue_WorkSheet_Final.Total_PTD_Fee_On_Hours_) AS Total_PTD_Fee_On_Hours_, SUM(Revenue_WorkSheet_Final.Total_PTD_Revenue_) AS Total_PTD_Revenue_ FROM (SELECT DISTINCT REORG_STRUC.REORG_ID AS Reorg_ID, REORG_STRUC.REORG_NAME AS Reorg_Name, REORG_ORG_LINK.ORG_ID AS Own_Org_ID FROM DELTEK.REORG_STRUC REORG_STRUC INNER JOIN DELTEK.REORG_ORG_LINK REORG_ORG_LINK ON REORG_ORG_LINK.REORG_ID = REORG_STRUC.REORG_ID AND REORG_ORG_LINK.COMPANY_ID = REORG_STRUC.COMPANY_ID ) Re_org INNER JOIN (SELECT T0.C0 AS Organization_ID, T0.C1 AS Subtotal_Type_Number, T0.C2 AS Subtotal_Type_Desc, T0.C3 AS c4, T0.C4 AS c5, T0.C5 AS Account_ID_Level, T0.C6 AS Project_ID, T0.C7 AS TopGroup, T0.C8 AS RevExp, T0.C9 AS Company_ID, T0.C10 AS Full_Name, T0.C11 AS Account_ID, T0.C12 AS Category_Acct, T0.C13 AS Category_Acct_Desc, T0.C14 AS Pool_Number, T0.C15 AS Period, T0.C16 AS Fiscal_Year, T0.C17 AS ITD_Revenue_Rate, T0.C18 AS YTD_Revenue_Rate, T0.C19 AS PTD_Revenue_Rate, T0.C20 AS ITD_Total_Hours_Cost, T0.C21 AS YTD_Total_Hours_Cost, T0.C22 AS PTD_Total_Hours_Cost, T0.C23 AS ITD_Allowable_Hours_Cost, T0.C24 AS YTD_Allowable_Hours_Cost, T0.C25 AS PTD_Allowable_Hours_Cost, T0.C26 AS c27, T0.C27 AS c28, T0.C28 AS c29, T0.C29 AS ITD_Fee, T0.C30 AS YTD_Fee, T0.C31 AS PTD_Fee, T0.C32 AS ITD_Fee_On_Hours, T0.C33 AS YTD_Fee_On_Hours, T0.C34 AS PTD_Fee_On_Hours, T0.C35 AS ITD_Revenue, T0.C36 AS YTD_Revenue, T0.C37 AS PTD_Revenue, T0.C38 AS ITD_Revenue_Adjustment_Amount, T0.C39 AS YTD_Revenue_Adjustment_Amount, T0.C40 AS PTD_Revenue_Adjustment_Amount, T0.C41 AS ITD_Revenue_Units_Amount, T0.C42 AS YTD_Revenue_Units_Amount, T0.C43 AS PTD_Revenue_Units_Amount, T0.C44 AS ITD_Subtotal_Revenue, T0.C45 AS YTD_Subtotal_Revenue, T0.C46 AS PTD_Subtotal_Revenue, T0.C47 AS ITD_Other_Fee, T0.C48 AS YTD_Other_Fee, T0.C49 AS PTD_Other_Fee, T0.C50 AS ITD_Reallocation_Amount, T0.C51 AS YTD_Reallocation_Amount, T0.C52 AS PTD_Reallocation_Amount, T0.C53 AS PTD_Retroactive_Revenue, T0.C54 AS c55, T0.C55 AS c56, T0.C56 AS c57, T0.C57 AS ITD_Less__Cost_in_Excess, T0.C58 AS YTD_Less__Cost_in_Excess, T0.C59 AS PTD_Less__Cost_in_Excess, T0.C60 AS ITD_Less__Total_in_Excess, T0.C61 AS YTD_Less__Total_in_Excess, T0.C62 AS PTD_Less__Total_in_Excess, T0.C63 AS ITD_Less__Discount_Amount, T0.C64 AS YTD_Less__Discount_Amount, T0.C65 AS PTD_Less__Discount_Amount, T0.C66 AS ITD_Total_Revenue, T0.C67 AS YTD_Total_Revenue, T0.C68 AS PTD_Total_Revenue, T0.C69 AS Account_Name_Level, T0.C20 AS Total_ITD_Total_Hours_Cost_, T0.C26 AS c72, T0.C23 AS c73, T0.C29 AS Total_ITD_Fee_, T0.C32 AS Total_ITD_Fee_On_Hours_, T0.C35 AS Total_ITD_Revenue_, T0.C21 AS Total_YTD_Total_Hours_Cost_, T0.C27 AS c78, T0.C24 AS c79, T0.C30 AS Total_YTD_Fee_, T0.C33 AS Total_YTD_Fee_On_Hours_, T0.C36 AS Total_YTD_Revenue_, T0.C22 AS Total_PTD_Total_Hours_Cost_, T0.C28 AS c84, T0.C25 AS c85, T0.C31 AS Total_PTD_Fee_, T0.C34 AS Total_PTD_Fee_On_Hours_, T0.C37 AS Total_PTD_Revenue_ FROM (SELECT Revenue_WorkSheet_Query.Organization_ID AS C0, Revenue_WorkSheet_Query.Subtotal_Type_Number AS C1, Revenue_WorkSheet_Query.Subtotal_Type_Desc AS C2, CASE Revenue_WorkSheet_Query.Subtotal_Type_Number WHEN 1 THEN 'Contractor Salary Threshold ' WHEN 14 THEN 'Award Fee' WHEN 3 THEN Pool_Query.Pool_Name WHEN 4 THEN Pool_Query.Pool_Name WHEN 5 THEN Pool_Query.Pool_Name ELSE Revenue_WorkSheet_Query.Account_Name_Level END AS C3, Revenue_WorkSheet_Query.c4 AS C4, Revenue_WorkSheet_Query.Account_ID_Level AS C5, Revenue_WorkSheet_Query.Project_ID AS C6, Revenue_WorkSheet_Query.TopGroup AS C7, Revenue_WorkSheet_Query.RevExp AS C8, Revenue_WorkSheet_Query.Company_ID AS C9, Revenue_WorkSheet_Query.Full_Name AS C10, Revenue_WorkSheet_Query.Account_ID AS C11, Revenue_WorkSheet_Query.Category_Acct AS C12, CASE WHEN NOT Revenue_WorkSheet_Query.PLC_Cd IS NULL THEN Revenue_WorkSheet_Query.PLC_Desc ELSE CASE WHEN Revenue_WorkSheet_Query.Pool_Number = 0 THEN Revenue_WorkSheet_Query.Account_Name_Level ELSE Pool_Query.Pool_Name END END AS C13, Revenue_WorkSheet_Query.Pool_Number AS C14, Revenue_WorkSheet_Query.Period AS C15, Revenue_WorkSheet_Query.Fiscal_Year AS C16, Revenue_WorkSheet_Query.ITD_Revenue_Rate AS C17, Revenue_WorkSheet_Query.YTD_Revenue_Rate AS C18, Revenue_WorkSheet_Query.PTD_Revenue_Rate AS C19, SUM(Revenue_WorkSheet_Query.ITD_Total_Hours_Cost) AS C20, SUM(Revenue_WorkSheet_Query.YTD_Total_Hours_Cost) AS C21, SUM(Revenue_WorkSheet_Query.PTD_Total_Hours_Cost) AS C22, SUM(Revenue_WorkSheet_Query.ITD_Allowable_Hours_Cost) AS C23, SUM(Revenue_WorkSheet_Query.YTD_Allowable_Hours_Cost) AS C24, SUM(Revenue_WorkSheet_Query.PTD_Allowable_Hours_Cost) AS C25, SUM(Revenue_WorkSheet_Query.c28) AS C26, SUM(Revenue_WorkSheet_Query.c29) AS C27, SUM(Revenue_WorkSheet_Query.c30) AS C28, SUM(Revenue_WorkSheet_Query.ITD_Fee) AS C29, SUM(Revenue_WorkSheet_Query.YTD_Fee) AS C30, SUM(Revenue_WorkSheet_Query.PTD_Fee) AS C31, SUM(Revenue_WorkSheet_Query.ITD_Fee_On_Hours) AS C32, SUM(Revenue_WorkSheet_Query.YTD_Fee_On_Hours) AS C33, SUM(Revenue_WorkSheet_Query.PTD_Fee_On_Hours) AS C34, SUM(Revenue_WorkSheet_Query.ITD_Revenue) AS C35, SUM(Revenue_WorkSheet_Query.YTD_Revenue) AS C36, SUM(Revenue_WorkSheet_Query.PTD_Revenue) AS C37, SUM(Revenue_WorkSheet_Query.ITD_Revenue_Adjustment_Amount) AS C38, SUM(Revenue_WorkSheet_Query.YTD_Revenue_Adjustment_Amount) AS C39, SUM(Revenue_WorkSheet_Query.PTD_Revenue_Adjustment_Amount) AS C40, SUM(Revenue_WorkSheet_Query.ITD_Revenue_Units_Amount) AS C41, SUM(Revenue_WorkSheet_Query.YTD_Revenue_Units_Amount) AS C42, SUM(Revenue_WorkSheet_Query.PTD_Revenue_Units_Amount) AS C43, ((SUM(Revenue_WorkSheet_Query.ITD_Revenue) + SUM(Revenue_WorkSheet_Query.ITD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.ITD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.ITD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.ITD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c53)) AS C44, ((SUM(Revenue_WorkSheet_Query.YTD_Revenue) + SUM(Revenue_WorkSheet_Query.YTD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.YTD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.YTD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.YTD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c54)) AS C45, (((SUM(Revenue_WorkSheet_Query.PTD_Revenue) + SUM(Revenue_WorkSheet_Query.PTD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.PTD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.PTD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.PTD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c55))) + SUM(Revenue_WorkSheet_Query.PTD_Retroactive_Revenue) AS C46, SUM(Revenue_WorkSheet_Query.ITD_Other_Fee) AS C47, SUM(Revenue_WorkSheet_Query.YTD_Other_Fee) AS C48, SUM(Revenue_WorkSheet_Query.PTD_Other_Fee) AS C49, SUM(Revenue_WorkSheet_Query.ITD_Reallocation_Amount) AS C50, SUM(Revenue_WorkSheet_Query.YTD_Reallocation_Amount) AS C51, SUM(Revenue_WorkSheet_Query.PTD_Reallocation_Amount) AS C52, SUM(Revenue_WorkSheet_Query.PTD_Retroactive_Revenue) AS C53, SUM(Revenue_WorkSheet_Query.c53) AS C54, SUM(Revenue_WorkSheet_Query.c54) AS C55, SUM(Revenue_WorkSheet_Query.c55) AS C56, SUM(Revenue_WorkSheet_Query.ITD_Less__Cost_in_Excess) AS C57, SUM(Revenue_WorkSheet_Query.YTD_Less__Cost_in_Excess) AS C58, SUM(Revenue_WorkSheet_Query.PTD_Less__Cost_in_Excess) AS C59, SUM(Revenue_WorkSheet_Query.ITD_Less__Total_in_Excess) AS C60, SUM(Revenue_WorkSheet_Query.YTD_Less__Total_in_Excess) AS C61, SUM(Revenue_WorkSheet_Query.PTD_Less__Total_in_Excess) AS C62, SUM(Revenue_WorkSheet_Query.ITD_Less__Discount_Amount) AS C63, SUM(Revenue_WorkSheet_Query.YTD_Less__Discount_Amount) AS C64, SUM(Revenue_WorkSheet_Query.PTD_Less__Discount_Amount) AS C65, (((SUM(Revenue_WorkSheet_Query.ITD_Revenue) + SUM(Revenue_WorkSheet_Query.ITD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.ITD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.ITD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.ITD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c53))) - ((SUM(Revenue_WorkSheet_Query.ITD_Less__Cost_in_Excess) + SUM(Revenue_WorkSheet_Query.ITD_Less__Total_in_Excess)) + SUM(Revenue_WorkSheet_Query.ITD_Less__Discount_Amount)) AS C66, (((SUM(Revenue_WorkSheet_Query.YTD_Revenue) + SUM(Revenue_WorkSheet_Query.YTD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.YTD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.YTD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.YTD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c54))) - ((SUM(Revenue_WorkSheet_Query.YTD_Less__Cost_in_Excess) + SUM(Revenue_WorkSheet_Query.YTD_Less__Total_in_Excess)) + SUM(Revenue_WorkSheet_Query.YTD_Less__Discount_Amount)) AS C67, ((((SUM(Revenue_WorkSheet_Query.PTD_Revenue) + SUM(Revenue_WorkSheet_Query.PTD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.PTD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.PTD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.PTD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c55))) + SUM(Revenue_WorkSheet_Query.PTD_Retroactive_Revenue)) - ((SUM(Revenue_WorkSheet_Query.PTD_Less__Cost_in_Excess) + SUM(Revenue_WorkSheet_Query.PTD_Less__Total_in_Excess)) + SUM(Revenue_WorkSheet_Query.PTD_Less__Discount_Amount)) AS C68, Revenue_WorkSheet_Query.Account_Name_Level AS C69 FROM (SELECT T0.C0 AS Organization_ID, T0.C1 AS Subtotal_Type_Number, T0.C2 AS Subtotal_Type_Desc, T0.C3 AS c4, T0.C4 AS Account_ID_Level, T0.C5 AS Project_ID_Level, T0.C6 AS Project_ID, T0.C5 AS TopGroup, T0.C7 AS RevExp, T0.C8 AS Company_ID, T0.C9 AS PLC_Cd, T0.C10 AS PLC_Desc, T0.C11 AS Full_Name, T0.C12 AS Account_ID, T0.C13 AS Category_Acct, T0.C14 AS Pool_Number, T0.C15 AS Period, T0.C16 AS Fiscal_Year, T0.C17 AS ITD_Revenue_Rate, T0.C17 AS YTD_Revenue_Rate, T0.C17 AS PTD_Revenue_Rate, T0.C18 AS ITD_Total_Hours_Cost, T0.C19 AS YTD_Total_Hours_Cost, T0.C20 AS PTD_Total_Hours_Cost, T0.C21 AS ITD_Allowable_Hours_Cost, T0.C22 AS YTD_Allowable_Hours_Cost, T0.C23 AS PTD_Allowable_Hours_Cost, T0.C24 AS c28, T0.C25 AS c29, T0.C26 AS c30, T0.C27 AS ITD_Fee, T0.C28 AS YTD_Fee, T0.C29 AS PTD_Fee, T0.C30 AS ITD_Fee_On_Hours, T0.C31 AS YTD_Fee_On_Hours, T0.C32 AS PTD_Fee_On_Hours, T0.C33 AS ITD_Revenue, T0.C34 AS YTD_Revenue, T0.C35 AS PTD_Revenue, T0.C36 AS ITD_Revenue_Adjustment_Amount, T0.C37 AS YTD_Revenue_Adjustment_Amount, T0.C38 AS PTD_Revenue_Adjustment_Amount, T0.C39 AS ITD_Revenue_Units_Amount, T0.C40 AS YTD_Revenue_Units_Amount, T0.C41 AS PTD_Revenue_Units_Amount, T0.C42 AS ITD_Other_Fee, T0.C43 AS YTD_Other_Fee, T0.C44 AS PTD_Other_Fee, T0.C45 AS ITD_Reallocation_Amount, T0.C46 AS YTD_Reallocation_Amount, T0.C47 AS PTD_Reallocation_Amount, T0.C48 AS PTD_Retroactive_Revenue, T0.C49 AS c53, T0.C50 AS c54, T0.C51 AS c55, T0.C52 AS ITD_Less__Cost_in_Excess, T0.C53 AS YTD_Less__Cost_in_Excess, T0.C52 AS PTD_Less__Cost_in_Excess, T0.C54 AS ITD_Less__Total_in_Excess, T0.C55 AS YTD_Less__Total_in_Excess, T0.C56 AS PTD_Less__Total_in_Excess, T0.C57 AS ITD_Less__Discount_Amount, T0.C58 AS YTD_Less__Discount_Amount, T0.C59 AS PTD_Less__Discount_Amount, T0.C60 AS Account_Name_Level FROM (SELECT PROJ.ORG_ID AS C0, RPT_REVENUE_WKS.SUB_TOT_TYPE_NO AS C1, CASE WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 1 THEN 'Total Direct Labor' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 2 THEN 'Total Direct Costs' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 3 THEN 'Total Indirect Costs' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 4 THEN 'Total COM' ELSE NULL END AS C2, '' AS C3, ACCT.ACCT_ID AS C4, PROJ.PROJ_ID AS C5, RPT_REVENUE_WKS.PROJ_ID AS C6, CASE RPT_REVENUE_WKS.SUB_TOT_TYPE_NO WHEN 1 THEN 'Revenue' WHEN 14 THEN 'Revenue' ELSE 'Expense' END AS C7, RPT_REVENUE_WKS.COMPANY_ID AS C8, RPT_REVENUE_WKS.BILL_LAB_CAT_CD AS C9, RPT_REVENUE_WKS.BILL_LAB_CAT_DESC AS C10, RPT_REVENUE_WKS.LAST_FIRST_NAME AS C11, RPT_REVENUE_WKS.ACCT_ID AS C12, CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.BILL_LAB_CAT_CD ELSE CASE WHEN RPT_REVENUE_WKS.POOL_NO = 0 THEN RPT_REVENUE_WKS.ACCT_ID ELSE CAST(RPT_REVENUE_WKS.POOL_NO AS VARCHAR(7)) END END AS C13, RPT_REVENUE_WKS.POOL_NO AS C14, RPT_REVENUE_WKS.PD_NO AS C15, RPT_REVENUE_WKS.FY_CD AS C16, CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END AS C17, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_HRS ELSE RPT_REVENUE_WKS.ITD_TOTAL_ACT_AMT END) AS C18, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_HRS ELSE RPT_REVENUE_WKS.YTD_TOTAL_ACT_AMT END) AS C19, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_HRS ELSE RPT_REVENUE_WKS.PTD_TOTAL_ACT_AMT END) AS C20, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_ALLOW_HRS ELSE RPT_REVENUE_WKS.ITD_ACT_AL_AMT END) AS C21, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.YTD_ACT_AL_AMT END) AS C22, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.PTD_ACT_AL_AMT END) AS C23, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_HRS ELSE RPT_REVENUE_WKS.ITD_TOTAL_ACT_AMT END - CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_ALLOW_HRS ELSE RPT_REVENUE_WKS.ITD_ACT_AL_AMT END) AS C24, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_HRS ELSE RPT_REVENUE_WKS.YTD_TOTAL_ACT_AMT END - CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.YTD_ACT_AL_AMT END) AS C25, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_HRS ELSE RPT_REVENUE_WKS.PTD_TOTAL_ACT_AMT END - CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.PTD_ACT_AL_AMT END) AS C26, SUM(RPT_REVENUE_WKS.ITD_ACT_FEE_AMT) AS C27, SUM(RPT_REVENUE_WKS.YTD_ACT_FEE_AMT) AS C28, SUM(RPT_REVENUE_WKS.PTD_ACT_FEE_AMT) AS C29, SUM(RPT_REVENUE_WKS.ITD_FEE_ON_HRS_AMT) AS C30, SUM(RPT_REVENUE_WKS.YTD_FEE_ON_HRS_AMT) AS C31, SUM(RPT_REVENUE_WKS.PTD_FEE_ON_HRS_AMT) AS C32, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_ALLOW_HRS ELSE RPT_REVENUE_WKS.ITD_ACT_AL_AMT END * CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END ELSE 0 END + CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN 0 ELSE ((CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_ALLOW_HRS ELSE RPT_REVENUE_WKS.ITD_ACT_AL_AMT END + RPT_REVENUE_WKS.ITD_ACT_FEE_AMT) + RPT_REVENUE_WKS.ITD_FEE_ON_HRS_AMT) END) AS C33, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.YTD_ACT_AL_AMT END * CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END ELSE 0 END + CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN 0 ELSE ((CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.YTD_ACT_AL_AMT END + RPT_REVENUE_WKS.YTD_ACT_FEE_AMT) + RPT_REVENUE_WKS.YTD_FEE_ON_HRS_AMT) END) AS C34, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.PTD_ACT_AL_AMT END * CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END ELSE 0 END + CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN 0 ELSE ((CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.PTD_ACT_AL_AMT END + RPT_REVENUE_WKS.PTD_ACT_FEE_AMT) + RPT_REVENUE_WKS.PTD_FEE_ON_HRS_AMT) END) AS C35, SUM(RPT_REVENUE_WKS.ITD_REV_ADJ_AMT) AS C36, SUM(RPT_REVENUE_WKS.YTD_REV_ADJ_AMT) AS C37, SUM(RPT_REVENUE_WKS.PTD_REV_ADJ_AMT) AS C38, SUM(RPT_REVENUE_WKS.ITD_REV_UNITS_AMT) AS C39, SUM(RPT_REVENUE_WKS.YTD_REV_UNITS_AMT) AS C40, SUM(RPT_REVENUE_WKS.PTD_REV_UNITS_AMT) AS C41, SUM(RPT_REVENUE_WKS.ITD_OT_FEE_ACT_AMT) AS C42, SUM(RPT_REVENUE_WKS.YTD_OT_FEE_ACT_AMT) AS C43, SUM(RPT_REVENUE_WKS.PTD_OT_FEE_ACT_AMT) AS C44, SUM(RPT_REVENUE_WKS.ITD_REALLC_ACT_AMT) AS C45, SUM(RPT_REVENUE_WKS.YTD_REALLC_ACT_AMT) AS C46, SUM(RPT_REVENUE_WKS.PTD_REALLC_ACT_AMT) AS C47, SUM(RPT_REVENUE_WKS.PTD_MARKUP_ACT_AMT) AS C48, SUM(RPT_REVENUE_WKS.ITD_PYREV_ACT_AMT) AS C49, SUM(RPT_REVENUE_WKS.YTD_PYREV_ACT_AMT) AS C50, SUM(RPT_REVENUE_WKS.PTD_PYREV_ACT_AMT) AS C51, SUM(RPT_REVENUE_WKS.ITD_OV_ACT_CST_AMT) AS C52, SUM(RPT_REVENUE_WKS.YTD_OV_ACT_CST_AMT) AS C53, SUM(RPT_REVENUE_WKS.ITD_OV_ACT_TOT_AMT) AS C54, SUM(RPT_REVENUE_WKS.YTD_OV_ACT_TOT_AMT) AS C55, SUM(RPT_REVENUE_WKS.PTD_OV_ACT_TOT_AMT) AS C56, SUM(RPT_REVENUE_WKS.ITD_DISC_ACT_AMT) AS C57, SUM(RPT_REVENUE_WKS.YTD_DISC_ACT_AMT) AS C58, SUM(RPT_REVENUE_WKS.PTD_DISC_ACT_AMT) AS C59, ACCT.ACCT_NAME AS C60 FROM (DELTEK.ACCT ACCT INNER JOIN DELTEK.RPT_REVENUE_WKS RPT_REVENUE_WKS ON ACCT.ACCT_ID = RPT_REVENUE_WKS.ACCT_ID) LEFT OUTER JOIN DELTEK.PROJ PROJ ON RPT_REVENUE_WKS.PROJ_ID = PROJ.PROJ_ID AND RPT_REVENUE_WKS.COMPANY_ID = PROJ.COMPANY_ID WHERE RPT_REVENUE_WKS.FY_CD + RPT_REVENUE_WKS.PD_NO = '2016 - Oct' AND ('Y' = 'Y' AND PROJ.ACTIVE_FL LIKE 'Y%' OR 'Y' = 'A' AND PROJ.ACTIVE_FL IN ('Y', 'N')) GROUP BY PROJ.ORG_ID, RPT_REVENUE_WKS.SUB_TOT_TYPE_NO, CASE WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 1 THEN 'Total Direct Labor' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 2 THEN 'Total Direct Costs' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 3 THEN 'Total Indirect Costs' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 4 THEN 'Total COM' ELSE NULL END, ACCT.ACCT_ID, PROJ.PROJ_ID, RPT_REVENUE_WKS.PROJ_ID, CASE RPT_REVENUE_WKS.SUB_TOT_TYPE_NO WHEN 1 THEN 'Revenue' WHEN 14 THEN 'Revenue' ELSE 'Expense' END, RPT_REVENUE_WKS.COMPANY_ID, RPT_REVENUE_WKS.BILL_LAB_CAT_CD, RPT_REVENUE_WKS.BILL_LAB_CAT_DESC, RPT_REVENUE_WKS.LAST_FIRST_NAME, RPT_REVENUE_WKS.ACCT_ID, CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.BILL_LAB_CAT_CD ELSE CASE WHEN RPT_REVENUE_WKS.POOL_NO = 0 THEN RPT_REVENUE_WKS.ACCT_ID ELSE CAST(RPT_REVENUE_WKS.POOL_NO AS VARCHAR(7)) END END, RPT_REVENUE_WKS.POOL_NO, RPT_REVENUE_WKS.PD_NO, RPT_REVENUE_WKS.FY_CD, CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END, ACCT.ACCT_NAME ) T0 ) Revenue_WorkSheet_Query LEFT OUTER JOIN (SELECT DISTINCT POOL_ALLOC.POOL_NO AS Pool_Number, POOL_ALLOC.POOL_NAME AS Pool_Name FROM DELTEK.POOL_ALLOC POOL_ALLOC ) Pool_Query ON Revenue_WorkSheet_Query.Pool_Number = Pool_Query.Pool_Number GROUP BY Revenue_WorkSheet_Query.Organization_ID, Revenue_WorkSheet_Query.Subtotal_Type_Number, Revenue_WorkSheet_Query.Subtotal_Type_Desc, CASE Revenue_WorkSheet_Query.Subtotal_Type_Number WHEN 1 THEN 'Contractor Salary Threshold ' WHEN 14 THEN 'Award Fee' WHEN 3 THEN Pool_Query.Pool_Name WHEN 4 THEN Pool_Query.Pool_Name WHEN 5 THEN Pool_Query.Pool_Name ELSE Revenue_WorkSheet_Query.Account_Name_Level END, Revenue_WorkSheet_Query.c4, Revenue_WorkSheet_Query.Account_ID_Level, Revenue_WorkSheet_Query.Project_ID, Revenue_WorkSheet_Query.TopGroup, Revenue_WorkSheet_Query.RevExp, Revenue_WorkSheet_Query.Company_ID, Revenue_WorkSheet_Query.Full_Name, Revenue_WorkSheet_Query.Account_ID, Revenue_WorkSheet_Query.Category_Acct, CASE WHEN NOT Revenue_WorkSheet_Query.PLC_Cd IS NULL THEN Revenue_WorkSheet_Query.PLC_Desc ELSE CASE WHEN Revenue_WorkSheet_Query.Pool_Number = 0 THEN Revenue_WorkSheet_Query.Account_Name_Level ELSE Pool_Query.Pool_Name END END, Revenue_WorkSheet_Query.Pool_Number, Revenue_WorkSheet_Query.Period, Revenue_WorkSheet_Query.Fiscal_Year, Revenue_WorkSheet_Query.ITD_Revenue_Rate, Revenue_WorkSheet_Query.YTD_Revenue_Rate, Revenue_WorkSheet_Query.PTD_Revenue_Rate, Revenue_WorkSheet_Query.Account_Name_Level ) T0 ) Revenue_WorkSheet_Final ON Re_org.Own_Org_ID = Revenue_WorkSheet_Final.Organization_ID WHERE 'O' = 'R' AND Re_org.Reorg_ID = '16.AR.FIN000' GROUP BY Re_org.Reorg_ID, Revenue_WorkSheet_Final.Subtotal_Type_Number, Revenue_WorkSheet_Final.Subtotal_Type_Desc, Revenue_WorkSheet_Final.c4, Revenue_WorkSheet_Final.c5, Revenue_WorkSheet_Final.Account_ID_Level, Revenue_WorkSheet_Final.Project_ID, Revenue_WorkSheet_Final.TopGroup, Revenue_WorkSheet_Final.RevExp, Revenue_WorkSheet_Final.Company_ID, Revenue_WorkSheet_Final.Account_ID, Revenue_WorkSheet_Final.Full_Name, Revenue_WorkSheet_Final.Category_Acct, Revenue_WorkSheet_Final.Category_Acct_Desc, Revenue_WorkSheet_Final.Pool_Number, Revenue_WorkSheet_Final.Period, Revenue_WorkSheet_Final.Fiscal_Year, Revenue_WorkSheet_Final.ITD_Revenue_Rate, Revenue_WorkSheet_Final.YTD_Revenue_Rate, Revenue_WorkSheet_Final.PTD_Revenue_Rate, Revenue_WorkSheet_Final.Account_Name_Level;

Are you sure that's the correct query? Because "WHERE 'O' = 'R' AND ..." is not going to match anything. There's other similar oddities:
AND ('Y' = 'Y' AND PROJ.ACTIVE_FL LIKE 'Y%' OR 'Y' = 'A' AND PROJ.ACTIVE_FL IN ('Y', 'N'))

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sreesree.96 SSC-Addicted Points: 468 More actions · Answer 4

I actually forgot mention ignore the where clause.! And i big thank you for helping me out.!

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Looking at the SQLPLAN, I see there are 3 massive sorts to support the GROUP BY operators you have. In the Oracle query, I see there is only 1 GROUP BY, while the SQL Server query has 3. Considering how many columns are in each of the 3 with complex CASE statements inside the GROUP BY, this is my initial guess why it's taking longer on SQL Server. What was the reason going from 1 to 3 groupings?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

sreesree.96 - Thursday, April 20, 2017 2:58 PM
And i big thank you for helping me out.!

Actual execution plan? Table and index definitions?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sreesree.96 SSC-Addicted Points: 468 More actions · Answer 7

GilaMonster - Thursday, April 20, 2017 2:22 PM
Table definitions and index definitions?
And an actual execution plan would be much more useful. Run the query with the 'include actual execution plan' option turned on (button on SSMS's toolbar), and include that plan.
And for anyone interested, here's the query formated to be readable
SELECT Re_org.Reorg_ID AS Reorg_ID, Revenue_WorkSheet_Final.Subtotal_Type_Number AS Subtotal_Type_Number, Revenue_WorkSheet_Final.Subtotal_Type_Desc AS Subtotal_Type_Desc, Revenue_WorkSheet_Final.c4 AS c4, Revenue_WorkSheet_Final.c5 AS c5, Revenue_WorkSheet_Final.Account_ID_Level AS Account_ID_Level, Revenue_WorkSheet_Final.Project_ID AS Project_ID, Revenue_WorkSheet_Final.TopGroup AS TopGroup, Revenue_WorkSheet_Final.RevExp AS RevExp, Revenue_WorkSheet_Final.Company_ID AS Company_ID, Revenue_WorkSheet_Final.Account_ID AS Account_ID, Revenue_WorkSheet_Final.Full_Name AS Full_Name, Revenue_WorkSheet_Final.Category_Acct AS Category_Acct, Revenue_WorkSheet_Final.Category_Acct_Desc AS Category_Acct_Desc, Revenue_WorkSheet_Final.Pool_Number AS Pool_Number, Revenue_WorkSheet_Final.Period AS Period, Revenue_WorkSheet_Final.Fiscal_Year AS Fiscal_Year, Revenue_WorkSheet_Final.ITD_Revenue_Rate AS ITD_Revenue_Rate, Revenue_WorkSheet_Final.YTD_Revenue_Rate AS YTD_Revenue_Rate, Revenue_WorkSheet_Final.PTD_Revenue_Rate AS PTD_Revenue_Rate, SUM(Revenue_WorkSheet_Final.ITD_Total_Hours_Cost) AS ITD_Total_Hours_Cost, SUM(Revenue_WorkSheet_Final.YTD_Total_Hours_Cost) AS YTD_Total_Hours_Cost, SUM(Revenue_WorkSheet_Final.PTD_Total_Hours_Cost) AS PTD_Total_Hours_Cost, SUM(Revenue_WorkSheet_Final.ITD_Allowable_Hours_Cost) AS ITD_Allowable_Hours_Cost, SUM(Revenue_WorkSheet_Final.YTD_Allowable_Hours_Cost) AS YTD_Allowable_Hours_Cost, SUM(Revenue_WorkSheet_Final.PTD_Allowable_Hours_Cost) AS PTD_Allowable_Hours_Cost, SUM(Revenue_WorkSheet_Final.c27) AS c27, SUM(Revenue_WorkSheet_Final.c28) AS c28, SUM(Revenue_WorkSheet_Final.c29) AS c29, SUM(Revenue_WorkSheet_Final.ITD_Fee) AS ITD_Fee, SUM(Revenue_WorkSheet_Final.YTD_Fee) AS YTD_Fee, SUM(Revenue_WorkSheet_Final.PTD_Fee) AS PTD_Fee, SUM(Revenue_WorkSheet_Final.ITD_Fee_On_Hours) AS ITD_Fee_On_Hours, SUM(Revenue_WorkSheet_Final.YTD_Fee_On_Hours) AS YTD_Fee_On_Hours, SUM(Revenue_WorkSheet_Final.PTD_Fee_On_Hours) AS PTD_Fee_On_Hours, SUM(Revenue_WorkSheet_Final.ITD_Revenue) AS ITD_Revenue, SUM(Revenue_WorkSheet_Final.YTD_Revenue) AS YTD_Revenue, SUM(Revenue_WorkSheet_Final.PTD_Revenue) AS PTD_Revenue, SUM(Revenue_WorkSheet_Final.ITD_Revenue_Adjustment_Amount) AS ITD_Revenue_Adjustment_Amount, SUM(Revenue_WorkSheet_Final.YTD_Revenue_Adjustment_Amount) AS YTD_Revenue_Adjustment_Amount, SUM(Revenue_WorkSheet_Final.PTD_Revenue_Adjustment_Amount) AS PTD_Revenue_Adjustment_Amount, SUM(Revenue_WorkSheet_Final.ITD_Revenue_Units_Amount) AS ITD_Revenue_Units_Amount, SUM(Revenue_WorkSheet_Final.YTD_Revenue_Units_Amount) AS YTD_Revenue_Units_Amount, SUM(Revenue_WorkSheet_Final.PTD_Revenue_Units_Amount) AS PTD_Revenue_Units_Amount, SUM(Revenue_WorkSheet_Final.ITD_Subtotal_Revenue) AS ITD_Subtotal_Revenue, SUM(Revenue_WorkSheet_Final.YTD_Subtotal_Revenue) AS YTD_Subtotal_Revenue, SUM(Revenue_WorkSheet_Final.PTD_Subtotal_Revenue) AS PTD_Subtotal_Revenue, SUM(Revenue_WorkSheet_Final.ITD_Other_Fee) AS ITD_Other_Fee, SUM(Revenue_WorkSheet_Final.YTD_Other_Fee) AS YTD_Other_Fee, SUM(Revenue_WorkSheet_Final.PTD_Other_Fee) AS PTD_Other_Fee, SUM(Revenue_WorkSheet_Final.ITD_Reallocation_Amount) AS ITD_Reallocation_Amount, SUM(Revenue_WorkSheet_Final.YTD_Reallocation_Amount) AS YTD_Reallocation_Amount, SUM(Revenue_WorkSheet_Final.PTD_Reallocation_Amount) AS PTD_Reallocation_Amount, SUM(Revenue_WorkSheet_Final.PTD_Retroactive_Revenue) AS PTD_Retroactive_Revenue, SUM(Revenue_WorkSheet_Final.c55) AS c55, SUM(Revenue_WorkSheet_Final.c56) AS c56, SUM(Revenue_WorkSheet_Final.c57) AS c57, SUM(Revenue_WorkSheet_Final.ITD_Less__Cost_in_Excess) AS ITD_Less__Cost_in_Excess, SUM(Revenue_WorkSheet_Final.YTD_Less__Cost_in_Excess) AS YTD_Less__Cost_in_Excess, SUM(Revenue_WorkSheet_Final.PTD_Less__Cost_in_Excess) AS PTD_Less__Cost_in_Excess, SUM(Revenue_WorkSheet_Final.ITD_Less__Total_in_Excess) AS ITD_Less__Total_in_Excess, SUM(Revenue_WorkSheet_Final.YTD_Less__Total_in_Excess) AS YTD_Less__Total_in_Excess, SUM(Revenue_WorkSheet_Final.PTD_Less__Total_in_Excess) AS PTD_Less__Total_in_Excess, SUM(Revenue_WorkSheet_Final.ITD_Less__Discount_Amount) AS ITD_Less__Discount_Amount, SUM(Revenue_WorkSheet_Final.YTD_Less__Discount_Amount) AS YTD_Less__Discount_Amount, SUM(Revenue_WorkSheet_Final.PTD_Less__Discount_Amount) AS PTD_Less__Discount_Amount, SUM(Revenue_WorkSheet_Final.ITD_Total_Revenue) AS ITD_Total_Revenue, SUM(Revenue_WorkSheet_Final.YTD_Total_Revenue) AS YTD_Total_Revenue, SUM(Revenue_WorkSheet_Final.PTD_Total_Revenue) AS PTD_Total_Revenue, Revenue_WorkSheet_Final.Account_Name_Level AS Account_Name_Level, SUM(Revenue_WorkSheet_Final.Total_ITD_Total_Hours_Cost_) AS Total_ITD_Total_Hours_Cost_, SUM(Revenue_WorkSheet_Final.c72) AS c72, SUM(Revenue_WorkSheet_Final.c73) AS c73, SUM(Revenue_WorkSheet_Final.Total_ITD_Fee_) AS Total_ITD_Fee_, SUM(Revenue_WorkSheet_Final.Total_ITD_Fee_On_Hours_) AS Total_ITD_Fee_On_Hours_, SUM(Revenue_WorkSheet_Final.Total_ITD_Revenue_) AS Total_ITD_Revenue_, SUM(Revenue_WorkSheet_Final.Total_YTD_Total_Hours_Cost_) AS Total_YTD_Total_Hours_Cost_, SUM(Revenue_WorkSheet_Final.c78) AS c78, SUM(Revenue_WorkSheet_Final.c79) AS c79, SUM(Revenue_WorkSheet_Final.Total_YTD_Fee_) AS Total_YTD_Fee_, SUM(Revenue_WorkSheet_Final.Total_YTD_Fee_On_Hours_) AS Total_YTD_Fee_On_Hours_, SUM(Revenue_WorkSheet_Final.Total_YTD_Revenue_) AS Total_YTD_Revenue_, SUM(Revenue_WorkSheet_Final.Total_PTD_Total_Hours_Cost_) AS Total_PTD_Total_Hours_Cost_, SUM(Revenue_WorkSheet_Final.c84) AS c84, SUM(Revenue_WorkSheet_Final.c85) AS c85, SUM(Revenue_WorkSheet_Final.Total_PTD_Fee_) AS Total_PTD_Fee_, SUM(Revenue_WorkSheet_Final.Total_PTD_Fee_On_Hours_) AS Total_PTD_Fee_On_Hours_, SUM(Revenue_WorkSheet_Final.Total_PTD_Revenue_) AS Total_PTD_Revenue_ FROM (SELECT DISTINCT REORG_STRUC.REORG_ID AS Reorg_ID, REORG_STRUC.REORG_NAME AS Reorg_Name, REORG_ORG_LINK.ORG_ID AS Own_Org_ID FROM DELTEK.REORG_STRUC REORG_STRUC INNER JOIN DELTEK.REORG_ORG_LINK REORG_ORG_LINK ON REORG_ORG_LINK.REORG_ID = REORG_STRUC.REORG_ID AND REORG_ORG_LINK.COMPANY_ID = REORG_STRUC.COMPANY_ID ) Re_org INNER JOIN (SELECT T0.C0 AS Organization_ID, T0.C1 AS Subtotal_Type_Number, T0.C2 AS Subtotal_Type_Desc, T0.C3 AS c4, T0.C4 AS c5, T0.C5 AS Account_ID_Level, T0.C6 AS Project_ID, T0.C7 AS TopGroup, T0.C8 AS RevExp, T0.C9 AS Company_ID, T0.C10 AS Full_Name, T0.C11 AS Account_ID, T0.C12 AS Category_Acct, T0.C13 AS Category_Acct_Desc, T0.C14 AS Pool_Number, T0.C15 AS Period, T0.C16 AS Fiscal_Year, T0.C17 AS ITD_Revenue_Rate, T0.C18 AS YTD_Revenue_Rate, T0.C19 AS PTD_Revenue_Rate, T0.C20 AS ITD_Total_Hours_Cost, T0.C21 AS YTD_Total_Hours_Cost, T0.C22 AS PTD_Total_Hours_Cost, T0.C23 AS ITD_Allowable_Hours_Cost, T0.C24 AS YTD_Allowable_Hours_Cost, T0.C25 AS PTD_Allowable_Hours_Cost, T0.C26 AS c27, T0.C27 AS c28, T0.C28 AS c29, T0.C29 AS ITD_Fee, T0.C30 AS YTD_Fee, T0.C31 AS PTD_Fee, T0.C32 AS ITD_Fee_On_Hours, T0.C33 AS YTD_Fee_On_Hours, T0.C34 AS PTD_Fee_On_Hours, T0.C35 AS ITD_Revenue, T0.C36 AS YTD_Revenue, T0.C37 AS PTD_Revenue, T0.C38 AS ITD_Revenue_Adjustment_Amount, T0.C39 AS YTD_Revenue_Adjustment_Amount, T0.C40 AS PTD_Revenue_Adjustment_Amount, T0.C41 AS ITD_Revenue_Units_Amount, T0.C42 AS YTD_Revenue_Units_Amount, T0.C43 AS PTD_Revenue_Units_Amount, T0.C44 AS ITD_Subtotal_Revenue, T0.C45 AS YTD_Subtotal_Revenue, T0.C46 AS PTD_Subtotal_Revenue, T0.C47 AS ITD_Other_Fee, T0.C48 AS YTD_Other_Fee, T0.C49 AS PTD_Other_Fee, T0.C50 AS ITD_Reallocation_Amount, T0.C51 AS YTD_Reallocation_Amount, T0.C52 AS PTD_Reallocation_Amount, T0.C53 AS PTD_Retroactive_Revenue, T0.C54 AS c55, T0.C55 AS c56, T0.C56 AS c57, T0.C57 AS ITD_Less__Cost_in_Excess, T0.C58 AS YTD_Less__Cost_in_Excess, T0.C59 AS PTD_Less__Cost_in_Excess, T0.C60 AS ITD_Less__Total_in_Excess, T0.C61 AS YTD_Less__Total_in_Excess, T0.C62 AS PTD_Less__Total_in_Excess, T0.C63 AS ITD_Less__Discount_Amount, T0.C64 AS YTD_Less__Discount_Amount, T0.C65 AS PTD_Less__Discount_Amount, T0.C66 AS ITD_Total_Revenue, T0.C67 AS YTD_Total_Revenue, T0.C68 AS PTD_Total_Revenue, T0.C69 AS Account_Name_Level, T0.C20 AS Total_ITD_Total_Hours_Cost_, T0.C26 AS c72, T0.C23 AS c73, T0.C29 AS Total_ITD_Fee_, T0.C32 AS Total_ITD_Fee_On_Hours_, T0.C35 AS Total_ITD_Revenue_, T0.C21 AS Total_YTD_Total_Hours_Cost_, T0.C27 AS c78, T0.C24 AS c79, T0.C30 AS Total_YTD_Fee_, T0.C33 AS Total_YTD_Fee_On_Hours_, T0.C36 AS Total_YTD_Revenue_, T0.C22 AS Total_PTD_Total_Hours_Cost_, T0.C28 AS c84, T0.C25 AS c85, T0.C31 AS Total_PTD_Fee_, T0.C34 AS Total_PTD_Fee_On_Hours_, T0.C37 AS Total_PTD_Revenue_ FROM (SELECT Revenue_WorkSheet_Query.Organization_ID AS C0, Revenue_WorkSheet_Query.Subtotal_Type_Number AS C1, Revenue_WorkSheet_Query.Subtotal_Type_Desc AS C2, CASE Revenue_WorkSheet_Query.Subtotal_Type_Number WHEN 1 THEN 'Contractor Salary Threshold ' WHEN 14 THEN 'Award Fee' WHEN 3 THEN Pool_Query.Pool_Name WHEN 4 THEN Pool_Query.Pool_Name WHEN 5 THEN Pool_Query.Pool_Name ELSE Revenue_WorkSheet_Query.Account_Name_Level END AS C3, Revenue_WorkSheet_Query.c4 AS C4, Revenue_WorkSheet_Query.Account_ID_Level AS C5, Revenue_WorkSheet_Query.Project_ID AS C6, Revenue_WorkSheet_Query.TopGroup AS C7, Revenue_WorkSheet_Query.RevExp AS C8, Revenue_WorkSheet_Query.Company_ID AS C9, Revenue_WorkSheet_Query.Full_Name AS C10, Revenue_WorkSheet_Query.Account_ID AS C11, Revenue_WorkSheet_Query.Category_Acct AS C12, CASE WHEN NOT Revenue_WorkSheet_Query.PLC_Cd IS NULL THEN Revenue_WorkSheet_Query.PLC_Desc ELSE CASE WHEN Revenue_WorkSheet_Query.Pool_Number = 0 THEN Revenue_WorkSheet_Query.Account_Name_Level ELSE Pool_Query.Pool_Name END END AS C13, Revenue_WorkSheet_Query.Pool_Number AS C14, Revenue_WorkSheet_Query.Period AS C15, Revenue_WorkSheet_Query.Fiscal_Year AS C16, Revenue_WorkSheet_Query.ITD_Revenue_Rate AS C17, Revenue_WorkSheet_Query.YTD_Revenue_Rate AS C18, Revenue_WorkSheet_Query.PTD_Revenue_Rate AS C19, SUM(Revenue_WorkSheet_Query.ITD_Total_Hours_Cost) AS C20, SUM(Revenue_WorkSheet_Query.YTD_Total_Hours_Cost) AS C21, SUM(Revenue_WorkSheet_Query.PTD_Total_Hours_Cost) AS C22, SUM(Revenue_WorkSheet_Query.ITD_Allowable_Hours_Cost) AS C23, SUM(Revenue_WorkSheet_Query.YTD_Allowable_Hours_Cost) AS C24, SUM(Revenue_WorkSheet_Query.PTD_Allowable_Hours_Cost) AS C25, SUM(Revenue_WorkSheet_Query.c28) AS C26, SUM(Revenue_WorkSheet_Query.c29) AS C27, SUM(Revenue_WorkSheet_Query.c30) AS C28, SUM(Revenue_WorkSheet_Query.ITD_Fee) AS C29, SUM(Revenue_WorkSheet_Query.YTD_Fee) AS C30, SUM(Revenue_WorkSheet_Query.PTD_Fee) AS C31, SUM(Revenue_WorkSheet_Query.ITD_Fee_On_Hours) AS C32, SUM(Revenue_WorkSheet_Query.YTD_Fee_On_Hours) AS C33, SUM(Revenue_WorkSheet_Query.PTD_Fee_On_Hours) AS C34, SUM(Revenue_WorkSheet_Query.ITD_Revenue) AS C35, SUM(Revenue_WorkSheet_Query.YTD_Revenue) AS C36, SUM(Revenue_WorkSheet_Query.PTD_Revenue) AS C37, SUM(Revenue_WorkSheet_Query.ITD_Revenue_Adjustment_Amount) AS C38, SUM(Revenue_WorkSheet_Query.YTD_Revenue_Adjustment_Amount) AS C39, SUM(Revenue_WorkSheet_Query.PTD_Revenue_Adjustment_Amount) AS C40, SUM(Revenue_WorkSheet_Query.ITD_Revenue_Units_Amount) AS C41, SUM(Revenue_WorkSheet_Query.YTD_Revenue_Units_Amount) AS C42, SUM(Revenue_WorkSheet_Query.PTD_Revenue_Units_Amount) AS C43, ((SUM(Revenue_WorkSheet_Query.ITD_Revenue) + SUM(Revenue_WorkSheet_Query.ITD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.ITD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.ITD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.ITD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c53)) AS C44, ((SUM(Revenue_WorkSheet_Query.YTD_Revenue) + SUM(Revenue_WorkSheet_Query.YTD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.YTD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.YTD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.YTD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c54)) AS C45, (((SUM(Revenue_WorkSheet_Query.PTD_Revenue) + SUM(Revenue_WorkSheet_Query.PTD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.PTD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.PTD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.PTD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c55))) + SUM(Revenue_WorkSheet_Query.PTD_Retroactive_Revenue) AS C46, SUM(Revenue_WorkSheet_Query.ITD_Other_Fee) AS C47, SUM(Revenue_WorkSheet_Query.YTD_Other_Fee) AS C48, SUM(Revenue_WorkSheet_Query.PTD_Other_Fee) AS C49, SUM(Revenue_WorkSheet_Query.ITD_Reallocation_Amount) AS C50, SUM(Revenue_WorkSheet_Query.YTD_Reallocation_Amount) AS C51, SUM(Revenue_WorkSheet_Query.PTD_Reallocation_Amount) AS C52, SUM(Revenue_WorkSheet_Query.PTD_Retroactive_Revenue) AS C53, SUM(Revenue_WorkSheet_Query.c53) AS C54, SUM(Revenue_WorkSheet_Query.c54) AS C55, SUM(Revenue_WorkSheet_Query.c55) AS C56, SUM(Revenue_WorkSheet_Query.ITD_Less__Cost_in_Excess) AS C57, SUM(Revenue_WorkSheet_Query.YTD_Less__Cost_in_Excess) AS C58, SUM(Revenue_WorkSheet_Query.PTD_Less__Cost_in_Excess) AS C59, SUM(Revenue_WorkSheet_Query.ITD_Less__Total_in_Excess) AS C60, SUM(Revenue_WorkSheet_Query.YTD_Less__Total_in_Excess) AS C61, SUM(Revenue_WorkSheet_Query.PTD_Less__Total_in_Excess) AS C62, SUM(Revenue_WorkSheet_Query.ITD_Less__Discount_Amount) AS C63, SUM(Revenue_WorkSheet_Query.YTD_Less__Discount_Amount) AS C64, SUM(Revenue_WorkSheet_Query.PTD_Less__Discount_Amount) AS C65, (((SUM(Revenue_WorkSheet_Query.ITD_Revenue) + SUM(Revenue_WorkSheet_Query.ITD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.ITD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.ITD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.ITD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c53))) - ((SUM(Revenue_WorkSheet_Query.ITD_Less__Cost_in_Excess) + SUM(Revenue_WorkSheet_Query.ITD_Less__Total_in_Excess)) + SUM(Revenue_WorkSheet_Query.ITD_Less__Discount_Amount)) AS C66, (((SUM(Revenue_WorkSheet_Query.YTD_Revenue) + SUM(Revenue_WorkSheet_Query.YTD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.YTD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.YTD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.YTD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c54))) - ((SUM(Revenue_WorkSheet_Query.YTD_Less__Cost_in_Excess) + SUM(Revenue_WorkSheet_Query.YTD_Less__Total_in_Excess)) + SUM(Revenue_WorkSheet_Query.YTD_Less__Discount_Amount)) AS C67, ((((SUM(Revenue_WorkSheet_Query.PTD_Revenue) + SUM(Revenue_WorkSheet_Query.PTD_Revenue_Adjustment_Amount)) + (SUM(Revenue_WorkSheet_Query.PTD_Revenue_Units_Amount) + SUM(Revenue_WorkSheet_Query.PTD_Other_Fee))) + (SUM(Revenue_WorkSheet_Query.PTD_Reallocation_Amount) + SUM(Revenue_WorkSheet_Query.c55))) + SUM(Revenue_WorkSheet_Query.PTD_Retroactive_Revenue)) - ((SUM(Revenue_WorkSheet_Query.PTD_Less__Cost_in_Excess) + SUM(Revenue_WorkSheet_Query.PTD_Less__Total_in_Excess)) + SUM(Revenue_WorkSheet_Query.PTD_Less__Discount_Amount)) AS C68, Revenue_WorkSheet_Query.Account_Name_Level AS C69 FROM (SELECT T0.C0 AS Organization_ID, T0.C1 AS Subtotal_Type_Number, T0.C2 AS Subtotal_Type_Desc, T0.C3 AS c4, T0.C4 AS Account_ID_Level, T0.C5 AS Project_ID_Level, T0.C6 AS Project_ID, T0.C5 AS TopGroup, T0.C7 AS RevExp, T0.C8 AS Company_ID, T0.C9 AS PLC_Cd, T0.C10 AS PLC_Desc, T0.C11 AS Full_Name, T0.C12 AS Account_ID, T0.C13 AS Category_Acct, T0.C14 AS Pool_Number, T0.C15 AS Period, T0.C16 AS Fiscal_Year, T0.C17 AS ITD_Revenue_Rate, T0.C17 AS YTD_Revenue_Rate, T0.C17 AS PTD_Revenue_Rate, T0.C18 AS ITD_Total_Hours_Cost, T0.C19 AS YTD_Total_Hours_Cost, T0.C20 AS PTD_Total_Hours_Cost, T0.C21 AS ITD_Allowable_Hours_Cost, T0.C22 AS YTD_Allowable_Hours_Cost, T0.C23 AS PTD_Allowable_Hours_Cost, T0.C24 AS c28, T0.C25 AS c29, T0.C26 AS c30, T0.C27 AS ITD_Fee, T0.C28 AS YTD_Fee, T0.C29 AS PTD_Fee, T0.C30 AS ITD_Fee_On_Hours, T0.C31 AS YTD_Fee_On_Hours, T0.C32 AS PTD_Fee_On_Hours, T0.C33 AS ITD_Revenue, T0.C34 AS YTD_Revenue, T0.C35 AS PTD_Revenue, T0.C36 AS ITD_Revenue_Adjustment_Amount, T0.C37 AS YTD_Revenue_Adjustment_Amount, T0.C38 AS PTD_Revenue_Adjustment_Amount, T0.C39 AS ITD_Revenue_Units_Amount, T0.C40 AS YTD_Revenue_Units_Amount, T0.C41 AS PTD_Revenue_Units_Amount, T0.C42 AS ITD_Other_Fee, T0.C43 AS YTD_Other_Fee, T0.C44 AS PTD_Other_Fee, T0.C45 AS ITD_Reallocation_Amount, T0.C46 AS YTD_Reallocation_Amount, T0.C47 AS PTD_Reallocation_Amount, T0.C48 AS PTD_Retroactive_Revenue, T0.C49 AS c53, T0.C50 AS c54, T0.C51 AS c55, T0.C52 AS ITD_Less__Cost_in_Excess, T0.C53 AS YTD_Less__Cost_in_Excess, T0.C52 AS PTD_Less__Cost_in_Excess, T0.C54 AS ITD_Less__Total_in_Excess, T0.C55 AS YTD_Less__Total_in_Excess, T0.C56 AS PTD_Less__Total_in_Excess, T0.C57 AS ITD_Less__Discount_Amount, T0.C58 AS YTD_Less__Discount_Amount, T0.C59 AS PTD_Less__Discount_Amount, T0.C60 AS Account_Name_Level FROM (SELECT PROJ.ORG_ID AS C0, RPT_REVENUE_WKS.SUB_TOT_TYPE_NO AS C1, CASE WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 1 THEN 'Total Direct Labor' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 2 THEN 'Total Direct Costs' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 3 THEN 'Total Indirect Costs' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 4 THEN 'Total COM' ELSE NULL END AS C2, '' AS C3, ACCT.ACCT_ID AS C4, PROJ.PROJ_ID AS C5, RPT_REVENUE_WKS.PROJ_ID AS C6, CASE RPT_REVENUE_WKS.SUB_TOT_TYPE_NO WHEN 1 THEN 'Revenue' WHEN 14 THEN 'Revenue' ELSE 'Expense' END AS C7, RPT_REVENUE_WKS.COMPANY_ID AS C8, RPT_REVENUE_WKS.BILL_LAB_CAT_CD AS C9, RPT_REVENUE_WKS.BILL_LAB_CAT_DESC AS C10, RPT_REVENUE_WKS.LAST_FIRST_NAME AS C11, RPT_REVENUE_WKS.ACCT_ID AS C12, CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.BILL_LAB_CAT_CD ELSE CASE WHEN RPT_REVENUE_WKS.POOL_NO = 0 THEN RPT_REVENUE_WKS.ACCT_ID ELSE CAST(RPT_REVENUE_WKS.POOL_NO AS VARCHAR(7)) END END AS C13, RPT_REVENUE_WKS.POOL_NO AS C14, RPT_REVENUE_WKS.PD_NO AS C15, RPT_REVENUE_WKS.FY_CD AS C16, CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END AS C17, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_HRS ELSE RPT_REVENUE_WKS.ITD_TOTAL_ACT_AMT END) AS C18, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_HRS ELSE RPT_REVENUE_WKS.YTD_TOTAL_ACT_AMT END) AS C19, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_HRS ELSE RPT_REVENUE_WKS.PTD_TOTAL_ACT_AMT END) AS C20, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_ALLOW_HRS ELSE RPT_REVENUE_WKS.ITD_ACT_AL_AMT END) AS C21, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.YTD_ACT_AL_AMT END) AS C22, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.PTD_ACT_AL_AMT END) AS C23, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_HRS ELSE RPT_REVENUE_WKS.ITD_TOTAL_ACT_AMT END - CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_ALLOW_HRS ELSE RPT_REVENUE_WKS.ITD_ACT_AL_AMT END) AS C24, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_HRS ELSE RPT_REVENUE_WKS.YTD_TOTAL_ACT_AMT END - CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.YTD_ACT_AL_AMT END) AS C25, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_HRS ELSE RPT_REVENUE_WKS.PTD_TOTAL_ACT_AMT END - CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.PTD_ACT_AL_AMT END) AS C26, SUM(RPT_REVENUE_WKS.ITD_ACT_FEE_AMT) AS C27, SUM(RPT_REVENUE_WKS.YTD_ACT_FEE_AMT) AS C28, SUM(RPT_REVENUE_WKS.PTD_ACT_FEE_AMT) AS C29, SUM(RPT_REVENUE_WKS.ITD_FEE_ON_HRS_AMT) AS C30, SUM(RPT_REVENUE_WKS.YTD_FEE_ON_HRS_AMT) AS C31, SUM(RPT_REVENUE_WKS.PTD_FEE_ON_HRS_AMT) AS C32, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_ALLOW_HRS ELSE RPT_REVENUE_WKS.ITD_ACT_AL_AMT END * CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END ELSE 0 END + CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN 0 ELSE ((CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.ITD_ALLOW_HRS ELSE RPT_REVENUE_WKS.ITD_ACT_AL_AMT END + RPT_REVENUE_WKS.ITD_ACT_FEE_AMT) + RPT_REVENUE_WKS.ITD_FEE_ON_HRS_AMT) END) AS C33, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.YTD_ACT_AL_AMT END * CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END ELSE 0 END + CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN 0 ELSE ((CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.YTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.YTD_ACT_AL_AMT END + RPT_REVENUE_WKS.YTD_ACT_FEE_AMT) + RPT_REVENUE_WKS.YTD_FEE_ON_HRS_AMT) END) AS C34, SUM(CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.PTD_ACT_AL_AMT END * CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END ELSE 0 END + CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN 0 ELSE ((CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.PTD_ALLOW_HRS ELSE RPT_REVENUE_WKS.PTD_ACT_AL_AMT END + RPT_REVENUE_WKS.PTD_ACT_FEE_AMT) + RPT_REVENUE_WKS.PTD_FEE_ON_HRS_AMT) END) AS C35, SUM(RPT_REVENUE_WKS.ITD_REV_ADJ_AMT) AS C36, SUM(RPT_REVENUE_WKS.YTD_REV_ADJ_AMT) AS C37, SUM(RPT_REVENUE_WKS.PTD_REV_ADJ_AMT) AS C38, SUM(RPT_REVENUE_WKS.ITD_REV_UNITS_AMT) AS C39, SUM(RPT_REVENUE_WKS.YTD_REV_UNITS_AMT) AS C40, SUM(RPT_REVENUE_WKS.PTD_REV_UNITS_AMT) AS C41, SUM(RPT_REVENUE_WKS.ITD_OT_FEE_ACT_AMT) AS C42, SUM(RPT_REVENUE_WKS.YTD_OT_FEE_ACT_AMT) AS C43, SUM(RPT_REVENUE_WKS.PTD_OT_FEE_ACT_AMT) AS C44, SUM(RPT_REVENUE_WKS.ITD_REALLC_ACT_AMT) AS C45, SUM(RPT_REVENUE_WKS.YTD_REALLC_ACT_AMT) AS C46, SUM(RPT_REVENUE_WKS.PTD_REALLC_ACT_AMT) AS C47, SUM(RPT_REVENUE_WKS.PTD_MARKUP_ACT_AMT) AS C48, SUM(RPT_REVENUE_WKS.ITD_PYREV_ACT_AMT) AS C49, SUM(RPT_REVENUE_WKS.YTD_PYREV_ACT_AMT) AS C50, SUM(RPT_REVENUE_WKS.PTD_PYREV_ACT_AMT) AS C51, SUM(RPT_REVENUE_WKS.ITD_OV_ACT_CST_AMT) AS C52, SUM(RPT_REVENUE_WKS.YTD_OV_ACT_CST_AMT) AS C53, SUM(RPT_REVENUE_WKS.ITD_OV_ACT_TOT_AMT) AS C54, SUM(RPT_REVENUE_WKS.YTD_OV_ACT_TOT_AMT) AS C55, SUM(RPT_REVENUE_WKS.PTD_OV_ACT_TOT_AMT) AS C56, SUM(RPT_REVENUE_WKS.ITD_DISC_ACT_AMT) AS C57, SUM(RPT_REVENUE_WKS.YTD_DISC_ACT_AMT) AS C58, SUM(RPT_REVENUE_WKS.PTD_DISC_ACT_AMT) AS C59, ACCT.ACCT_NAME AS C60 FROM (DELTEK.ACCT ACCT INNER JOIN DELTEK.RPT_REVENUE_WKS RPT_REVENUE_WKS ON ACCT.ACCT_ID = RPT_REVENUE_WKS.ACCT_ID) LEFT OUTER JOIN DELTEK.PROJ PROJ ON RPT_REVENUE_WKS.PROJ_ID = PROJ.PROJ_ID AND RPT_REVENUE_WKS.COMPANY_ID = PROJ.COMPANY_ID WHERE RPT_REVENUE_WKS.FY_CD + RPT_REVENUE_WKS.PD_NO = '2016 - Oct' AND ('Y' = 'Y' AND PROJ.ACTIVE_FL LIKE 'Y%' OR 'Y' = 'A' AND PROJ.ACTIVE_FL IN ('Y', 'N')) GROUP BY PROJ.ORG_ID, RPT_REVENUE_WKS.SUB_TOT_TYPE_NO, CASE WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 1 THEN 'Total Direct Labor' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 2 THEN 'Total Direct Costs' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 3 THEN 'Total Indirect Costs' WHEN RPT_REVENUE_WKS.SUB_TOT_TYPE_NO = 4 THEN 'Total COM' ELSE NULL END, ACCT.ACCT_ID, PROJ.PROJ_ID, RPT_REVENUE_WKS.PROJ_ID, CASE RPT_REVENUE_WKS.SUB_TOT_TYPE_NO WHEN 1 THEN 'Revenue' WHEN 14 THEN 'Revenue' ELSE 'Expense' END, RPT_REVENUE_WKS.COMPANY_ID, RPT_REVENUE_WKS.BILL_LAB_CAT_CD, RPT_REVENUE_WKS.BILL_LAB_CAT_DESC, RPT_REVENUE_WKS.LAST_FIRST_NAME, RPT_REVENUE_WKS.ACCT_ID, CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.BILL_LAB_CAT_CD ELSE CASE WHEN RPT_REVENUE_WKS.POOL_NO = 0 THEN RPT_REVENUE_WKS.ACCT_ID ELSE CAST(RPT_REVENUE_WKS.POOL_NO AS VARCHAR(7)) END END, RPT_REVENUE_WKS.POOL_NO, RPT_REVENUE_WKS.PD_NO, RPT_REVENUE_WKS.FY_CD, CASE WHEN NOT RPT_REVENUE_WKS.BILL_LAB_CAT_CD IS NULL THEN RPT_REVENUE_WKS.REV_RT_AMT ELSE 0 END, ACCT.ACCT_NAME ) T0 ) Revenue_WorkSheet_Query LEFT OUTER JOIN (SELECT DISTINCT POOL_ALLOC.POOL_NO AS Pool_Number, POOL_ALLOC.POOL_NAME AS Pool_Name FROM DELTEK.POOL_ALLOC POOL_ALLOC ) Pool_Query ON Revenue_WorkSheet_Query.Pool_Number = Pool_Query.Pool_Number GROUP BY Revenue_WorkSheet_Query.Organization_ID, Revenue_WorkSheet_Query.Subtotal_Type_Number, Revenue_WorkSheet_Query.Subtotal_Type_Desc, CASE Revenue_WorkSheet_Query.Subtotal_Type_Number WHEN 1 THEN 'Contractor Salary Threshold ' WHEN 14 THEN 'Award Fee' WHEN 3 THEN Pool_Query.Pool_Name WHEN 4 THEN Pool_Query.Pool_Name WHEN 5 THEN Pool_Query.Pool_Name ELSE Revenue_WorkSheet_Query.Account_Name_Level END, Revenue_WorkSheet_Query.c4, Revenue_WorkSheet_Query.Account_ID_Level, Revenue_WorkSheet_Query.Project_ID, Revenue_WorkSheet_Query.TopGroup, Revenue_WorkSheet_Query.RevExp, Revenue_WorkSheet_Query.Company_ID, Revenue_WorkSheet_Query.Full_Name, Revenue_WorkSheet_Query.Account_ID, Revenue_WorkSheet_Query.Category_Acct, CASE WHEN NOT Revenue_WorkSheet_Query.PLC_Cd IS NULL THEN Revenue_WorkSheet_Query.PLC_Desc ELSE CASE WHEN Revenue_WorkSheet_Query.Pool_Number = 0 THEN Revenue_WorkSheet_Query.Account_Name_Level ELSE Pool_Query.Pool_Name END END, Revenue_WorkSheet_Query.Pool_Number, Revenue_WorkSheet_Query.Period, Revenue_WorkSheet_Query.Fiscal_Year, Revenue_WorkSheet_Query.ITD_Revenue_Rate, Revenue_WorkSheet_Query.YTD_Revenue_Rate, Revenue_WorkSheet_Query.PTD_Revenue_Rate, Revenue_WorkSheet_Query.Account_Name_Level ) T0 ) Revenue_WorkSheet_Final ON Re_org.Own_Org_ID = Revenue_WorkSheet_Final.Organization_ID WHERE 'O' = 'R' AND Re_org.Reorg_ID = '16.AR.FIN000' GROUP BY Re_org.Reorg_ID, Revenue_WorkSheet_Final.Subtotal_Type_Number, Revenue_WorkSheet_Final.Subtotal_Type_Desc, Revenue_WorkSheet_Final.c4, Revenue_WorkSheet_Final.c5, Revenue_WorkSheet_Final.Account_ID_Level, Revenue_WorkSheet_Final.Project_ID, Revenue_WorkSheet_Final.TopGroup, Revenue_WorkSheet_Final.RevExp, Revenue_WorkSheet_Final.Company_ID, Revenue_WorkSheet_Final.Account_ID, Revenue_WorkSheet_Final.Full_Name, Revenue_WorkSheet_Final.Category_Acct, Revenue_WorkSheet_Final.Category_Acct_Desc, Revenue_WorkSheet_Final.Pool_Number, Revenue_WorkSheet_Final.Period, Revenue_WorkSheet_Final.Fiscal_Year, Revenue_WorkSheet_Final.ITD_Revenue_Rate, Revenue_WorkSheet_Final.YTD_Revenue_Rate, Revenue_WorkSheet_Final.PTD_Revenue_Rate, Revenue_WorkSheet_Final.Account_Name_Level;
Are you sure that's the correct query? Because "WHERE 'O' = 'R' AND ..." is not going to match anything. There's other similar oddities:
AND ('Y' = 'Y' AND PROJ.ACTIVE_FL LIKE 'Y%' OR 'Y' = 'A' AND PROJ.ACTIVE_FL IN ('Y', 'N'))

Hi Guru,

I uploaded the Actual Exc plan and Table definations.
Our Tables doe not have PM key and Fkey.
the Query have 3 different Group by.. which not creating by us.. its Cognos report Query. Is generating 3 group by's. I am really not sure why SQL server is creating 3 group by's but Oracle has only group by. and according to my knowledge there will be no change in the report side. we just referring the cognos report queries.
Please help me to figure out why the SQL query is taking more time compare to ORACLE.?

Appreciate your help.!

Phil Parkin SSC Guru Points: 246965 More actions · Answer 8

Please help me to figure out why the SQL query is taking more time compare to ORACLE.?

Sounds like you figured it out for yourself. You're not comparing like with like.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sreesree.96 SSC-Addicted Points: 468 More actions · Answer 9

Phil Parkin - Friday, April 21, 2017 12:15 PM
Please help me to figure out why the SQL query is taking more time compare to ORACLE.?
Sounds like you figured it out for yourself. You're not comparing like with like.

Thank you For Replay.!

Still banging with my head my friend.! not @ fingered out.
now I attached the Complete ACTUAL EXC Plan. and table definition. under main post/ please refer and that proved me the best solution..!

Tahnak you for your time.!

sreesree.96 SSC-Addicted Points: 468 More actions · Answer 10

sreesree.96 - Friday, April 21, 2017 12:22 PM
Phil Parkin - Friday, April 21, 2017 12:15 PM
Please help me to figure out why the SQL query is taking more time compare to ORACLE.?
Sounds like you figured it out for yourself. You're not comparing like with like.
Thank you For Replay.!
Still banging with my head my friend.! not @ fingered out.
now I attached the Complete ACTUAL EXC Plan. and table definition. under main post/ please refer and that proved me the best solution..!
Tahnak you for your time.!

and Please Ignore all the where condition in the query. I do not worry about the Where and likes.. conditions..

AlexSQLForums SSChampion Points: 14277 More actions · Answer 11

AlexSQLForums

SSChampion

Points: 14277

April 24, 2017 at 5:45 pm

#1939692

Try running sp_updatestats.

Alex S