July 30, 2017 at 4:06 am
Nice one to start the week thanks Steve
...
July 31, 2017 at 1:14 am
Nice one to start the week on, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
July 31, 2017 at 1:47 am
For me (and please remember it's very early on a Monday morning and I haven't had much coffee yet), the code won't do what the question asks.
You're ignoring the partial dollars for payments (which means rounding down) and doing the same for recharges (rounding down again (ignoring the fact it's a negative figure)).
Therefore, you've more chance of running out of cash on the card.
What you should be doing is ignoring the partial dollars on the recharges (so underestimating what you've put on the card) and rounding up the cost of the coffee (so overestimating spend).
That way, you'll never run out of cash on the card because you've assumed you've paid more and recharged less.
Do any of that make sense???
July 31, 2017 at 2:06 am
Hi Steve,
in your solution the Group by is missing.
GROUP BY a;
Instead you could use a solution like that, without showing the functionality of CEILING.
WITH mycte (a, n)
AS (SELECT a, n FROM (VALUES ('Coffee', 2.38), ('Coffee', 4.53), ('Recharge card', -15.50), ('Recharge card', -5.25)) a (a, n) )
, Calculate_Factor as(
Select a, n, cast(n/ABS(n) as int) as factor
from mycte)
SELECT a, SUM(FLOOR(abs(n))*factor) from Calculate_Factor
GROUP BY a;
Have a nice day,Christoph
July 31, 2017 at 2:07 am
richardmgreen1 - Monday, July 31, 2017 1:47 AMFor me (and please remember it's very early on a Monday morning and I haven't had much coffee yet), the code won't do what the question asks.You're ignoring the partial dollars for payments (which means rounding down) and doing the same for recharges (rounding down again (ignoring the fact it's a negative figure)).
Therefore, you've more chance of running out of cash on the card.
What you should be doing is ignoring the partial dollars on the recharges (so underestimating what you've put on the card) and rounding up the cost of the coffee (so overestimating spend).
That way, you'll never run out of cash on the card because you've assumed you've paid more and recharged less.
Do any of that make sense???
I tend to agree with you on this - If my coffee costs 2.99 and I round it down to 2 dollars - I've already lost a dollar on my card - so there is no way I'm going to have any money left on my card to buy my Christmas presents!
July 31, 2017 at 2:17 am
Have to agree with the above. I have to admit I didn't really understand what Steve was trying to achieve, but I still got points because that was the only option that made any sense, given the posted results. "I can't do this with T-SQL." 🙂 As if that ever happens?
But given the problem, CEILING() would be the correct choice. If you really want to round towards zero, and don't care for decimals, you can simply CAST it to INT instead.
July 31, 2017 at 5:48 am
richardmgreen1 - Monday, July 31, 2017 1:47 AMFor me (and please remember it's very early on a Monday morning and I haven't had much coffee yet), the code won't do what the question asks.
You're ignoring the partial dollars for payments (which means rounding down) and doing the same for recharges (rounding down again (ignoring the fact it's a negative figure)).
Therefore, you've more chance of running out of cash on the card.
What you should be doing is ignoring the partial dollars on the recharges (so underestimating what you've put on the card) and rounding up the cost of the coffee (so overestimating spend).
That way, you'll never run out of cash on the card because you've assumed you've paid more and recharged less.
Do any of that make sense???
I agree with what you said, however I think the purpose of the example was to point out how FLOOR functions with negative numbers.
What I did for my solution was to simply CAST the value to an INT within the SUM so I didn't have to determine whether or not to use FLOOR or CEILING.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
July 31, 2017 at 6:12 am
Or we could simply just use ROUND() to get the correct result.
WITH mycte (a, n)
AS (SELECT a, n
FROM ( VALUES
('Coffee', 2.38),
('Coffee', 4.53),
('Recharge card', -15.50),
('Recharge card', -5.25)
) a (a, n)
)
SELECT
a,SUM(ROUND(n,0,1))
FROM mycte
GROUP BY a;
July 31, 2017 at 7:47 am
WITH mycte (a, n)
AS (SELECT a, n FROM (
SELECT 'Coffee', 2.38 UNION SELECT 'Coffee', 4.53 UNION SELECT 'Recharge card', -15.50 UNION SELECT 'Recharge card', -5.25) a (a, n) )
SELECT TransactionType=a
, AccountingAmount=SUM(CEILING(n))
, ActualAmount=SUM(n)
, ChristmasSavings =CAST(SUM(CEILING(n)) as money)-SUM(n)
FROM mycte
GROUP BY a;
I agree with richardmgreen1, it should be tracked as $8 spent, $20 loaded, for an "available" balance of $12 and an effective Christmas Savings of $1.84.
July 31, 2017 at 8:51 am
Another agreement for @richardgreen1. Not sure how anyone could have thought that floor would be the right choice for this. Ceiling is the only function that works correctly for both the positive and negative values given the problem you want to solve.
July 31, 2017 at 9:27 am
Christoph Muthmann - Monday, July 31, 2017 2:06 AMHi Steve,
in your solution the Group by is missing.GROUP BY a;
Instead you could use a solution like that, without showing the functionality of CEILING.
WITHmycte (a, n)
AS (SELECT a, n FROM (VALUES ('Coffee', 2.38), ('Coffee', 4.53), ('Recharge card', -15.50), ('Recharge card', -5.25)) a (a, n) )
, Calculate_Factor as(
Select a, n, cast(n/ABS(n) as int) as factor
from mycte)
SELECT a, SUM(FLOOR(abs(n))*factor) from Calculate_Factor
GROUP BY a;
Hi Cristoph,
You had a creative way of getting the factor. However, you might have just used SIGN() which would return the same value.
July 31, 2017 at 9:33 am
Christoph Muthmann - Monday, July 31, 2017 2:06 AMHi Steve,
in your solution the Group by is missing.GROUP BY a;
Instead you could use a solution like that, without showing the functionality of CEILING.
WITHmycte (a, n)
AS (SELECT a, n FROM (VALUES ('Coffee', 2.38), ('Coffee', 4.53), ('Recharge card', -15.50), ('Recharge card', -5.25)) a (a, n) )
, Calculate_Factor as(
Select a, n, cast(n/ABS(n) as int) as factor
from mycte)
SELECT a, SUM(FLOOR(abs(n))*factor) from Calculate_Factor
GROUP BY a;
Thank, bad cut/paste.
July 31, 2017 at 9:53 am
Steve Jones - SSC Editor - Monday, July 31, 2017 9:33 AMChristoph Muthmann - Monday, July 31, 2017 2:06 AMHi Steve,
in your solution the Group by is missing.GROUP BY a;
Instead you could use a solution like that, without showing the functionality of CEILING.
WITHmycte (a, n)
AS (SELECT a, n FROM (VALUES ('Coffee', 2.38), ('Coffee', 4.53), ('Recharge card', -15.50), ('Recharge card', -5.25)) a (a, n) )
, Calculate_Factor as(
Select a, n, cast(n/ABS(n) as int) as factor
from mycte)
SELECT a, SUM(FLOOR(abs(n))*factor) from Calculate_Factor
GROUP BY a;Thank, bad cut/paste.
You're still not accounting for the floor affecting your Coffee amounts incorrectly. Suppose you spend 14.99 on coffee after your prior purchases:
WITH mycte (a, n)
AS (SELECT a, n FROM (VALUES ('Coffee', 2.38), ('Coffee', 4.53), ('Recharge card', -15.50), ('Recharge card', -5.25)), ('Coffee', 14.99)) a (a, n) )
, Calculate_Factor as(
Select a, n, cast(n/ABS(n) as int) as factor
from mycte)
SELECT a, Actual=SUM(n), Converted=SUM(FLOOR(abs(n))*factor) from Calculate_Factor
GROUP BY a;
It will appear that both are at $20, when you have overspent by $1.15.
July 31, 2017 at 10:02 am
Rewording the question. Not sure what I was thinking. Kept looking at rounding up, but wrote and used Floor. Changing the scenario a bit.
Look for the "Ceiling" question in the next month.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply