So GROUP BY will separate the the grouping done by sum()? So that also means
I could factor in all discounts not just static ones? Like so? Modifier 0 =
Percent Of and 1 = Static amount? Also which statement is going to be more
efficient the UPDATE or the REPLACE?
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance -
SUM((CASE WHEN D.Modifier == 0 THEN (D.Amount / 100.00 * 500) ELSE D.Amount
END))
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
GROUP BY DA.MemberId
Igor Tandetnik wrote:
>
> Cnichols <[EMAIL PROTECTED]> wrote:
>> Thanks alot the UPDATE works great! Although the REPLACE INTO still
>> only executes the last row of a multi discount.
>
> I see. MA to DA is a one-to-many relation, too. You need to move all the
> tables into an inner select: the goal is to use SUM aggregate to produce
> a single row (or no rows) for each MA.MemberId. Like this:
>
> REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT DA.MemberId, MA.Balance - 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
> 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
> GROUP BY DA.MemberId
>
> Igor Tandetnik
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7632401
Sent from the SQLite mailing list archive at Nabble.com.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
