March 3, 2015 at 7:07 am
Good morning,
I have the following tables:
--acts as a transaction table
CREATE TABLE #TestData (
id int not null identity(1,1) primary key,
account varchar(10) not null,
deposit int not null
);
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 10)
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 20)
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 30)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 40)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 50)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 60)
INSERT INTO #TestData (account, deposit) VALUES ('Party', 70)
INSERT INTO #TestData (account, deposit) VALUES ('Party', 80)
--acts as a budget table
CREATE TABLE #TestBudget(id int not null identity(1,1) primary key,
account varchar(10) not null,
budget int not null);
INSERT INTO #TestBudget (account, budget) VALUES ('Vacation', 25)
INSERT INTO #TestBudget (account, budget) VALUES ('Bills', 45)
INSERT INTO #TestBudget (account, budget) VALUES ('Party', 70)
--add a "running total" field from the transactions table while joining budget table
SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id) AS 'total',max(b.budget) as budget
INTO #Temp1
FROM #TestData a
INNER JOIN #TestBudget b on a.account = b.account
GROUP BY a.id, a.account, a.deposit
ORDER BY a.id
--desired results
-- within each account group, when a individual record causes the groups running total to exceed the group's budget, show the difference that causes the groups running total to exceed the budget as unbudgeted. The amount of that transaction upto the budget amount show as renewal. For any succeeding individual records in the group, the amount of that transaction is Unbudgeted.
Here's the SQL I have thus far...
SELECT a.id
,a.account
,a.deposit
,a.total
,a.budget
,case when a.total <= a.budget
then a.deposit
when a.total > a.budget
then a.total - a.budget
end as Renewal
,' ' Unbudgeted --- ??????
FROM #Temp1 a
March 4, 2015 at 1:19 am
How about
WITHcte1 AS (SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id) AS 'total',max(b.budget) as budget
FROM #TestData a
INNER JOIN #TestBudget b on (a.account = b.account)
GROUP BY a.id, a.account, a.deposit
),
cte2 AS (SELECT*,budget-total AS RemainingBudget
FROM cte1
),
cte3 AS (SELECT *,
CASE
WHEN RemainingBudget>0 THEN Deposit
WHEN Deposit+RemainingBudget>0 THEN Deposit+RemainingBudget
ELSE 0
END AS InBudget
FROM cte2
)
SELECT*,
id,
account,
deposit,
InBudgetAS [Renewal],
deposit-InBudgetAS [Unbudgetted]
FROM cte3
Which gives results:
4Bills40400
5Bills50545
6Bills60060
7Party70700
8Party80080
1Vacation10100
2Vacation20155
3Vacation30030
March 4, 2015 at 5:21 am
A table displaying the required results would be helpful.
Try this:
;WITH RunningTotal AS (
SELECT
a.id, a.account, a.deposit, b.Budget,
[Total] = SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id)
FROM #TestData a
INNER JOIN #TestBudget b
ON a.account = b.account
)
SELECT
id, account, deposit, Budget, total,
x.RemainingBudget,
x.InBudget,
Renewal = InBudget,
x.Unbudgeted
FROM RunningTotal
CROSS APPLY (
SELECT
[RemainingBudget] = CASE WHEN Total<=Budget THEN Budget-Total ELSE 0 END,
[Unbudgeted] = CASE WHEN Total>=Budget THEN Total-Budget ELSE 0 END,
[InBudget] = CASE
WHEN Total<=Budget THEN deposit
WHEN Total-Deposit > Budget THEN 0
ELSE Deposit+Budget-Total END
) x
ORDER BY id;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 4, 2015 at 5:39 pm
I have the distinct impression that my under-caffeinated brain is missing something here, but can't you just skip the first step and do something like this?
SELECT id, account, deposit, rtotal, budget
,[deposit o/(u) budget]=budget-rtotal
FROM
(
SELECT a.id, a.account, a.deposit
,rtotal=SUM(deposit) OVER (PARTITION BY a.account ORDER BY a.id ROWS UNBOUNDED PRECEDING)
,b.budget
FROM #Testdata a
JOIN #Testbudget b ON a.account = b.account
) a;
We are talking SQL 2012 here right?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 4, 2015 at 6:52 pm
Thanks everyone. All three suggestions were helpful. I was able to get the desired results for my client.
Thank you!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply