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.

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

CodePudding user response：

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

CodePudding user response：

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.