June 12, 2012 at 4:05 pm
I have a table with this values:
key year month value
------------------------
1 2011 1 100
1 2011 2 200
1 2012 4 300
2 2011 1 150
2 2011 2 180
2 2011 3 200
I want to obtain:
key year month YTD
------------------------
1 2011 1 100
1 2011 2 300
1 2012 4 300
2 2011 1 150
2 2011 2 330
2 2011 3 530
Some help? Thank you very much
June 12, 2012 at 6:18 pm
You should be able to work with something like this. Straight from SS Help 🙂
The following example groups SalesQuota and aggregates SaleYTD amounts. The GROUPING function is applied to the SalesQuota column.
Copy Code
USE AdventureWorks2012;
GO
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO
The result set shows two null values under SalesQuota. The first NULL represents the group of null values from this column in the table. The second NULL is in the summary row added by the ROLLUP operation. The summary row shows the TotalSalesYTD amounts for all SalesQuota groups and is indicated by 1 in the Grouping column.
Here is the result set.
SalesQuota TotalSalesYTD Grouping
------------ ----------------- --------
NULL 1533087.5999 0
250000.00 33461260.59 0
300000.00 9299677.9445 0
NULL 44294026.1344 1
(4 row(s) affected)
Steve
We need men who can dream of things that never were.
June 12, 2012 at 6:38 pm
Search this site for an article by Jeff moden called 'Quirky Update'. Also other sources using "running totals" or "Cumulative Update" as your keywords. They'll help walk you through what ends up being a very involved process, but Jeff's article is one of the most comprehensive.
In general, if you have the option, leave this kind of thing to a front end to do. If you don't have that option, be prepared to get inundated with a LOT of information to get it to perform right.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 12, 2012 at 11:30 pm
Try this
;with cte
as
(
select *, row_number() over(partition by [Key],[Year] order by [month]) as ID
from #sale
)
select cte.,cte.[month], cte.[year],cte.value, A.Total
from cte
INNER JOIN
(
select Sal.[Key], cte.Id,cte.[year], sum(Sal.value) Total
from cte
inner join #Sale Sal on Sal.[Key]=cte.[Key] and Sal.[month]<=cte.[month] and Sal.[year]=cte.[year]
group by Sal.[Key], cte.Id,cte.[year]
)A ON A.[Key]=cte. and A.Id=cte.Id and A.year=cte.year
June 13, 2012 at 12:21 am
Thanks
June 13, 2012 at 2:29 am
/* If you're not concerned about persisting the results, then a recursive CTE works just fine */
/* Working with more than a few thousand rows? Then spool "OrderedData" into a local #temp table */
/* and create a unique clustered index on rn. */
DROP TABLE #Sales
CREATE TABLE #Sales ( INT, [year] INT, [month] INT, value INT)
INSERT INTO #Sales (, [year], [month], value)
SELECT 1, 2011, 1, 100 UNION ALL
SELECT 1, 2011, 2, 200 UNION ALL
SELECT 1, 2012, 4, 300 UNION ALL
SELECT 2, 2011, 1, 150 UNION ALL
SELECT 2, 2011, 2, 180 UNION ALL
SELECT 2, 2011, 3, 200
-- Generate a processing sequence
;WITH OrderedData AS (
SELECT rn = ROW_NUMBER() OVER (ORDER BY , [year], [month]), *
FROM #Sales
),
Calculator AS (
SELECT
rn,
, [year], [month], value,
YTD = value
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT
o.rn,
o., o.[year], o.[month], o.value,
YTD = CASE
WHEN o. = c. AND o.[year] = c.[year] THEN o.value + c.YTD
ELSE o.value END
FROM Calculator c -- last row
INNER JOIN OrderedData o ON o.rn = c.rn+1 -- current row
)
SELECT , [year], [month], value, YTD
FROM Calculator
/*
key year month YTD
------------------------
1 2011 1 100
1 2011 2 300
1 2012 4 300
2 2011 1 150
2 2011 2 330
2 2011 3 530
*/
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
June 13, 2012 at 3:04 am
wonderful!
June 14, 2012 at 7:46 am
Go hit up Amazon and buy Itzik Ben-Gan's Window Function Book: http://www.amazon.com/Microsoft-Server-High-Performance-Window-Functions/dp/0735658366. It is one of the few SQL Server books I have in my library - and if I have it you should too!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply