3

Im trying to filter results by a specific date but when I use the below code I only get results where CloseDate matches current date. It doesnt include the results from ResolvedDate when CloseDate is Null. I tried with :

(CASE WHEN (Closedate IS Null) then ResolvedDate, ELSE CloseDate END) AS FinalDate

but then it states:

 "column FinalDate does not exist"

Any other way I can do this?

Here is the code thus far. Appreciate your help.

SELECT
id,
(CASE WHEN (Closedate IS Null) then ResolvedDate, ELSE CloseDate END),
FROM cases
WHERE (EXTRACT (month FROM Closedate) = EXTRACT(month FROM current_date))
AND  ( EXTRACT(day from Closedate) = EXTRACT(day FROM current_date))
Houari
  • 5,326
  • 3
  • 31
  • 54
exclusivebiz
  • 35
  • 1
  • 4
  • With you where clause as it is CloseDate can never be null anyway. However, you could just use `COALESCE(CloseDate, ResolvedDate)`. It would also be a more sargable query if you did something like `CloseDate >= current_date AND CloseDate < current_date + interval `1 day'` – GarethD Mar 10 '14 at 22:29
  • Remove comma after ResolveDate..unless postgres is weird like that. – Mihai Mar 10 '14 at 22:31
  • A table definition would be in order with such a question. We would need exact data types. – Erwin Brandstetter Mar 10 '14 at 22:31
  • Hi, my mistake on the extra comma, it wasnt supposed to be there. – exclusivebiz Mar 10 '14 at 22:32
  • You cannot reference column aliases in the where clause, you would need to repeat the expression, or put the expression in a subquery and then apply the where clause. – GarethD Mar 10 '14 at 22:33
  • Exactly, the results dont show any Null values after the Where clause as it looks for some sort of date, so Im missing results as there are no values and it should show ResolveDate at that point. I will try the Coalesce – exclusivebiz Mar 10 '14 at 22:34

2 Answers2

2

Assuming you want to match the year as well:

SELECT id, COALESCE(Closedate, ResolvedDate) AS cdate
FROM   cases
WHERE  date_trunc('day', COALESCE(Closedate, ResolvedDate))
     = date_trunc('day', now())

Per documentation: COALESCE, date_trunc()

If you want to ignore the year:

WHERE  to_char(COALESCE(Closedate, ResolvedDate), 'MMDD')
     = to_char(now(), 'MMDD')

A bit more on that:
How do you do date math that ignores the year?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks but I got a ERROR: function date_trunc(unknown, timestamp without time zone, timestamp without time zone) does not exist – exclusivebiz Mar 10 '14 at 22:44
  • @exclusivebiz: That was probably the first draft of my answer which had an error. You must have missed the update, it works fine now. – Erwin Brandstetter Mar 10 '14 at 23:36
1

You should use COALESCE function http://www.postgresql.org/docs/current/static/functions-conditional.html

Try this:

SELECT
  id,
  COALESCE(CloseDate, ResolvedDate) AS FinalDate
FROM
  cases
WHERE
  (EXTRACT (month FROM COALESCE(CloseDate, ResolvedDate)) = EXTRACT(month FROM current_date)) AND
  (EXTRACT (day from COALESCE(CloseDate, ResolvedDate)) = EXTRACT(day FROM current_date))

That should do it...

CrazyMenConnected
  • 682
  • 5
  • 21