Question 112992 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Finding total budget using a case expression

So I’m trying to find the total budget of each account in the most current week. The sample data would look have 3 columns Account/Week/Budget. The desired outcome would be the "Current Budget" Column. So that when I sum the "Current Budget" column I would sum the most recent data of each account for what the projected budget is. If there is no data on the account for the current week (3), it would look at the previous week’s data (2) instead.

Account Week Budget Current budget
1 1 $24 null
2 1 $100 null
3 1 $30 null
1 2 $100 null
2 2 $24 null
3 2 $100 $100
1 3 $100 $100
2 3 $24 $24

So far this is what I have. But I run into the issue where I would have last weeks budget for accounts that already have a week 3 budget.

declare @currentweek = datepart(week, getdate())
declare @lastweek = @currentweek -1

select t.*,
case
when t.week = @currentweek then t.budget
when t.week = @lastweek then t.budget
else null
from
table t

Tags:

2

Answers


  1. 0

    First thing that comes to my mind is using a subquery to know which is the most recent week of each account, like the following code:

    select a.*, 
case when a.week = (SELECT MAX(WEEK) FROM BUDGETS B WHERE a.account = B.ACCOUNT) 
then a.budget end as [current budget]
from budgets a

    You could also use an auxiliary table:

    
declare @auxTable TABLE (account int, mWeek int)
INSERT INTO @auxTable
SELECT ACCOUNT, MAX(WEEK)  FROM BUDGETS GROUP BY account

select a.*, case when a.week=b.mWeek then a.budget end as [current budget]
from budgets a
left join @auxTable b on a.account=b.account

    Please, let me know if I didn’t understand your necessity correctly

    Login or Signup to reply.
  2. 0

    I would consider something like

    WITH cte AS
(
 SELECT account, week, budget, RANK() OVER(PARTITION BY account ORDER BY week DESC) wr
 FROM mytable
)
SELECT account, week, CASE WHEN wr = 1 THEN SUM(budget) ELSE NULL END budget
FROM cte
GROUP BY account, week, wr
ORDER BY week, account

    In the common table expression RANK() is used to figure out the most recent week (so the main query can have a special case for wr = 1 which is the most recent week). I used CASE WHEN wr = 1 THEN SUM(budget) ELSE NULL END even though I think SUM(CASE WHEN wr = 1 THEN budget END) is easier to read, since the latter will give a warning about nulls being eliminated by the aggregate.

    You can see it working in this Fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search