Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ay2k_99
Starting Member
22 Posts |
 Posted - 2009-05-04 : 00:17:21
|
Hello about my reporting query will take 3 min to get result on server and from network it will take 80 min to display the result..all table is having good index and up-date stats also it will retrive 4,29,334 records. but take long time and take all resource of server like (CPU and memory take only 2 MB while server is having 16 GB ram and 8 processor... select "Union19"."Unit" "Unit" , "Union19"."Currency" "Currency" , "Union19"."Balance_Pool" "Balance_Pool" , "Union19"."Account_ID" "Account_ID" , "Union19"."BAU_Clearing_Period_Number" "BAU_Clearing_Period_Number" , "Union19"."Account_Type" "Account_Type" , "Union19"."Reconciliation_Methodlogy" "Reconciliation_Methodlogy" , "Union19"."Reconciliation_Type" "Reconciliation_Type" , "Union19"."Transaction_ID" "Transaction_ID" , "Union19"."Transaction_Age_Month_Count" "Transaction_Age_Month_Count" , "Union19"."c13" "c11" , "Union19"."Group_Number" "Group_Number" , "Union19"."Value_Date" "Value_Date" , "Union19"."Journal_Entry_Number" "Journal_Entry_Number" , "Union19"."Journal_Entry_Date" "Journal_Entry_Date" , "Union19"."Reference_Number" "Reference_Number" , "Union19"."Transaction19" "Transaction17" , "Union19"."Transaction_Amount" "Transaction_Amount" , "Union19"."Amount__in_US__" "Amount__in_US__" , "Union19"."Corporate_Standard_Direct_Rate" "Corporate_Standard_Direct_Rate" , "Union19"."Source_System_Name" "Source_System_Name" , "Union19"."Transaction_Flag_Indicator" "Transaction_Flag_Indicator" , "Union19"."Reconciliation_Analyst_Name" "Reconciliation_Analyst_Name" , "Union19"."Reconciliation_Approver_Name" "Reconciliation_Approver_Name" , "Union19"."Root_Cause_Code" "Root_Cause_Code" , "Union19"."Action_Plan_Code" "Action_Plan_Code" , "Union19"."Resolution_Date" "Resolution_Date" , "Union19"."Trans_Case_Identifier" "Trans_Case_Identifier" , "Union19"."Transaction_Count" "Transaction_Count" , "Union19"."User_Remarks" "User_Remarks" , "Union19"."Reserve_Required_Indicator" "Reserve_Required_Indicator" , "Union19"."c34" "c32" , "Union19"."Market" "Market" from (select "Account_Dimension"."RgnDsTx" "Region" , "Account_Dimension"."MktDsTx" "Market" , "Account_Dimension"."UnitDsTx" "Unit" , "Account_Dimension"."CurrAbbrDsTx" "Currency" , "Balance_Pool_Dimension"."BalPoolId" "Balance_Pool" , "Account_Dimension"."AcctId" "Account_ID" , "Balance_Pool_Dimension"."BAUClrPerNo" "BAU_Clearing_Period_Number" , "Account_Dimension"."AcctTypeDsTx" "Account_Type" , "T3"."RecnMethodlogyDsTx" "Reconciliation_Methodlogy" , "T3"."RecnTypeDsTx" "Reconciliation_Type" , "Open_Item_Fact"."TransId" "Transaction_ID" , sum("Open_Item_Fact"."TransAgeMoCt") "Transaction_Age_Month_Count" , sum("Open_Item_Fact"."TransAgeClrPerMoCt") "c13" , "Open_Item_Fact"."GrpNo" "Group_Number" , "Open_Item_Fact"."NewValDt" "Value_Date" , "Open_Item_Fact"."JeNo" "Journal_Entry_Number" , "Open_Item_Fact"."JeDt" "Journal_Entry_Date" , "Open_Item_Fact"."RefNo" "Reference_Number" , "Open_Item_Fact"."TransDsTx" "Transaction19" , sum(case when "Open_Item_Fact"."DbCrFlagIn" = 'CR' then "Open_Item_Fact"."TransAm" * -1 else "Open_Item_Fact"."TransAm" end ) "Transaction_Amount" , sum(case when ("Open_Item_Fact"."DbCrFlagIn" = 'CR') then ("Open_Item_Fact"."TransAm" * -1) else "Open_Item_Fact"."TransAm" end * "Corp_Rate_Lookup"."CSR_Rate") "Amount__in_US__" , max("Corp_Rate_Lookup"."CSR_Rate") "Corporate_Standard_Direct_Rate" , "Open_Item_Fact"."SourceSysNm" "Source_System_Name" , "Open_Item_Fact"."TransRemrkTx" "Transaction_Flag_Indicator" , "Reconciler_Dimension"."RecnAnalNm" "Reconciliation_Analyst_Name" , "Reconciler_Dimension"."RecnApproverNm" "Reconciliation_Approver_Name" , "Open_Item_Fact"."RootCauseCdTx" "Root_Cause_Code" , "Open_Item_Fact"."ActPlanCdTx" "Action_Plan_Code" , "Open_Item_Fact"."ResDt" "Resolution_Date" , sum("Open_Item_Fact"."TracsCaseId") "Trans_Case_Identifier" , sum("Open_Item_Fact"."TransCt") "Transaction_Count" , "Open_Item_Fact"."UserRemrkTx" "User_Remarks" , "Open_Item_Fact"."RsvRqrIn" "Reserve_Required_Indicator" , "Balance_Pool_Dimension"."CurRecnMoNo" "c34" from "Account_Dimension" "Account_Dimension" with (index(IX_Account_Dimension_3),nolock), "Balance_Pool_Dimension" "Balance_Pool_Dimension", "Reconciliation_Methodology_Dimension" "T3", "Open_Item_Fact" "Open_Item_Fact" with (index(PK16)), "Reconciler_Dimension" , (select "Account_Dimension_CSR"."AcctSurrogateKeyId" "Account_SK" , "Corporate_Standard_Rate_Fact"."CorpStdRt" "CSR_Rate" from "Reconciliation_Month_Dimension" "T1", "Account_Dimension" "Account_Dimension_CSR", "Corporate_Standard_Rate_Fact" "Corporate_Standard_Rate_Fact" WITH (NOLOCK,index(PK18)) where "T1"."MoId" = '200904' and "T1"."RecnMoSurrogateKeyId" = "Corporate_Standard_Rate_Fact"."RecnMoSurrogateKeyId" and "Account_Dimension_CSR"."CurrAbbrDsTx" = "Corporate_Standard_Rate_Fact"."FromCurrDsTx" and "Corporate_Standard_Rate_Fact"."ToCurrDsTx" = '000') "Corp_Rate_Lookup" where "Balance_Pool_Dimension"."CurRecnMoNo" = 200904 and "Account_Dimension"."RgnDsTx" = 'EMEA' and "Account_Dimension"."BalPoolSurrogateKeyId" = "Balance_Pool_Dimension"."BalPoolSurrogateKeyId" and "Account_Dimension"."AcctSurrogateKeyId" = "Open_Item_Fact"."AcctSurrogateKeyId" and "T3"."RecnMthdSurrogateKeyId" = "Open_Item_Fact"."RecnMthdSurrogateKeyId" and "Reconciler_Dimension"."ReconcilerSurrogateKeyId" = "Open_Item_Fact"."ReconcilerSurrogateKeyId" and "Corp_Rate_Lookup"."Account_SK" = "Open_Item_Fact"."AcctSurrogateKeyId" group by "Account_Dimension"."RgnDsTx", "Account_Dimension"."MktDsTx", "Account_Dimension"."UnitDsTx", "Account_Dimension"."CurrAbbrDsTx", "Balance_Pool_Dimension"."BalPoolId", "Account_Dimension"."AcctId", "Balance_Pool_Dimension"."BAUClrPerNo", "Account_Dimension"."AcctTypeDsTx", "T3"."RecnMethodlogyDsTx", "T3"."RecnTypeDsTx", "Open_Item_Fact"."TransId", "Open_Item_Fact"."GrpNo", "Open_Item_Fact"."NewValDt", "Open_Item_Fact"."JeNo", "Open_Item_Fact"."JeDt", "Open_Item_Fact"."RefNo", "Open_Item_Fact"."TransDsTx", "Open_Item_Fact"."SourceSysNm", "Open_Item_Fact"."TransRemrkTx", "Reconciler_Dimension"."RecnAnalNm", "Reconciler_Dimension"."RecnApproverNm", "Open_Item_Fact"."RootCauseCdTx", "Open_Item_Fact"."ActPlanCdTx", "Open_Item_Fact"."ResDt", "Open_Item_Fact"."UserRemrkTx", "Open_Item_Fact"."RsvRqrIn", "Balance_Pool_Dimension"."CurRecnMoNo" union select "Account_Dimension"."RgnDsTx" "Region" , "Account_Dimension"."MktDsTx" "Market" , "Account_Dimension"."UnitDsTx" "Unit" , "Account_Dimension"."CurrAbbrDsTx" "Currency" , "Balance_Pool_Dimension"."BalPoolId" "Balance_Pool" , "Account_Dimension"."AcctId" "Account_ID" , "Balance_Pool_Dimension"."BAUClrPerNo" "BAU_Clearing_Period_Number" , "Account_Dimension"."AcctTypeDsTx" "Account_Type" , "T3"."RecnMethodlogyDsTx" "Reconciliation_Methodlogy" , "T3"."RecnTypeDsTx" "Reconciliation_Type" , "Open_Item_Month_End_Fact"."TransId" "Transaction_ID" , sum("Open_Item_Month_End_Fact"."TransAgeMoCt") "Transaction_Age_Month_Count" , sum("Open_Item_Month_End_Fact"."TransAgeClrPerMoCt") "c13" , "Open_Item_Month_End_Fact"."GrpNo" "Group_Number" , "Open_Item_Month_End_Fact"."NewValDt" "Value_Date" , "Open_Item_Month_End_Fact"."JeNo" "Journal_Entry_Number" , "Open_Item_Month_End_Fact"."JeDt" "Journal_Entry_Date" , "Open_Item_Month_End_Fact"."RefNo" "Reference_Number" , "Open_Item_Month_End_Fact"."TransDsTx" "Transaction19" , sum(case when "Open_Item_Month_End_Fact"."DbCrFlagIn" = 'CR' then "Open_Item_Month_End_Fact"."TransAm" * -1 else "Open_Item_Month_End_Fact"."TransAm" end ) "Transaction_Amount" , sum(case when ("Open_Item_Month_End_Fact"."DbCrFlagIn" = 'CR') then ("Open_Item_Month_End_Fact"."TransAm" * -1) else "Open_Item_Month_End_Fact"."TransAm" end * "Corp_Rate_Lookup8"."CSR_Rate") "Amount__in_US__" , max("Corp_Rate_Lookup8"."CSR_Rate") "Corporate_Standard_Direct_Rate" , "Open_Item_Month_End_Fact"."SourceSysNm" "Source_System_Name" , "Open_Item_Month_End_Fact"."TransRemrkTx" "Transaction_Flag_Indicator" , "Reconciler_Dimension"."RecnAnalNm" "Reconciliation_Analyst_Name" , "Reconciler_Dimension"."RecnApproverNm" "Reconciliation_Approver_Name" , "Open_Item_Month_End_Fact"."RootCauseCdTx" "Root_Cause_Code" , "Open_Item_Month_End_Fact"."ActPlanCdTx" "Action_Plan_Code" , "Open_Item_Month_End_Fact"."ResDt" "Resolution_Date" , sum("Open_Item_Month_End_Fact"."TracsCaseId") "Trans_Case_Identifier" , sum("Open_Item_Month_End_Fact"."TransCt") "Transaction_Count" , "Open_Item_Month_End_Fact"."UserRemrkTx" "User_Remarks" , "Open_Item_Month_End_Fact"."RsvRqrIn" "Reserve_Required_Indicator" , "Reconciliation_Month_Dimension"."MoId" "c34" from "Account_Dimension" "Account_Dimension", "Balance_Pool_Dimension" "Balance_Pool_Dimension", "Reconciliation_Methodology_Dimension" "T3", "Open_Item_Month_End_Fact" "Open_Item_Month_End_Fact", "Reconciler_Dimension" "Reconciler_Dimension", "Reconciliation_Month_Dimension" "Reconciliation_Month_Dimension", (select "Account_Dimension_CSR"."AcctSurrogateKeyId" "Account_SK" , "Corporate_Standard_Rate_Fact"."CorpStdRt" "CSR_Rate" from "Reconciliation_Month_Dimension" "T1", "Account_Dimension" "Account_Dimension_CSR", "Corporate_Standard_Rate_Fact" "Corporate_Standard_Rate_Fact" with (FORCESEEK) where "T1"."MoId" = '200904' and "T1"."RecnMoSurrogateKeyId" = "Corporate_Standard_Rate_Fact"."RecnMoSurrogateKeyId" and "Account_Dimension_CSR"."CurrAbbrDsTx" = "Corporate_Standard_Rate_Fact"."FromCurrDsTx" and "Corporate_Standard_Rate_Fact"."ToCurrDsTx" = '000') "Corp_Rate_Lookup8" where "Reconciliation_Month_Dimension"."MoId" = 200904 and "Account_Dimension"."RgnDsTx" = 'EMEA' and "Account_Dimension"."BalPoolSurrogateKeyId" = "Balance_Pool_Dimension"."BalPoolSurrogateKeyId" and "Account_Dimension"."AcctSurrogateKeyId" = "Open_Item_Month_End_Fact"."AcctSurrogateKeyId" and "Reconciliation_Month_Dimension"."RecnMoSurrogateKeyId" = "Open_Item_Month_End_Fact"."RecnMoSurrogateKeyId" and "T3"."RecnMthdSurrogateKeyId" = "Open_Item_Month_End_Fact"."RecnMthdSurrogateKeyId" and "Reconciler_Dimension"."ReconcilerSurrogateKeyId" = "Open_Item_Month_End_Fact"."ReconcilerSurrogateKeyId" and "Corp_Rate_Lookup8"."Account_SK" = "Open_Item_Month_End_Fact"."AcctSurrogateKeyId" group by "Account_Dimension"."RgnDsTx", "Account_Dimension"."MktDsTx", "Account_Dimension"."UnitDsTx", "Account_Dimension"."CurrAbbrDsTx", "Balance_Pool_Dimension"."BalPoolId", "Account_Dimension"."AcctId", "Balance_Pool_Dimension"."BAUClrPerNo", "Account_Dimension"."AcctTypeDsTx", "T3"."RecnMethodlogyDsTx", "T3"."RecnTypeDsTx", "Open_Item_Month_End_Fact"."TransId", "Open_Item_Month_End_Fact"."GrpNo", "Open_Item_Month_End_Fact"."NewValDt", "Open_Item_Month_End_Fact"."JeNo", "Open_Item_Month_End_Fact"."JeDt", "Open_Item_Month_End_Fact"."RefNo", "Open_Item_Month_End_Fact"."TransDsTx", "Open_Item_Month_End_Fact"."SourceSysNm", "Open_Item_Month_End_Fact"."TransRemrkTx", "Reconciler_Dimension"."RecnAnalNm", "Reconciler_Dimension"."RecnApproverNm", "Open_Item_Month_End_Fact"."RootCauseCdTx", "Open_Item_Month_End_Fact"."ActPlanCdTx", "Open_Item_Month_End_Fact"."ResDt", "Open_Item_Month_End_Fact"."UserRemrkTx", "Open_Item_Month_End_Fact"."RsvRqrIn", "Reconciliation_Month_Dimension"."MoId") "Union19" --order by 33 asc , 4 asc |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-04 : 01:43:58
|
A bit more readable...SELECT "Union19"."Unit" "Unit", "Union19"."Currency" "Currency", "Union19"."Balance_Pool" "Balance_Pool", "Union19"."Account_ID" "Account_ID", "Union19"."BAU_Clearing_Period_Number" "BAU_Clearing_Period_Number", "Union19"."Account_Type" "Account_Type", "Union19"."Reconciliation_Methodlogy" "Reconciliation_Methodlogy", "Union19"."Reconciliation_Type" "Reconciliation_Type", "Union19"."Transaction_ID" "Transaction_ID", "Union19"."Transaction_Age_Month_Count" "Transaction_Age_Month_Count", "Union19"."c13" "c11", "Union19"."Group_Number" "Group_Number", "Union19"."Value_Date" "Value_Date", "Union19"."Journal_Entry_Number" "Journal_Entry_Number", "Union19"."Journal_Entry_Date" "Journal_Entry_Date", "Union19"."Reference_Number" "Reference_Number", "Union19"."Transaction19" "Transaction17", "Union19"."Transaction_Amount" "Transaction_Amount", "Union19"."Amount__in_US__" "Amount__in_US__", "Union19"."Corporate_Standard_Direct_Rate" "Corporate_Standard_Direct_Rate", "Union19"."Source_System_Name" "Source_System_Name", "Union19"."Transaction_Flag_Indicator" "Transaction_Flag_Indicator", "Union19"."Reconciliation_Analyst_Name" "Reconciliation_Analyst_Name", "Union19"."Reconciliation_Approver_Name" "Reconciliation_Approver_Name", "Union19"."Root_Cause_Code" "Root_Cause_Code", "Union19"."Action_Plan_Code" "Action_Plan_Code", "Union19"."Resolution_Date" "Resolution_Date", "Union19"."Trans_Case_Identifier" "Trans_Case_Identifier", "Union19"."Transaction_Count" "Transaction_Count", "Union19"."User_Remarks" "User_Remarks", "Union19"."Reserve_Required_Indicator" "Reserve_Required_Indicator", "Union19"."c34" "c32", "Union19"."Market" "Market" FROM (SELECT "Account_Dimension"."RgnDsTx" "Region", "Account_Dimension"."MktDsTx" "Market", "Account_Dimension"."UnitDsTx" "Unit", "Account_Dimension"."CurrAbbrDsTx" "Currency", "Balance_Pool_Dimension"."BalPoolId" "Balance_Pool", "Account_Dimension"."AcctId" "Account_ID", "Balance_Pool_Dimension"."BAUClrPerNo" "BAU_Clearing_Period_Number", "Account_Dimension"."AcctTypeDsTx" "Account_Type", "T3"."RecnMethodlogyDsTx" "Reconciliation_Methodlogy", "T3"."RecnTypeDsTx" "Reconciliation_Type", "Open_Item_Fact"."TransId" "Transaction_ID", Sum("Open_Item_Fact"."TransAgeMoCt") "Transaction_Age_Month_Count", Sum("Open_Item_Fact"."TransAgeClrPerMoCt") "c13", "Open_Item_Fact"."GrpNo" "Group_Number", "Open_Item_Fact"."NewValDt" "Value_Date", "Open_Item_Fact"."JeNo" "Journal_Entry_Number", "Open_Item_Fact"."JeDt" "Journal_Entry_Date", "Open_Item_Fact"."RefNo" "Reference_Number", "Open_Item_Fact"."TransDsTx" "Transaction19", Sum(CASE WHEN "Open_Item_Fact"."DbCrFlagIn" = 'CR' THEN "Open_Item_Fact"."TransAm" * -1 ELSE "Open_Item_Fact"."TransAm" END) "Transaction_Amount", Sum(CASE WHEN ("Open_Item_Fact"."DbCrFlagIn" = 'CR') THEN ("Open_Item_Fact"."TransAm" * -1) ELSE "Open_Item_Fact"."TransAm" END * "Corp_Rate_Lookup"."CSR_Rate") "Amount__in_US__", Max("Corp_Rate_Lookup"."CSR_Rate") "Corporate_Standard_Direct_Rate", "Open_Item_Fact"."SourceSysNm" "Source_System_Name", "Open_Item_Fact"."TransRemrkTx" "Transaction_Flag_Indicator", "Reconciler_Dimension"."RecnAnalNm" "Reconciliation_Analyst_Name", "Reconciler_Dimension"."RecnApproverNm" "Reconciliation_Approver_Name", "Open_Item_Fact"."RootCauseCdTx" "Root_Cause_Code", "Open_Item_Fact"."ActPlanCdTx" "Action_Plan_Code", "Open_Item_Fact"."ResDt" "Resolution_Date", Sum("Open_Item_Fact"."TracsCaseId") "Trans_Case_Identifier", Sum("Open_Item_Fact"."TransCt") "Transaction_Count", "Open_Item_Fact"."UserRemrkTx" "User_Remarks", "Open_Item_Fact"."RsvRqrIn" "Reserve_Required_Indicator", "Balance_Pool_Dimension"."CurRecnMoNo" "c34" FROM "Account_Dimension" "Account_Dimension" WITH (index (IX_Account_Dimension_3 ) ,nolock), "Balance_Pool_Dimension" "Balance_Pool_Dimension", "Reconciliation_Methodology_Dimension" "T3", "Open_Item_Fact" "Open_Item_Fact" WITH (index (PK16 )), "Reconciler_Dimension", (SELECT "Account_Dimension_CSR"."AcctSurrogateKeyId" "Account_SK", "Corporate_Standard_Rate_Fact"."CorpStdRt" "CSR_Rate" FROM "Reconciliation_Month_Dimension" "T1", "Account_Dimension" "Account_Dimension_CSR", "Corporate_Standard_Rate_Fact" "Corporate_Standard_Rate_Fact" WITH (NOLOCK ,index (PK18 )) WHERE "T1"."MoId" = '200904' AND "T1"."RecnMoSurrogateKeyId" = "Corporate_Standard_Rate_Fact"."RecnMoSurrogateKeyId" AND "Account_Dimension_CSR"."CurrAbbrDsTx" = "Corporate_Standard_Rate_Fact"."FromCurrDsTx" AND "Corporate_Standard_Rate_Fact"."ToCurrDsTx" = '000') "Corp_Rate_Lookup" WHERE "Balance_Pool_Dimension"."CurRecnMoNo" = 200904 AND "Account_Dimension"."RgnDsTx" = 'EMEA' AND "Account_Dimension"."BalPoolSurrogateKeyId" = "Balance_Pool_Dimension"."BalPoolSurrogateKeyId" AND "Account_Dimension"."AcctSurrogateKeyId" = "Open_Item_Fact"."AcctSurrogateKeyId" AND "T3"."RecnMthdSurrogateKeyId" = "Open_Item_Fact"."RecnMthdSurrogateKeyId" AND "Reconciler_Dimension"."ReconcilerSurrogateKeyId" = "Open_Item_Fact"."ReconcilerSurrogateKeyId" AND "Corp_Rate_Lookup"."Account_SK" = "Open_Item_Fact"."AcctSurrogateKeyId" GROUP BY "Account_Dimension"."RgnDsTx", "Account_Dimension"."MktDsTx", "Account_Dimension"."UnitDsTx", "Account_Dimension"."CurrAbbrDsTx", "Balance_Pool_Dimension"."BalPoolId", "Account_Dimension"."AcctId", "Balance_Pool_Dimension"."BAUClrPerNo", "Account_Dimension"."AcctTypeDsTx", "T3"."RecnMethodlogyDsTx", "T3"."RecnTypeDsTx", "Open_Item_Fact"."TransId", "Open_Item_Fact"."GrpNo", "Open_Item_Fact"."NewValDt", "Open_Item_Fact"."JeNo", "Open_Item_Fact"."JeDt", "Open_Item_Fact"."RefNo", "Open_Item_Fact"."TransDsTx", "Open_Item_Fact"."SourceSysNm", "Open_Item_Fact"."TransRemrkTx", "Reconciler_Dimension"."RecnAnalNm", "Reconciler_Dimension"."RecnApproverNm", "Open_Item_Fact"."RootCauseCdTx", "Open_Item_Fact"."ActPlanCdTx", "Open_Item_Fact"."ResDt", "Open_Item_Fact"."UserRemrkTx", "Open_Item_Fact"."RsvRqrIn", "Balance_Pool_Dimension"."CurRecnMoNo" UNION SELECT "Account_Dimension"."RgnDsTx" "Region", "Account_Dimension"."MktDsTx" "Market", "Account_Dimension"."UnitDsTx" "Unit", "Account_Dimension"."CurrAbbrDsTx" "Currency", "Balance_Pool_Dimension"."BalPoolId" "Balance_Pool", "Account_Dimension"."AcctId" "Account_ID", "Balance_Pool_Dimension"."BAUClrPerNo" "BAU_Clearing_Period_Number", "Account_Dimension"."AcctTypeDsTx" "Account_Type", "T3"."RecnMethodlogyDsTx" "Reconciliation_Methodlogy", "T3"."RecnTypeDsTx" "Reconciliation_Type", "Open_Item_Month_End_Fact"."TransId" "Transaction_ID", Sum("Open_Item_Month_End_Fact"."TransAgeMoCt") "Transaction_Age_Month_Count", Sum("Open_Item_Month_End_Fact"."TransAgeClrPerMoCt") "c13", "Open_Item_Month_End_Fact"."GrpNo" "Group_Number", "Open_Item_Month_End_Fact"."NewValDt" "Value_Date", "Open_Item_Month_End_Fact"."JeNo" "Journal_Entry_Number", "Open_Item_Month_End_Fact"."JeDt" "Journal_Entry_Date", "Open_Item_Month_End_Fact"."RefNo" "Reference_Number", "Open_Item_Month_End_Fact"."TransDsTx" "Transaction19", Sum(CASE WHEN "Open_Item_Month_End_Fact"."DbCrFlagIn" = 'CR' THEN "Open_Item_Month_End_Fact"."TransAm" * -1 ELSE "Open_Item_Month_End_Fact"."TransAm" END) "Transaction_Amount", Sum(CASE WHEN ("Open_Item_Month_End_Fact"."DbCrFlagIn" = 'CR') THEN ("Open_Item_Month_End_Fact"."TransAm" * -1) ELSE "Open_Item_Month_End_Fact"."TransAm" END * "Corp_Rate_Lookup8"."CSR_Rate") "Amount__in_US__", Max("Corp_Rate_Lookup8"."CSR_Rate") "Corporate_Standard_Direct_Rate", "Open_Item_Month_End_Fact"."SourceSysNm" "Source_System_Name", "Open_Item_Month_End_Fact"."TransRemrkTx" "Transaction_Flag_Indicator", "Reconciler_Dimension"."RecnAnalNm" "Reconciliation_Analyst_Name", "Reconciler_Dimension"."RecnApproverNm" "Reconciliation_Approver_Name", "Open_Item_Month_End_Fact"."RootCauseCdTx" "Root_Cause_Code", "Open_Item_Month_End_Fact"."ActPlanCdTx" "Action_Plan_Code", "Open_Item_Month_End_Fact"."ResDt" "Resolution_Date", Sum("Open_Item_Month_End_Fact"."TracsCaseId") "Trans_Case_Identifier", Sum("Open_Item_Month_End_Fact"."TransCt") "Transaction_Count", "Open_Item_Month_End_Fact"."UserRemrkTx" "User_Remarks", "Open_Item_Month_End_Fact"."RsvRqrIn" "Reserve_Required_Indicator", "Reconciliation_Month_Dimension"."MoId" "c34" FROM "Account_Dimension" "Account_Dimension", "Balance_Pool_Dimension" "Balance_Pool_Dimension", "Reconciliation_Methodology_Dimension" "T3", "Open_Item_Month_End_Fact" "Open_Item_Month_End_Fact", "Reconciler_Dimension" "Reconciler_Dimension", "Reconciliation_Month_Dimension" "Reconciliation_Month_Dimension", (SELECT "Account_Dimension_CSR"."AcctSurrogateKeyId" "Account_SK", "Corporate_Standard_Rate_Fact"."CorpStdRt" "CSR_Rate" FROM "Reconciliation_Month_Dimension" "T1", "Account_Dimension" "Account_Dimension_CSR", "Corporate_Standard_Rate_Fact" "Corporate_Standard_Rate_Fact" WITH (FORCESEEK) WHERE "T1"."MoId" = '200904' AND "T1"."RecnMoSurrogateKeyId" = "Corporate_Standard_Rate_Fact"."RecnMoSurrogateKeyId" AND "Account_Dimension_CSR"."CurrAbbrDsTx" = "Corporate_Standard_Rate_Fact"."FromCurrDsTx" AND "Corporate_Standard_Rate_Fact"."ToCurrDsTx" = '000') "Corp_Rate_Lookup8" WHERE "Reconciliation_Month_Dimension"."MoId" = 200904 AND "Account_Dimension"."RgnDsTx" = 'EMEA' AND "Account_Dimension"."BalPoolSurrogateKeyId" = "Balance_Pool_Dimension"."BalPoolSurrogateKeyId" AND "Account_Dimension"."AcctSurrogateKeyId" = "Open_Item_Month_End_Fact"."AcctSurrogateKeyId" AND "Reconciliation_Month_Dimension"."RecnMoSurrogateKeyId" = "Open_Item_Month_End_Fact"."RecnMoSurrogateKeyId" AND "T3"."RecnMthdSurrogateKeyId" = "Open_Item_Month_End_Fact"."RecnMthdSurrogateKeyId" AND "Reconciler_Dimension"."ReconcilerSurrogateKeyId" = "Open_Item_Month_End_Fact"."ReconcilerSurrogateKeyId" AND "Corp_Rate_Lookup8"."Account_SK" = "Open_Item_Month_End_Fact"."AcctSurrogateKeyId" GROUP BY "Account_Dimension"."RgnDsTx", "Account_Dimension"."MktDsTx", "Account_Dimension"."UnitDsTx", "Account_Dimension"."CurrAbbrDsTx", "Balance_Pool_Dimension"."BalPoolId", "Account_Dimension"."AcctId", "Balance_Pool_Dimension"."BAUClrPerNo", "Account_Dimension"."AcctTypeDsTx", "T3"."RecnMethodlogyDsTx", "T3"."RecnTypeDsTx", "Open_Item_Month_End_Fact"."TransId", "Open_Item_Month_End_Fact"."GrpNo", "Open_Item_Month_End_Fact"."NewValDt", "Open_Item_Month_End_Fact"."JeNo", "Open_Item_Month_End_Fact"."JeDt", "Open_Item_Month_End_Fact"."RefNo", "Open_Item_Month_End_Fact"."TransDsTx", "Open_Item_Month_End_Fact"."SourceSysNm", "Open_Item_Month_End_Fact"."TransRemrkTx", "Reconciler_Dimension"."RecnAnalNm", "Reconciler_Dimension"."RecnApproverNm", "Open_Item_Month_End_Fact"."RootCauseCdTx", "Open_Item_Month_End_Fact"."ActPlanCdTx", "Open_Item_Month_End_Fact"."ResDt", "Open_Item_Month_End_Fact"."UserRemrkTx", "Open_Item_Month_End_Fact"."RsvRqrIn", "Reconciliation_Month_Dimension"."MoId") "Union19"
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
|
|
Need Help on query...