Cnichols <[EMAIL PROTECTED]> wrote:
I have a complex SQL statement which attempts to update all Member
Accounts balance with a discount if there anniversary falls between
last handled date and the current day.
<pre>
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) >
DATE(strftime('0001-%m-%d', (SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d', 'now'))
AND
D.Type = 1 AND D.Modifier = 1
</pre>
The SELECT statement will return multiple rows. A member may have
more than one discount that can be applied. When this case occurs
the last row for that member is the only one that actually executes.
The rows before that are not replacing the value of BALANCE only the
last row for that member will affect the balance.
But of course. You want something like this:
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance - ifnull(
(SELECT sum(D.Amount)
FROM Discounts AS D, Members AS M
WHERE
DA.DiscountId = D.Id AND
DA.MemberId = M.Id AND
DATE(strftime('0001-%m-%d', M.Registered)) >
DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1
), 0)
FROM DiscountsApplied AS DA
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
Or perhaps
UPDATE MemberAccounts SET Balance = Balance - ifnull(
(SELECT sum(D.Amount)
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
WHERE
DA.MemberId = MemberAccounts.MemberId AND
DATE(strftime('0001-%m-%d', M.Registered)) >
DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1
), 0)
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
