January 19, 2011 at 7:09 am
-> For example year 2010 consists of 12months and the 12 th months cummulative sum is 7600,now for the next year's 1st month the 7600+the amount in this years 1st month should come and like wise.
create table dbo.sales (year int, month int, amt int)
insert into dbo.sales values(2010,1,100)
insert into dbo.sales values(2010,2,200)
insert into dbo.sales values(2010,3,300)
insert into dbo.sales values(2010,4,400)
insert into dbo.sales values(2010,5,500)
insert into dbo.sales values(2010,6,600)
insert into dbo.sales values(2010,7,700)
insert into dbo.sales values(2010,8,800)
insert into dbo.sales values(2010,9,900)
insert into dbo.sales values(2010,10,1000)
insert into dbo.sales values(2010,11,1100)
insert into dbo.sales values(2010,12,1200)
insert into dbo.sales values(2010,1,10)
insert into dbo.sales values(2010,2,20)
insert into dbo.sales values(2010,3,30)
insert into dbo.sales values(2010,4,40)
insert into dbo.sales values(2010,5,50)
insert into dbo.sales values(2010,6,60)
insert into dbo.sales values(2010,7,70)
insert into dbo.sales values(2010,8,80)
insert into dbo.sales values(2010,9,90)
insert into dbo.sales values(2010,10,100)
select * from sales
insert into sales values(2011,01,100)
insert into sales values(2011,12,10)
Need to get the Output like this:
20101110110
20102220330
20103330660
201044401100
201055501650
201066602310
201077703080
201088803960
201099904950
20101011006050
20101112107260
20101213208580
201111008680
201112108690
January 19, 2011 at 7:20 am
So what's the question?
January 19, 2011 at 8:15 pm
after inserting these two records to the previous records we need to get the result as:
insert into sales values(2011,01,100)
insert into sales values(2011,12,10)
ans:
20101110110
20102220330
20103330660
201044401100
201055501650
201066602310
201077703080
201088803960
201099904950
20101011006050
20101112107260
20101213208580
201111008680
201112108690
January 19, 2011 at 8:25 pm
Ninja's_RGR'us (1/19/2011)
So what's the question?
The question was in the sub-title of the post on the forum page.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2011 at 9:26 pm
nandakrishnacherukuri,
First, thanks for the readily consumable test data and the CREATE TABLE code. That answered all of my questions without me having to ask. Well done! 🙂
Second, here's a super high performance method for doing your "running total". The details are in the comments of the code. As usual, I include the original test data just to keep it all together...
--=============================================================================
-- This section of the code merely sets up a test bed.
-- Nothing is this section is a part of the solution
--=============================================================================
--===== Do this in a nice safe place that everyone has
USE TempDB
;
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('TempDB.dbo.Sales','U') IS NOT NULL
DROP TABLE TempDB.dbo.Sales
;
--===== Create the test table
CREATE TABLE dbo.Sales
(
[Year] INT NOT NULL,
[Month] INT NOT NULL,
Amt INT NOT NULL
)
;
--===== Populate the test table with the given data
INSERT INTO dbo.Sales
([Year], [Month], Amt)
SELECT 2010,1,100 UNION ALL
SELECT 2010,2,200 UNION ALL
SELECT 2010,3,300 UNION ALL
SELECT 2010,4,400 UNION ALL
SELECT 2010,5,500 UNION ALL
SELECT 2010,6,600 UNION ALL
SELECT 2010,7,700 UNION ALL
SELECT 2010,8,800 UNION ALL
SELECT 2010,9,900 UNION ALL
SELECT 2010,10,1000 UNION ALL
SELECT 2010,11,1100 UNION ALL
SELECT 2010,12,1200 UNION ALL
SELECT 2010,1,10 UNION ALL
SELECT 2010,2,20 UNION ALL
SELECT 2010,3,30 UNION ALL
SELECT 2010,4,40 UNION ALL
SELECT 2010,5,50 UNION ALL
SELECT 2010,6,60 UNION ALL
SELECT 2010,7,70 UNION ALL
SELECT 2010,8,80 UNION ALL
SELECT 2010,9,90 UNION ALL
SELECT 2010,10,100 UNION ALL
SELECT 2010,11,110 UNION ALL
SELECT 2010,12,120 UNION ALL
SELECT 2011,01,100 UNION ALL
SELECT 2011,12,10
;
--=============================================================================
-- This section is one possible solution to the problem.
-- It uses a technique known as the "Quirky Update" and has some very
-- special rules to follow.
--
-- Assuming that we can't modify the original table, we'll preaggregate
-- the data and copy it into a Temp Table as well as a new column to hold
-- the running total. During the running total calculation, we'll add a
-- sorted row number which will be used as a part of the safety check for
-- the running total calculations.
--
-- If you change anything in the code below, you run the risk of the code
-- failing.
--
-- Other than that, this will blow the doors off any other method except
-- maybe for a well written CLR.
--
-- Jeff Moden
--=============================================================================
--===== Conditionally drop the Temp Table to make reruns easier.
IF OBJECT_ID('TempDB..#CumeSales','U') IS NOT NULL
DROP TABLE #CumeSales
;
--===== Preaggregate the data in the original table and store it in the Temp
-- Table adding a column to calculate the cumulative (running) total in.
-- Notice how we use ISNULL to create NOT NULL columns for the PK.
WITH
ctePreAggregate AS
(--==== Preaggregate the amounts by Year and Month
SELECT [Year],
[Month],
TotalMonth = SUM(Amt)
FROM dbo.Sales
GROUP BY [Year], [Month]
)
SELECT [Year] = ISNULL([Year] ,0),
[Month] = ISNULL([Month],0),
TotalMonth,
TotalCume = CAST(0 AS INT)
INTO #CumeSales
FROM ctePreAggregate
;
--===== Add the Primary Key which is the quintessential part of the Quirky Update.
-- It provides the necessary Unique Clustered Index that causes it to work
-- correctly. Because it's a Temp Table, we won't give it a name because
-- PK names must be unique in a database and giving it a name would prevent
-- concurrent runs. The FILLFACTOR of 100 will help the code run its
-- fastest even if the default FILLFACTOR isn't 0 (same as 100) or 100.
ALTER TABLE #CumeSales
ADD PRIMARY KEY CLUSTERED ([Year], [Month]) WITH FILLFACTOR = 100
;
--===== Build and preset some necessary and obviously-named variables to support the
-- Quirky Update code just like you'd do it if it were code like C# or whatever.
DECLARE @PreviousBalance INT,
@Anchor INT,
@Counter INT
;
SELECT @PreviousBalance = 0,
@Counter = 1
;
--===== Create the running total (TotalCume) using the "Quirky Update". This is an
-- enhanced version with a built in safety feature which will cause an error
-- and an implicit rollback if the UPDATE goes haywire. The safety check is
-- created by the RowNum column and the @Counter variable.
WITH
cteBaseData AS
(
SELECT RowNum = ROW_NUMBER() OVER (ORDER BY [Year], [Month]),
TotalMonth,
TotalCume
FROM #CumeSales WITH (TABLOCKX) --Not really needed but takes a single table lock
--instead of a bazillion row locks which will get
--escalated anyway.
)
UPDATE tgt
SET @PreviousBalance = tgt.TotalCume = CASE
WHEN @Counter = tgt.RowNum
THEN tgt.TotalMonth + @PreviousBalance
ELSE 1/0
END,
@Anchor = tgt.RowNum,
@Counter = @Counter + 1
FROM cteBaseData tgt
OPTION (MAXDOP 1) --Prevents parallelism which absolutely must be prevented for this code.
;
--===== Finally, SELECT the desired result
SELECT [Year], [Month], TotalMonth, TotalCume
FROM #CumeSales
ORDER BY [Year], [Month]
;
Here are the results from the run above. Like I said, this is nasty-fast code that will do a million rows in just a couple of seconds.
(26 row(s) affected)
(14 row(s) affected)
(14 row(s) affected)
Year Month TotalMonth TotalCume
----------- ----------- ----------- -----------
2010 1 110 110
2010 2 220 330
2010 3 330 660
2010 4 440 1100
2010 5 550 1650
2010 6 660 2310
2010 7 770 3080
2010 8 880 3960
2010 9 990 4950
2010 10 1100 6050
2010 11 1210 7260
2010 12 1320 8580
2011 1 100 8680
2011 12 10 8690
(14 row(s) affected)
If you have any questions, please don't hesitate to ask.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2011 at 10:40 pm
Thanks For giving the solution.
January 20, 2011 at 5:14 am
Jeff Moden (1/19/2011)
Ninja's_RGR'us (1/19/2011)
So what's the question?The question was in the sub-title of the post on the forum page.
Tx, but I never get in through that door! :w00t:
January 20, 2011 at 1:23 pm
nandakrishnacherukuri (1/19/2011)
Thanks For giving the solution.
You're welcome but, I need to make sure, do you understand the solution? If you don't, don't use the solution because you'll need to explain it to others and you'll be the one that maintains it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply