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
BusinessEntityIDTerritoryIDSalesYTDSalesYearCumulativeTotal
274NULL559,697.5620051,079,603.50
287NULL519,905.932006692,430.38
285NULL172,524.452007172,524.45
28011,352,577.1320052,929,139.33
28311,573,012.9420052,925,590.07
28411,576,562.2020061,576,562.20
27523,763,178.1820053,763,178.18
27733,189,418.3720053,189,418.37
27644,251,368.5520056,709,904.17
28142,458,535.6220052,458,535.62
now when I do a manual calculation it is as follow:
BusinessEntityIDTerritoryIDSalesYTDSalesYearCumulativeTotal
274NULL559,697.5620051,079,603.50 (559,697.56+519,905.93)
287NULL519,905.932006692,430.38 (519,905.93+172,524.45)
285NULL172,524.452007172,524.45 (172,524.45)
28011,352,577.1320054502152.27 (1,352,577.13+1,573,012.94+1,576,562.20)=>different conclusion
28311,573,012.9420054502152.27 (1,352,577.13+1,573,012.94+1,576,562.20)=>different conclusion
28411,576,562.2020061,576,562.20 (1,576,562.20+0)
27523,763,178.1820053,763,178.18 (3,763,178.18+0)
27733,189,418.3720053,189,418.37 (3,189,418.37)
27644,251,368.5520056,709,904.17 (4,251,368.55+2,458,535.62)
28142,458,535.6220052,458,535.62 (2,458,535.62+0)
the actual result is as follow
BusinessEntityIDTerritoryIDSalesYTDSalesYearCumulativeTotal
28011,352,577.1320052,929,139.33 (1,352,577.13+1,576,562.20)=>why is the outcome as follow?
28311,573,012.9420052,925,590.07 (1,573,012.94+1,576,562.20)=>why is the outcome as follow?
28411,576,562.2020061,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