PostgreSQL: need to extend a complex existing query to include a new data column in the result
Budget: €30 – €250 EUR
Hello,
Gonna pay 100 for this query task
I need a PostgreSQL expert to help me with a query I need to extend to include a new column.
I created a DB fiddle where I need to get the help
The issue I also tried to ask it here but I need urgent help with that
https://stackoverflow.com/questions/71674258/postgresql-how-to-add-an-extra-column-to-resulting-data-table-with-multiple-join?noredirect=1#comment126673298_71674258
The fiddle is here
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=8e200ffc2dcf95e989f7f02c88354daf
The query I need help with is the first one.
As you see that query generates a resulting table with some data on it.
What I need to add is a new column result called 'first_contact'
The new column first_contact has to contain the minimum timestamp which is the result between the column statusFrom and statusTo with a left join to the table activities on the column created_at.
The goal is to show what was the first contact timestamp of a candidate. That information is stored in activities and the types we are considering are: PHONE, SMS, EMAIL
The rest of the table has to be the same we just need to add a new column as explained above.
The conditions for the new column are as follow to have a better understanding:
1. first_contact is the minimum timestamp from the activities table
2. That timestamp needs to be between the statusFrom timestamp and statusTo timestamp range from statuses table
3. If we have no contact from activities as a result in the range the column shows null
4. if the statusTo is null then also first_contact is null
I hope that was clear but can be discussed during the chats on the requirements
Thank you
Gonna pay 100 for this query task
I need a PostgreSQL expert to help me with a query I need to extend to include a new column.
I created a DB fiddle where I need to get the help
The issue I also tried to ask it here but I need urgent help with that
https://stackoverflow.com/questions/71674258/postgresql-how-to-add-an-extra-column-to-resulting-data-table-with-multiple-join?noredirect=1#comment126673298_71674258
The fiddle is here
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=8e200ffc2dcf95e989f7f02c88354daf
The query I need help with is the first one.
As you see that query generates a resulting table with some data on it.
What I need to add is a new column result called 'first_contact'
The new column first_contact has to contain the minimum timestamp which is the result between the column statusFrom and statusTo with a left join to the table activities on the column created_at.
The goal is to show what was the first contact timestamp of a candidate. That information is stored in activities and the types we are considering are: PHONE, SMS, EMAIL
The rest of the table has to be the same we just need to add a new column as explained above.
The conditions for the new column are as follow to have a better understanding:
1. first_contact is the minimum timestamp from the activities table
2. That timestamp needs to be between the statusFrom timestamp and statusTo timestamp range from statuses table
3. If we have no contact from activities as a result in the range the column shows null
4. if the statusTo is null then also first_contact is null
I hope that was clear but can be discussed during the chats on the requirements
Thank you