Hi All,
I have an existing reporting query that I'm adding data to that is now going to be pulling from an additional table. This table is called "collection_assignments" and has_many rows that have a relation to my "charges" table based on the charge_id.
collection_assignments
-----------------------
id
charge_id
company_id
total_due
charges
---------
id
account_number
the charges table has 100 columns so I won't list them all.
There are many more tables involved in this query so there are a lot of inner joins, left joins etc.. but I won't post the whole thing here because it's quite large.
Essentially in our report, we show 1 row of data for each charge and now with adding this collection_assignments table I need to pull data from multiple rows but still display it in a single row.
So if I have these rows in the collection_assignments table:
| id | charge_id | company_id | listed_on |
|----|-----------|------------|---------------------|
| 1 | 1301717 | 6 | 2021-03-24 17:23:23 |
| 3 | 1301717 | 11 | 2021-04-21 19:40:23 |
| 4 | 1301717 | 12 | 2021-05-10 19:40:23 |
| 5 | 1301717 | 16 | 2021-05-21 19:40:23 |
I need to display as the following(I'm including one column from the charges table to show how it would look):
Account # Date Submitted Placement # First Placement First Placement Location
123456 05-21-2021 4 03-24-2021 My Company
Here is the description of how this data would be represented:
1. Account # - this is just our account # assigned to this charge stored in the charges table. We already have this in place
2. Date Submitted - This is the most recent date in the listed_on column from those 4 rows
3. Placement - this is just a count of the number of rows we have in the collection_assignments table for that particular charge_id
4. First Placement - The first listed_on date in the collection_assignments table for that charge_id
5. Placement Location - The company that the charge resided with for that very first placement date.
Because we have 4 rows in that table we would also have 2nd, 3rd and 4th Placement Dates and Locations.
The way I'm achieving this currently and does seem to work OK, but I feel like it's not efficient. I've done some query profiling and the overall time to run this query based on 1300 rows went from 154ms to around 190ms so about 0.15 seconds to 0.19 seconds which isn't a ton and to be honest speed isn't a huge issue since these reports are already a little slow when we display them in our app. But, I want to make sure I'm not introducing a major bottleneck or writing this inefficiently. So if anyone can recommend a better solution, I would love to try it.
I've had to resort to doing sub selects to get this to work and this is where I'm questioning what I'm doing
SELECT
charges.account_number,
(
select
max(collection_assignments.listed_on)
from
collection_assignments
where
charge_id = charges.id
) as collection_assignment_date_submitted,
(
select
count(collection_assignments.id)
from
collection_assignments
where
charge_id = charges.id
) as placement_number,
(
select
min(collection_assignments.listed_on)
from
collection_assignments
where
charge_id = charges.id
) as first_placement,
(
select
companies.name
from
companies
where
companies.id = (
select
collection_company_assignments.collection_company_id
from
collection_company_assignments
where
charge_id = charges.id
AND listed_on = (
select
min(collection_company_assignments.listed_on)
from
collection_company_assignments
where
charge_id = charges.id
)
)
) as first_placement_location,
(
select
min(a.listed_on)
from
collection_company_assignments a
where
a.charge_id = charges.id
AND a.listed_on > (
select
min(i.listed_on) as listed_on
from
collection_company_assignments i
where
i.charge_id = a.charge_id
)
) as second_placement,
(
select
companies.name
from
companies
where
companies.id = (
select
collection_company_assignments.collection_company_id
from
collection_company_assignments
where
listed_on = (
select
min(a.listed_on)
from
collection_company_assignments a
where
a.charge_id = charges.id
AND a.listed_on > (
select
min(i.listed_on) as listed_on
from
collection_company_assignments i
where
i.charge_id = a.charge_id
)
)
)
) as second_placement_location
FROM
charges
LEFT JOIN some_other_table
I've only worked up to the second placement data but the 3rd and 4th would be very similar, I'm thinking.
The joins continue on and there are multiple where conditions but hopefully this will get my point across.
Any help would be greatly appreciated.
Thank you,
Ben
Edited 1 time(s). Last edit at 06/01/2021 01:51PM by Peter Brawley.