SQL Server Performance

Nested Case Statement

Discussion in 'SQL Server 2008 General DBA Questions' started by sid_eab, Mar 21, 2011.

  1. sid_eab New Member

    Hi everyone,
    I am having some difficulty with the code for my database and would greatly appreciate it if you'll could help me out. I am trying to use a nested case statement i.e. referring to a computed column in the code. What I require is something along the lines of this in Excel:
    sumif("range of dates", ">" "today's date", "amount we receive") - Kindly note I am just using the fields I require.
    In other words, what I require is to be able to state the outstanding balance under any future month for a certain deal, which I will group by certain parameters. So for instance if I have a loan that goes on till Mar 2013, I want to be able to sum all the balances from Apr 2012 till Mar 2013.
    Below is the code I am using:
    SELECT DISTINCT
    AL1."Deal_type_OMDLP",
    case when AL3."Revolving_commitment_LCYRV"= N'Y' then AL3."Final_repayment_date_LCDTER_Converted" when AL7."Sundry_analysis_code_GFSAC" = N'NP' then (AL5."Maturity_date_OTMDT_Converted"+10000) else AL5."Maturity_date_OTMDT_Converted" end as "Mvmt",
    AL7."Closed_GFCUC",
    AL7."Responsibility_code_GFACO",
    AL7."Customer_s_basic_number_GFCPNC",
    AL1."Currency_mnemonic_OMCCY",
    AL1."Amount_we_receive_OMNWR",
    round((AL6."Spread_rate_V5SPR"*100),0) as "Margin"
    FROM "Brio_EOD"."dbo"."Movements_OMPF" AL1, "Brio_EOD"."dbo"."Drawdown_details_LDPF" AL2, "Brio_EOD"."dbo"."Commitment_details_LCPF" AL3, "Brio_EOD"."dbo"."Customer_information_GFPF" AL7, "Brio_EOD"."dbo"."Basic_deal_details_OSPF" AL4 LEFT OUTER JOIN "Brio_EOD"."dbo"."Term_deal_details_OTPF" AL5 ON (AL5."Branch_mnemonic_OTBRNM"=AL4."Deal_branch_mnemonic_OSBRNM" AND AL5."Deal_type_OTDLP"=AL4."Deal_type_OSDLP" AND AL5."Deal_reference_OTDLR"=AL4."Deal_reference_OSDLR") LEFT OUTER JOIN "Brio_EOD"."dbo"."Interest_status_deal_V5PF" AL6 ON (AL6."Deal_branch_mnemonic_V5BRNM"=AL4."Deal_branch_mnemonic_OSBRNM" AND AL6."Deal_type_V5DLP"=AL4."Deal_type_OSDLP" AND AL6."Deal_reference_V5DLR"=AL4."Deal_reference_OSDLR")
    WHERE (AL1."Deal_type_OMDLP"=AL2."Deal_type_LDDLP" AND AL1."Deal_reference_OMDLR"=AL2."Deal_reference_LDDLR" AND AL2."Commitment_reference_LDCMR"=AL3."Commitment_reference_LCCMR" AND AL4."Deal_type_OSDLP"=AL1."Deal_type_OMDLP" AND AL4."Deal_reference_OSDLR"=AL1."Deal_reference_OMDLR" AND AL4."Deal_branch_mnemonic_OSBRNM"=AL1."Branch_mnemonic_OMBRNM" AND AL7."Customer_mnemonic_GFCUS"=AL4."Customer_mnemonic_OSCUS" AND AL7."Customer_location_GFCLC"=AL4."Customer_location_OSCLC")  AND ((AL1."Movement_date_OMDTE_converted" >= (Select GetDate()) AND AL1."Deal_type_OMDLP" IN (N'AK', N'AX', N'BQ', N'CD1', N'CD6', N'EC1', N'EC2', N'KL', N'LBE', N'LBG', N'LBI', N'LBK', N'LBM', N'LBX', N'LC1', N'LC3', N'LK', N'LP', N'LV', N'QGR') AND AL3."Equation_Date" = (select max (Equation_Date) from Commitment_Details_Lcpf) AND AL4."Equation_Date" = (select max (Equation_Date) from Basic_Deal_Details_Ospf) AND AL5."Equation_Date" = (select max (Equation_Date) from Term_Deal_Details_Otpf) AND AL6."Equation_Date" = (select max (Equation_Date) from Interest_Status_Deal_V5pf) AND AL1."Movement_type_I_Interest_P_Principal_OMMVT"=N'P' AND (NOT AL4."Status_OSCANR"=N'C') AND AL1."Amount_we_receive_OMNWR">0))
    What I require is to reuse the 2nd "Select" where I use the case statement defined as "Mvmt" i.e. I need another case statement that says:
    case when "Mvmt" > "today's date" then sum AL1."Amount_we_receive_OMNWR" else null end
    So basically I require a sum of AL1."Amount_we_receive_OMNWR" if "Mvmt" is greater than today. I can define another column as today if required and will be grouping the various factors, but would like your help in cracking this case statement. I am relatively new to this and it is really important for a project I am doing.
    Thank you for your help in advance.

Share This Page