به دو روش 2012 و 2008 برات می نویسم
دوست عزیز از این به بعد زحمت بکش اگه چنین سوالاتی داشته اسکریپت تولید دیتا رو هم بنویس تا برامون راحت تر باشه..
اول به روش 2012
CREATE TABLE T1(ActionDate Int, Name nvarchar(10), Percentage decimal(18,2) )
INSERT T1 VALUES
(20120403,'a',-3.57),
(20120404,'a',-0.08),
(20120407,'a',-3.63),
(20120408,'a',1.70),
(20120409,'a',3.97),
(20120410,'a',3.97),
(20120411,'a',-2.64),
(20120403,'b',-0.08),
(20120404,'b',-2.27),
(20120407,'b',-3.77),
(20120408,'b',-1.41),
(20120409,'b',3.91),
(20120410,'b',3.53),
(20120411,'b',-2.10);
WITH CTE ( RW, PriorPercentage, Name, Percentage ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ActionDate),
ISNULL( LAG(Percentage) OVER (PARTITION BY Name ORDER BY ActionDate),Percentage),
Name, Percentage
FROM T1
),
CTE_REC(RW, Name, Percentage, Grp) AS
(
SELECT RW, Name, Percentage, 0 Grp FROM CTE
WHERE RW =1
UNION ALL
SELECT A.RW , A.Name, A.Percentage, B.Grp + CASE WHEN SIGN(A.Percentage) <> SIGN(A.PriorPercentage) THEN 1 ELSE 0 END FROM CTE A
INNER JOIN CTE_REC B ON A.RW = B.Rw+1 and a.Name = b.Name
)
SELECT DISTINCT
Name,
'SumGroup'=SUM(Percentage) OVER(PARTITION BY Name,GRP),
'CountGroup'=COUNT(Percentage) OVER(PARTITION BY Name,GRP)
FROM CTE_REC
و به روش 2008 چون 2008 از دستور LAG پشتیبانی نمی کند
CREATE TABLE T1(ActionDate Int, Name nvarchar(10), Percentage decimal(18,2) );
INSERT T1 VALUES
(20120403,'a',-3.57),
(20120404,'a',-0.08),
(20120407,'a',-3.63),
(20120408,'a',1.70),
(20120409,'a',3.97),
(20120410,'a',3.97),
(20120411,'a',-2.64),
(20120403,'b',-0.08),
(20120404,'b',-2.27),
(20120407,'b',-3.77),
(20120408,'b',-1.41),
(20120409,'b',3.91),
(20120410,'b',3.53),
(20120411,'b',-2.10);
WITH CTE_T1 AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ActionDate) as RW,
ActionDate,Name, Percentage
FROM T1
),
CTE ( RW, PriorPercentage, Name, Percentage ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.Name ORDER BY A.ActionDate),
ISNULL(B.Percentage,A.Percentage) as PriorPercentage,
A.Name, A.Percentage
FROM CTE_T1 A
LEFT JOIN CTE_T1 B ON A.Name = B.Name and A.RW = B.RW+1
)
,
CTE_REC(RW, Name, Percentage, Grp) AS
(
SELECT RW, Name, Percentage, 0 Grp FROM CTE
WHERE RW =1
UNION ALL
SELECT A.RW , A.Name, A.Percentage, B.Grp + CASE WHEN SIGN(A.Percentage) <> SIGN(A.PriorPercentage) THEN 1 ELSE 0 END FROM CTE A
INNER JOIN CTE_REC B ON A.RW = B.Rw+1 and a.Name = b.Name
)
SELECT DISTINCT
Name,
'SumGroup'=SUM(Percentage) OVER(PARTITION BY Name,GRP),
'CountGroup'=COUNT(Percentage) OVER(PARTITION BY Name,GRP)
FROM CTE_REC