1

Is it possible to query a field, an array of integers, and return an array of Dates based off the current_date - an int value.

My data structure is something like

_Category_
name:string
send_reminders:array[integer]

_Appointment_
category_id:integer
title:string
event_date:date

_Customer_
appointemtn_id:integer
name:string
contact:boolean

I want to query the customers who have an appointment reminder on that date. I thought I could accomplish this by

  1. Use Category:send_reminders to generate an array of Dates ([1,2,3] produces [yesterday, the day before, two days before])
  2. Then use a condition where Appointment date is in that array.
  3. Select the Customers in that Appointment where contact is true.
Improve this question
1
  • 1
    Can you share some sample data and the result you'd like to get for it? It would make the question much clearer, and would help us help you. Commented Aug 16, 2017 at 19:20

1 Answer 1

Reset to default
2 
select *
from
    category cat
    inner join
    appointment ap on cat.category_id = ap.category_id
    inner join
    customer ct on ct.appointment_id = ap.appointment_id
where
    ap.event_date in (
        select current_date - i
        from unnest(cat.send_reminders) u (i)
    )
    and ct.contact
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.