Sum and Case Statement Help

[#1]

Hi,

I have the following code but I cant get the logic to work.

The variables names are correct and I think the code I have produce should show what im trying to achieve. If anyone can interpret that I would be grateful. I thin the first 2 are correct and maybe the 4th one but not 100%

case when Date_Term_Prod = Key_hedge then Conversion_Rate_hedge else 0 end as Alco_pc,

Case when Product_Term_comps = Term_hedge_2 then Prepayment_rate_hedge_2 else 0 end as prepayment,

case when alco_pc = 0 then sum(Pipeline_ytd, Pipeline_ytd*alco_pc) end as pipeline_x_alco_pc,

sum(case when pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD,

case when Product_end_date = 0 then sum(Weekly_movement_Allocs * prepayment) else sum(Weekly_movement_Allocs * Alco_pc * prepayment) as weekly_movement

[PGStats]

Every expression seems syntactically correct, except the fourth one which contains an incomplete CASE expression.

Are you aware that there are two kinds of SUM functions in your query? SUM(x, y) simply returns x + y, whereas SUM(x*y), ...  GROUP BY a returns the sum of x*y over every value of a. The first one is a SAS datastep function and the second one is a SQL aggregate function.

PG

[#3]

No I wasn't aware of that, thank you.

[Reeza]

There's no way to say what's 'correct' without having sample input/output data. They may syntactically correct, but be logically incorrect.

This appears incorrect to me, you'd at least need an END.

sum(case when pipeline_x_alco_pc + Completions_ytd * prepayment) as LTD,