One to Many

[NancyJ]

I have a table of users and a table of subscriptions
Every payment that's made records an expiry_date and a user_id. There can be multiple payments made against a single account.

I need to get a list of accounts expiring in the next 5 days - that is accounts where there is a payment record with an expiry date within the next 5 days AND no further records. mySQL version 4.0.27.

[bazz]

Hi Nancy,

Do you mean that the account has an expiry date within the next five days and that the same account has no records with an expiry date after then?

bazz

[NancyJ]

Hi Nancy,

Do you mean that the account has an expiry date within the next five days and that the same account has no records with an expiry date after then?

bazz

yes...

[TheShaner]

Nancy, try the below:
SELECT user_id, MAX(expiry_date) AS last_expiry_date
WHERE last_expiry_date >= CURDATE() AND last_expiry_date <= DATE_ADD(CURDATE(), INTERVAL 5 DAY)
GROUP BY user_id
ORDER BY user_id
I believe MAX should work on a date but I haven't tested though.

You could also use the DATEDIFF (http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_datediff) function to substract the current date and the 5 days later date to see if if falls in the 0 - 5 range.

-Shane

[NancyJ]

Needed a bit of a tweak (HAVING instead of WHERE) but otherwise, seems to work perfectly (based on limited data)

Final query was

SELECT user_id, MAX( expiry_date ) AS last_expiry_date
FROM payments
GROUP BY user_id
HAVING last_expiry_date >= CURDATE( )
AND last_expiry_date <= DATE_ADD( CURDATE( ) , INTERVAL 5
DAY )
ORDER BY user_id

[TheShaner]

Needed a bit of a tweak (HAVING instead of WHERE) but otherwise, seems to work perfectly (based on limited data)
Oops, sorry, I'm glad you knew to change that, hehe (and to overlook my missing FROM, haha)

-Shane

[NancyJ]

I just figured you left that out because I didn't tell you the table name ;)

I learnt about HAVING from writing this monstrosity:


SELECT mia_booking.id, IFNULL( (

SELECT sum( mia_booking_payment.amount_base )
FROM mia_booking_payment
WHERE mia_booking.id = mia_booking_payment.booking_id AND date_received
LIKE '$year-$month%' AND account = 'ECM' ) , 0
) + IFNULL( (

SELECT sum( value_euro )
FROM ajt_cheques
LEFT JOIN ajt_payments_az ON ajt_payments_az.cheque_id = ajt_cheques.cheque_id
WHERE ajt_payments_az.booking_id = mia_booking.id AND accountto = 'ECM' AND written
LIKE '$year-$month%' ) , 0
) + IFNULL( (

SELECT sum( value_euro )
FROM ajt_cheques
LEFT JOIN ajt_payments_azf ON ajt_payments_azf.cheque_id = ajt_cheques.cheque_id
WHERE ajt_payments_azf.booking_id = mia_booking.id AND accountto = 'ECM' AND written
LIKE '$year-$month%' ) , 0
) + IFNULL( (

SELECT sum( value_euro )
FROM ajt_cheques
LEFT JOIN ajt_payments_ecc ON ajt_payments_ecc.cheque_id = ajt_cheques.cheque_id
WHERE ajt_payments_ecc.booking_id = mia_booking.id AND accountto = 'ECM' AND written
LIKE '$year-$month%' ) , 0
) AS amount_recieved, IFNULL( (

SELECT sum( value_euro )
FROM ajt_cheques
JOIN ajt_payments_ecm ON ajt_cheques.cheque_id = ajt_payments_ecm.cheque_id
WHERE ajt_payments_ecm.booking_id = mia_booking.id AND written
LIKE '$year-$month%' AND accountfrom = 'ECM' AND (
description
LIKE 'Net Proprietor%' OR description
LIKE 'Rental for%' OR description
LIKE 'Expense%'
) ) , 0
) AS net_prop, IFNULL( (

SELECT sum( value_euro )
FROM ajt_cheques
JOIN ajt_payments_ecm ON ajt_cheques.cheque_id = ajt_payments_ecm.cheque_id
WHERE ajt_payments_ecm.booking_id = mia_booking.id AND written
LIKE '$year-$month%' AND accountfrom = 'ECM' AND (
description NOT
LIKE 'Net Proprietor%' AND description NOT
LIKE 'Rental for%' AND description NOT
LIKE 'Expense%' AND description NOT
LIKE 'Return of Security Deposit%'
) ) , 0
) AS margin, (
`sum` + ifnull( security_deposit, 0 ) + ifnull( service_2_price, 0 ) + ifnull( service_3_price, 0 ) + ifnull( service_4_price, 0 ) + ifnull( service_5_price, 0 )
) AS amount_expected, IFNULL( (

SELECT sum( value_euro )
FROM ajt_cheques
JOIN ajt_payments_ecm ON ajt_cheques.cheque_id = ajt_payments_ecm.cheque_id
WHERE ajt_payments_ecm.booking_id = mia_booking.id AND written
LIKE '$year-$month%' AND accountfrom = 'ECM' AND description
LIKE 'Return of Security Deposit%' ) , 0
) AS security_deposit
FROM mia_booking
JOIN booking ON mia_booking.id = booking.bid
HAVING amount_recieved <>0 OR net_prop <>0 OR security_deposit <>0 OR margin <>0


(I didn't design the db for this project - tbh I'm not sure anyone really did... its just lots of unrelated tables cobbled together for various different 'off the shelf' scripts then hacked up by multiple coders - not including myself!

[TheShaner]

I just figured you left that out because I didn't tell you the table name ;)
Uh, yeah, right! :D I'm just a bit scatter-brained today. I can definitely say you know what that is like after having to write that nasty SQL, haha.

(I didn't design the db for this project - tbh I'm not sure anyone really did... its just lots of unrelated tables cobbled together for various different 'off the shelf' scripts then hacked up by multiple coders - not including myself!
Unfortunately that is how most projects seem to be :( They almost seem mythical; those projects where you can design a DB from the start and actually have it all in at least 3rd NF...

-Shane

[NancyJ]

you know, for all that code, all it does is return booking id, amount invoiced, amount received, amount paid out as rent, amount returned as security deposit and miscellaneous expenses within a given date range.

[bazz]

you know, for all that code, all it does is return booking id, amount invoiced, amount received, amount paid out as rent, amount returned as security deposit and miscellaneous expenses within a given date range.

lol. That gives me hope because I am building (what I think will be), a huge Db and I am trying to get it to 4NF. So far, my code has nothing so long.

bazz