May 22, 2013 at 1:27 am
Hi Friends...
I Having Table For The Following Structure..( Sample Data )
Create Table EmpProcessDetail ( EmpId varchar(25), EmpName varchar(50),ProcessName varchar(30),GroupCode varchar(5),PlannedDays int,ActualDays int,
DelayedDays int,TotalDelayDays int)
insert into EmpProcessDetail values ('DS_1','Saran','Purchase','A',15,11,4,NULL),
('DS_1','Saran','Measurement','B',18,15,3,NULL),
('DS_1','Saran','Cutting','C',12,15,-3,NULL),
('DS_1','Saran','Stitching','D',20,27,-7,NULL),
('DS_1','Saran','Ironing','E',14,19,-5,NULL),
('DS_1','Saran','Packing','F',15,13,2,NULL),
('DS_1','Saran','Checking','G',18,12,6,NULL),
('DS_1','Saran','Delivery','H',22,33,-11,NULL),
('DS_2','Arun','Purchase','A',12,13,-1,NULL),
('DS_2','Arun','Measurement','B',15,19,-4,NULL),
('DS_2','Arun','Cutting','C',22,29,-7,NULL),
('DS_2','Arun','Stitching','D',41,43,-2,NULL),
('DS_2','Arun','Ironing','E',27,26,-1,NULL),
('DS_2','Arun','Packing','F',18,13,5,NULL),
('DS_2','Arun','Checking','G',11,14,-3,NULL),
('DS_2','Arun','Delivery','H',17,24,-7,NULL),
('DS_3','Kumar','Purchase','A',41,43,-2,NULL),
('DS_3','Kumar','Measurement','B',23,26,-3,NULL),
('DS_3','Kumar','Cutting','C',22,29,-7,NULL),
('DS_3','Kumar','Stitching','D',41,43,-2,NULL),
('DS_3','Kumar','Ironing','E',12,13,-1,NULL),
('DS_3','Kumar','Packing','F',14,19,-5,NULL),
('DS_3','Kumar','Checking','G',12,14,-2,NULL),
('DS_3','Kumar','Delivery','H',18,13,5,NULL)
select * from EmpProcessDetail
Below I have mentioned The my Required Table Data
EmpId EmpName ProcessName GroupCode PlannedDays ActualDays DelayedDays TotalDelayDays
------------------------- -------------------------------------------------- ------------------------------ --------- ----------- ----------- ----------- --------------
DS_1 Saran Purchase A 15 11 4 4
DS_1 Saran Measurement B 18 15 3 7
DS_1 Saran Cutting C 12 15 -3 4
DS_1 Saran Stitching D 20 27 -7 -3
DS_1 Saran Ironing E 14 19 -5 -8
DS_1 Saran Packing F 15 13 2 -6
DS_1 Saran Checking G 18 12 6 0
DS_1 Saran Delivery H 22 33 -11 -11
DS_2 Arun Purchase A 12 13 -1 -1
DS_2 Arun Measurement B 15 19 -4 -5
DS_2 Arun Cutting C 22 29 -7 -12
DS_2 Arun Stitching D 41 43 -2 -14
DS_2 Arun Ironing E 27 26 -1 -15
DS_2 Arun Packing F 18 13 5 -10
DS_2 Arun Checking G 11 14 -3 -13
DS_2 Arun Delivery H 17 24 -7 -20
DS_3 Kumar Purchase A 41 43 -2 -2
DS_3 Kumar Measurement B 23 26 -3 -5
DS_3 Kumar Cutting C 22 29 -7 -12
DS_3 Kumar Stitching D 41 43 -2 -14
DS_3 Kumar Ironing E 12 13 -1 -15
DS_3 Kumar Packing F 14 19 -5 -20
DS_3 Kumar Checking G 12 14 -2 -22
DS_3 Kumar Delivery H 18 13 5 -17
My Requirement :
1) I want to be Fill the TotalDelayDays Column..
2)The Cumulative Sum Based on the DelayedDays..
3) The Cumulative Sum Should be Calculated on EmpID once it will finished for particular EmpID
then Cumulative Sum should start from Initial for second EmpID ...
I Got The Result For the Following Method...
; WITH BASE AS
(
SELECT EmpId,
EmpName,
ProcessName,
GroupCode,
PlannedDays,
ActualDays,
DelayedDays,
rnk = DENSE_RANK() OVER (PARTITION BY EmpID ORDER BY GroupCode)
FROM EmpProcessDetail)
SELECT
EmpId,
EmpName,
ProcessName,
GroupCode,
PlannedDays,
ActualDays,
DelayedDays,
x.TotalDelayDays
FROM
BASE b
CROSS APPLY (SELECT TotalDelayDays = SUM(DelayedDays)
FROM BASE
WHERE EmpID = b.EmpID
AND rnk <= b.rnk) x
But That Method Takes Over time To Produce The Result for 40000 Records takes around 25 minutes.
I want to avoid the Performance Issue..
So i Need Another Method to calculate The Cumulative Sum...(for 40000 Records)
Thanks & Regards..
Saravanan.D
May 22, 2013 at 2:58 am
The fastest way to calculate "running" total in SQL is here:
http://www.sqlservercentral.com/articles/T-SQL/68467/
SQL2012 now has in-build functionality for doing that, but the above method still wins most of the time.
May 22, 2013 at 3:09 am
Hi,
I think you should put the first query in a temp table:
SELECT EmpId,
EmpName,
ProcessName,
GroupCode,
PlannedDays,
ActualDays,
DelayedDays,
rowid = ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY GroupCode)
INTO #base
FROM EmpProcessDetail
Then use this temp table for your results:
SELECT
EmpId,
EmpName,
ProcessName,
GroupCode,
PlannedDays,
ActualDays,
DelayedDays,
x.TotalDelayDays
FROM
#BASE b
CROSS APPLY (SELECT TotalDelayDays = SUM(DelayedDays)
FROM #base BASE
WHERE EmpID = b.EmpID
AND rowid <= b.rowid) x
When data is in memory, it comes within 1 second.
Kind Regards
Geoffrey
May 22, 2013 at 3:24 am
@ Eugene: amazing, that technique!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply