October 16, 2013 at 9:27 am
Hi,
with reference to http://technet.microsoft.com/en-us/library/ms189461.aspx
D. Specifying the ROWS clause
SELECT BusinessEntityID, TerritoryID
,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;
if I modified the code as follow
SELECT BusinessEntityID, TerritoryID
,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID, SalesYear, BusinessEntityID;
result is as follow
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
274 NULL 559,697.56 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
280 1 1,352,577.13 2005 2,929,139.33
283 1 1,573,012.94 2005 2,925,590.07
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 2,458,535.62
now when I do a manual calculation it is as follow:
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
274 NULL 559,697.56 2005 1,079,603.50 (559,697.56+519,905.93)
287 NULL 519,905.93 2006 692,430.38 (519,905.93+172,524.45)
285 NULL 172,524.45 2007 172,524.45 (172,524.45)
280 1 1,352,577.13 2005 4502152.27 (1,352,577.13+1,573,012.94+1,576,562.20)=>different conclusion
283 1 1,573,012.94 2005 4502152.27 (1,352,577.13+1,573,012.94+1,576,562.20)=>different conclusion
284 1 1,576,562.20 2006 1,576,562.20 (1,576,562.20+0)
275 2 3,763,178.18 2005 3,763,178.18 (3,763,178.18+0)
277 3 3,189,418.37 2005 3,189,418.37 (3,189,418.37)
276 4 4,251,368.55 2005 6,709,904.17 (4,251,368.55+2,458,535.62)
281 4 2,458,535.62 2005 2,458,535.62 (2,458,535.62+0)
the actual result is as follow
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
280 1 1,352,577.13 2005 2,929,139.33 (1,352,577.13+1,576,562.20)=>why is the outcome as follow?
283 1 1,573,012.94 2005 2,925,590.07 (1,573,012.94+1,576,562.20)=>why is the outcome as follow?
284 1 1,576,562.20 2006 1,576,562.20 (1,576,562.20+0)
would appreciate someone could point me as in why the above discrepancy happen?
thanks
October 16, 2013 at 11:58 am
The example is using "ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING". Just two values are possible, so why are you adding three values?
-- wrong
280 1 1,352,577.13 2005 4502152.27 (1,352,577.13+1,573,012.94+1,576,562.20)=>different conclusion
283 1 1,573,012.94 2005 4502152.27 (1,352,577.13+1,573,012.94+1,576,562.20)=>different conclusion
284 1 1,576,562.20 2006 1,576,562.20 (1,576,562.20+0)
280 1 1,352,577.13 2005 (current 1,352,577.13 + next in the partition / order 1,573,012.94)
283 1 1,573,012.94 2005 (current 1,573,012.94 + next in the partition / order 1,576,562.20)
284 1 1,576,562.20 2006 (current 1,576,562.20 because there is no next in the partition / order)
The partition is by TerritoryID and the order is by year. Since territoryid = 1 has two rows with same value for the year then the ordering is not deterministic and it is possible that next time you execute the query the result could be different. To break ties they should have added a column like BusinessEntityID to the order, as you did in the ORDER BY clause.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply