Rookie In Need..

[pacard]

Hello All-Hoping for a little guidance. I'm trying to generate a list of persons, their property, unit and their payments within a date range. My problem is that the payments are being duplicated if they lived in more than one unit. Is there a way to only include those units that actually had payments associated with them? Here's the query:

SELECT p.property, u.unit_name AS UNIT, a.app_name as Client, t.amt_total AS PMT_AMT, t.trans_date, tt.ttype_name
FROM properties p, units u, apps a, occupants o, trans t, trantypes tt
WHERE p.id = 3
AND p.id = u.property_id
AND a.id = o.app_id
AND u.unit_id = o.unit_id
AND t.trans_date BETWEEN '8/15/2006' and '1/07/2007'
AND tt.ttype_name = 'PAYMENT'
AND t.ttype_id = tt.ttype_id
AND t.app_id = a.id
ORDER BY 3,5 ASC

Here's an example of the output

unit client pmt_amt trans_date
546C ANDERSON, GARRY $315.00 8/17/2006
540D ANDERSON, GARRY $315.00 8/17/2006
546C ANDERSON, GARRY $322.50 9/2/2006
540D ANDERSON, GARRY $322.50 9/2/2006

As you can see the payments have shown up twice because at one time this person lived in a second unit.

ANY HELP is much appreciated!
pc

 

[PHV]

Brute force method:
SELECT [!]DISTINCT[/!] ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pacard]

Hey PH,

Thanks for trying. That didn't work. I'm still getting the same duplicate payments....Any other ideas?

 

[gmmastros]

It looks like you are missing a join somewhere.


You appear to have a column in the trans table for the apps id. Do you also have a unit_id in the trans table? In my opinion, a transaction should be applied to a unit, not necessarily an application.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pacard]

gmmastros,

I don't have a unit_id in the trans table. unit_id only appears in the units table or occupants table...Any suggestions?

 

[gmmastros]

Yeah... Add the unit_id to the table. When a transaction is applied, it should be applied to a unit because that is what you are getting paid for.

I suspect you have an application date in the application table. You could get the application id that is associated with the last application *think Max(ApplcationDate)* that this occupant has filled out to determine the unit to apply the transaction to.

In my opinion, this is only a band-aid solution. What would happen if a person is currently occupying 2 units? How do you know which unit to apply the payment toward. I hate to say it, but it sounds to me like you need to re-design your table and also your application. Now, don't misunderstand me, most of what I see here is ok. You just seem to be missing this one critical piece.

Also, remember that this is just my opinion. You, and others, may disagree. Hopefully I've given you something to think about. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pacard]

Thanks George. Bearing in mind this is all new to me...

1. This is PostGrSql. (a bit more disclosure)
2. When I add the column to the table as best as I can tell, I would do it like this:
ALTER TABLE trans
ADD unit_id INT(20)

3. DB Behavior Question- will the database only populate this column from this point forward as new transactions occur, or will it look "backwards" and attach a unit_id to each previous transaction?

Any other insight would be appreciated...Thanks again,

pc

 

[Mercury2]

Besides the unit_id column, it's better to use the JOIN clause to match the tuples instead of the WHERE clause. Anyway, you can try out this,

SELECT DISTINCT p.property, u.unit_name AS UNIT, a.app_name as Client, t.amt_total AS PMT_AMT, t.trans_date, tt.ttype_name
FROM properties p, units u, apps a, occupants o, trans t, trantypes tt
WHERE p.id = 3
AND p.id = u.property_id
AND a.id = o.app_id
AND u.unit_id = o.unit_id
AND t.trans_date BETWEEN '8/15/2006' and '1/07/2007'
AND tt.ttype_name = 'PAYMENT'
AND t.ttype_id = tt.ttype_id
AND t.app_id = a.id
GROUP BY p.property, u.unit_name, a.app_name, t.trans_date, tt.ttype_name
ORDER BY 3,5 ASC

 

[pacard]

Thanks Mercury, but that didn't do it. Let me try approaching this a different way...

I asked the database to give me records where the transaction date is between the movein & moveout date using this:

AND t.trans_date BETWEEN o.move_in and o.move_out

The problem is that it only responded w/ tenants that have moved out. Any way to include both current tenants and the ones that have moved out? When I do t.trans_date > o.move-in, it sends me back to square 1 w/ duplicate entries....

 

[gmmastros]

Maybe....

t.trans_date > 0.move_in
And o.move_out is null

This is assuming that you have null values for move_out until someone actually does move out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Mercury2]

yes, maybe George is right. Anyway you could try

...
AND ( t.trans_date BETWEEN o.move_in and o.move_out
OR o.move_out is Null )
...

If you need more help pls paste here the complete corrected SELECT statement and all the messages you get when run.

 

[pacard]

thanks to everyone for helping!

The "and/or" did it!...amazing how a couple missed brackets can screw everything up.

Thanks again,
pc