I could really use help with a SQL query 1

[JAFrank]

Thanks for any help, I think I am thinking myself into a corner...

In my database I have (among others) 2 tables. One is a list of people who owe money (debtors) and the other is a list of individual debts. A debtor can have one or more debts. Debts are assigned a 3 digit status code.

What I need to output is a list which contains the debtor ID number, the debtor name, and the sum of his debts which have a status code less than 400 - or put another way, I need debts whose status is greater than or equal to 400 to be excluded from the sum.

Complicating this, I also need to be able filter the results by an arbitrary balance total.

So, in English, something like this:

select the debtor ID, debtor name, and a sum of debt balances where debt status is less than 400 and the sum of the balances for those debts is greater than $300.

What is really hanging me up is that last part. It seems like I need to do a subquery to get the value to compare, but I just can't wrap my mind around what it should look like.

Thanks again,

JAFrank

 

[SkipVought]

hi,

select [debtor ID], [debtor name], sum([debt balance])
from [YourTable]
where [debt status] < 400
group by [debtor ID], [debtor name]
having sum([debt balance]) > 300

note, if [debt status] is CHAR then

where [debt status] < '400'

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JAFrank]

Thanks,

Does it make a difference that the debtor id and name are coming from a different table than the debts? Would I just need to add a join?

select [debtor ID], [debtor name], sum([debt balance])
from [dbtr]
join
[debt]
on
[dbtr.debtor ID] = [debt.primary debtor ID]
where [debt status] < 400
group by [debtor ID], [debtor name]
having sum([debt balance]) > 300

I appreciate the help!

JAFrank

 

[SkipVought]

select a.[debtor ID], a.[debtor name], sum(a.[debt balance])
from dbtr a, inner join debt b
on
A.[debtor ID] = b.[primary debtor ID]
where [debt status] < 400
group by [debtor ID], [debtor name]
having sum([debt balance]) > 300

And although I did not finish the alias table prefixes on each field (not knowing) that's your job.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JAFrank]

This is great! and I appreciate your help.

Sadly, it turns out that I was trying to fix the wrong problem - and in an effort to simplify the question, I left out information (which I thought wasn't important) which is related to the problem.

This query actually involves a join of 7 tables. I assumed that the incorrect sum() results that I was getting was because I had that part of the query wrong. After stripping it down to the above, the query worked correctly, and as I added portions back in, I came across what I believe to be the real failure.

There is another table called PMT which has a row for every payment received. As part of this query, we want to be able to search for debtors who haven't made a payment in more than x days.

So now the query in english becomes:

select the debtor ID, debtor name, and a sum of debt balances where debt status is less than 400 and the sum of the balances for those debts is greater than $300, and who had not made a payment in more than 180 days.

So, I add a join on the PMT table and a condition to the where clause and my sum() goes crazy. I was able to determine that the query was returning a result for every payment which had been recorded that met the date match criteria - then the sum() would add those together. So, if a debtor had made 3 payments more than 6 months ago, the sum() would be 3x what it should be.

So let's say these are my tables:

DBTR:
DEBTOR_ID          NAME
123                John Smith
654                Abraham Lincoln


DEBT:
DEBT_ID   DEBTOR_ID    STATUS_CODE      CUR_BAL
1         123          255               399.54
2         123          403               303.47
3         123          403              1416.92
4         123          255              1488.37
5         123          255              1394.02
6         456          255               550.65

PMT:
PMT_ID   DEBTOR_ID    PMT_DATE    PMT_AMOUNT
1        123          2011/12/01    50.00
2        456          2012/10/15   200.00
3        123          2010/09/15   125.00

What I would want to return from my query would be 1 record:

DEBTOR_ID   NAME              SUM_OF_CUR_BAL
123         John Smith        3281.93

Abraham Lincoln wouldn't be returned because he made a payment 2 months ago, and 2 of John Smith's debts don't get summed because the status code is greater than 400.

I don't need any of the values from the PMT table returned, but I want to filter my results based on a test of the payment date.

How can I test the most recent payment date without getting a hit for every record where the payment date is older than a certain date?

Thanks again for the help!

JAFrank

 

[SkipVought]

Something like this...

Where [pmt date] =
(
Select max([pmt date])
From [some table or more]
Where [i]appropriate criteria[/i]
)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JAFrank]

I am getting so close, but I have run up against another odd behavior, and can't figure a way around it. Here is my query as it currently stands:

Select 
d.debtor_id, d.name1, sum(t.cur_bal) as total, max(e.name), max(p.pmt_date)

from
   dm.dbtr d
      left outer join dm.dbtremp e on d.debtor_id = e.debtor_id
         left outer join dm.pmt p on e.debtor_id = p.debtor_id 
            join
               dm.debt_view t on d.debtor_id = t.primary_debtor_id
where
   e.name = (select max(emp.name) from dm.dbtremp emp where emp.debtor_id = d.debtor_id)
and
   p.pmt_date = (select max(pmt.pmt_date) from dm.pmt pmt where pmt.debtor_id = d.debtor_id)
and
   t.primary_debtor_id = XXXXXX
and
   t.status_code < 400
group by
   d.debtor_id, d.name1
having
   sum(t.cur_bal) > 300

Tables are as follows:
dbtr contains a record for each debtor pk = debtor_id
dbtremp contains employer information for debtors. a debtor may have zero or more records. fk = debtor_id
pmt contains records of payments received from debtors. a debtor may have zero or more records. fk = debtor_id
debt_view contains records of individual debts owed. a debtor may have one of more records. fk = primary_debtor_id

What I am trying to achieve is to return (in this example) a single record which would include:
debtor_id from the dbtr table,
name1 from the dbtr table,
name from the dbtremp table (if a debtor has more than one employer, it can be any of them, or blank (null) if they don't have record of one)
sum of the cur_bal field from the debt_view table of records which have a status_code value < 400
the most recent date that a payment was made by this debtor

I am using left outer join on dbtremp and pmt because there is a chance that a debtor may not have a record in either or both of those.

The particular debtor that I am experimenting with right now has made 4 payments and has had 3 different employers. If I run the query without the subqueries on payments and employers, the sum of current balance = 12 x the actual balance. this makes sense to me. When I have the subqueries in, though, the sum is 3 x the actual balance.

I *think* what is happening is that the query is figuring the max(pmt_date) for each of the 3 employers and adding them together, but I have not been able to think of a way to rewrite it so that I get the results that I am after.

Any ideas?

Thanks again,

JAFrank

 

[JAFrank]

I think I found a solution, but I would appreciate any feedback on ways that it might fail. I ended up moving my subqueries into the from clause, like this:

select
   d.debtor_id, d.name1, emp.name, sum(t.cur_bal) as total
from 
   dm.dbtr d
left outer join
   (select debtor_id, max(pmt_date) as pmt_date from dm.pmt group by debtor_id) as pmt
on pmt.debtor_id = d.debtor_id
left outer join
   (select debtor_id, max(name) as name from dm.dbtremp group by debtor_id) as emp
on emp.debtor_id = d.debtor_id
   join dm.debt_view t on t.primary_debtor_id = d.debtor_id
   join dm.dbtr_view r on r.debtor_id = d.debtor_id
   join dm.clt c on c.clt_id = t.clt_id
   left outer join dm.dbtrapi a on a.debtor_id = d.debtor_id
having
   sum(t.cur_bal) > 300
   
where 
   d.debtor_id = XXXXXX
group by
   d.debtor_id, d.name1, emp.name

This seems to give the results that I am looking for, but I have never structured a query like this, so I welcome any thoughts.

Thanks!

JAFrank-