Problem:

There is a table with draw amounts of an account (last 2 tables of the join), that has a field called the asof field. This s.asof field is a date field. I want to pull the closest date of the s.asof that comes before the date of the a.activity_date field and subtract the s.balanceonthatday field from the s.asof field that is the closest date AFTER THE ACTIVITY date field.



SELECT



something like tihs:

(CASE WHEN
o.closedate >= a.activity_date, other filter condition, other filter condition

THEN
(max(case when(coalesce(s.asoffield, s2.asoffield) that comes before a.activitydate ) then s.balanceonthatday else null end) - min(case when(coalesce(s.asoffield, s2.asoffield) that is closest date after a.activity date) then s.balancethatday))
So I'm trying to figure out how to do this properly. Any thoughts? I'm thinking the interval function might be helpful here, but I'm not sure how to apply it. The other thing is i want a bunch of other filter conditions in the case when too ..so I'm a bit lost.




FROM
reporting.os_marketo_activities a
LEFT JOIN reporting.os_marketo_leads z ON z.id = a.lead_id
LEFT JOIN reporting.os_marketo_activity_mapping b ON b.activity_type_id = a.activity_type_id
LEFT JOIN reporting.os_marketo_activity_attributes d ON d.id = a.id
LEFT JOIN sf_contact c ON c.id = z.sfdc_contact_id
LEFT JOIN sf_lead l ON l.id = z.sfdc_lead_id
LEFT JOIN sf_opportunity o ON o.accountid = c.accountid
LEFT JOIN sf_opportunity o2 ON o2.id = l.convertedopportunityid
LEFT JOIN reporting.lsp_loc_status_history S ON s.loan_id = o.loan_id__c
LEFT JOIN reporting.lsp_loc_status_history s2 ON S2.loan_id = O2.LOAN_ID__C



WHERE

a.activity_date >= '2015-05-01'
AND a.primary_attribute_value iLIKE '%loc rate decrease june 2015%'


GROUP BY 1,2

This post was edited by noflexzone on Jun 26 2015 05:59pm

anyone have any ideas? I'm trying to figure out the best way to write that first case when statement.

i stopped reading when it's clear you're not simplifying your examples.

without going into much details, i suggest you learn how to use subselects or unions.

eg use a single select to grab each piece of data you want, then union all the cases together.
or select the data difference as a virtual column integer, then do a select on top of it for your case statement to make it easier to follow.

Starting with the basics...will this case select the balance on the greatest date less than the activity date

max(case when(coalesce(s.asoffield, s2.asoffield) < a.activitydate ) then s.balanceonthatday else null end)


Is there harm with putting case statements within a case?

This post was edited by noflexzone on Jun 28 2015 01:19pm

you can nest case statements if you want.

to get the balance on the greatest date before activity date:

select balance from table where date < activity_date order by date desc limit 1

you would need to nest it in oracle, but i think postgres handles limit properly with an order by.

Syntax wise would this work?

SELECT

SUM(((CASE WHEN
o.closedate >= a.activity_date, other filter condition, other filter condition
THEN
(select balance from table where date < activity_date order by date desc limit 1) - ( select balance from table where date > activity_date order by date asc limit 1)

else end ))


from
table1
table2
etc


If that runs that way, that's brilliant.

This post was edited by noflexzone on Jun 28 2015 02:48pm

this is a question you shouldn't ask here. you've got the postgres. test it for yourself.