Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

over clause discrepancy Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 9:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 6:50 PM
Points: 39, Visits: 141
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
Post #1505298
Posted Wednesday, October 16, 2013 11:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:10 PM
Points: 115, Visits: 628
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.



Post #1505355
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse