June 28, 2015 at 2:59 pm
SELECT
SUM(((CASE WHEN
o.date>= a.activity_date, other filter condition, other filter condition
THEN
(select d.balance from drawtable d where d.date < a.activity_date order by d.date desc limit 1) - ( select d.balance from drawtable d where d.date = interval 'current date'
else end ))
from
emailtable a
LEFT JOIN opportunity o
left join drawtable d
etc
--Assuming I'll have a few other case whens as part of the main select and some where conditions, how do I properly write this (postgres environment)?
June 29, 2015 at 11:15 am
dandenise316 (6/28/2015)
SELECTSUM(((CASE WHEN
o.date>= a.activity_date, other filter condition, other filter condition
THEN
(select d.balance from drawtable d where d.date < a.activity_date order by d.date desc limit 1) - ( select d.balance from drawtable d where d.date = interval 'current date'
else end ))
from
emailtable a
LEFT JOIN opportunity o
left join drawtable d
etc
--Assuming I'll have a few other case whens as part of the main select and some where conditions, how do I properly write this (postgres environment)?
This is not a POSTGRES forum, and the folks here aren't necessarily going to be able to help with that. If it were SQL Server, here's how it would look:
SELECT SUM(
CASE
WHEN o.date>= a.activity_date AND other filter condition AND other filter condition
THEN (
SELECT TOP (1) d.balance
FROM drawtable AS d
WHERE d.date < a.activity_date
ORDER BY d.date DESC
) - (
SELECT d.balance
FROM drawtable AS d
WHERE d.date = DATEADD(interval, GETDATE())
)
ELSE someothervalue
END
) AS FIELD_NAME
FROM emailtable AS a
LEFT JOIN opportunity AS o
ON a.somejoinfield = o.somejoinfield
LEFT JOIN drawtable AS d
ON tablealias.somejoinfield = d.somejoinfield
--query continues here
Whether or not POSTGRE supports the DATEADD function or GETDATE() is an unknown for me. You'll have to do that research somewhere other than here...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply