So the sum() function brings the multiple discounts together for a member in
the UPDATE statement?
So I have a question regarding this then the Discount.Modifier stands for 0
= percent of a value or 1 = static
The update you constructed would work for all static values since sums
groups all the numerics together.
So I assume if I used sum() for percent of values it would collect the sum
of all values calculated like so for percent of modifier?
sum(D.Amount / 100 * [C#VALUE])
Also would it be logical to have separate Updates (One for Static, One for
Percent of Value) or would it be more logical to try to achieve this by
using ifnull and nullif on the D.Modifier to get the correct sum function()?
Igor Tandetnik wrote:
>
> 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]
> -----------------------------------------------------------------------------
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7631805
Sent from the SQLite mailing list archive at Nabble.com.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
