SQL Server – How to Use Convert vs CAST

sql serversql-server-2008

This is a piece of code on MSDN page for OVER clause:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, TerritoryID      
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID 
                                        ORDER BY DATEPART(yy,ModifiedDate) 
                                       ),1) AS MovingAvg
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 
                                        ORDER BY DATEPART(yy,ModifiedDate) 
                                        ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

So I am having issues understanding why CONVERT function had to be used there. I assume it is to do with return types of one of the fields in the expression part of the convert function? Can CAST be used instead?
Second question I have is, how exactly does this part work?

SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)

What exactly is that saying? Is it calculating sum for every year? Is that it?

Best Answer

Convert style

  • If SalesYTD type is float or real:

    1 = Always 8 digits. Always use in scientific notation.

  • If type is money and small money:

    1 = Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

Cast will use the default value for style: 0

  • If SalesYTD type is float or real:

    0 (default) = A maximum of 6 digits. Use in scientific notation, when appropriate.

  • If type is money and small money:

    0 (default) = No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

There are other differences and default values for style with other types but I think it covers your data types. See CAST and CONVERT (Transact-SQL)

SUM window function

For each partition (=TerritoryID) it takes all SalesYTD, order them by DATEPART(yy,ModifiedDate) and sum them. Note that the Order part is not necessary or could be anything since you only do SUM and AVG but using it change the behaviors (see hypercube answer about it and moving average). With ROW_NUMBER and others, it would be important and mandatory.

Diffence between SUM and moving SUM (@hypercube answer):

declare @data table(TerritoryID int, SalesYTD int, ModifiedDate date)
insert into @data(TerritoryID, SalesYTD, ModifiedDate) values
(1, 1, '20100101')
, (1, 2, '20110101')
, (1, 3, '20120101')
, (1, 4, '20130101')
, (1, 5, '20140101')
, (2, 6, '20150101')
, (2, 7, '20160101')
, (2, 8, '20170101')

Select *
    , [Moving SUM] = SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate))
    , [SUM] = SUM(SalesYTD) OVER (PARTITION BY TerritoryID)
From @data

Output:

TerritoryID | SalesYTD | ModifiedDate | Moving SUM    | SUM
---------------------------------------------------------------------
1           | 1        | 2010-01-01   | 1 => 1        | 10 => 1+2+3+4
1           | 2        | 2011-01-01   | 3 => 1+2      | 10 => 1+2+3+4
1           | 3        | 2012-01-01   | 6 => 1+2+3    | 10 => 1+2+3+4
1           | 4        | 2013-01-01   | 10 => 1+2+3+4 | 10 => 1+2+3+4
2           | 6        | 2015-01-01   | 6 => 6        | 21 => 6+7+8
2           | 7        | 2016-01-01   | 13 => 6+7     | 21 => 6+7+8
2           | 8        | 2017-01-01   | 21 => 6+7+8   | 21 => 6+7+8

See OVER Clause (Transact-SQL) for window functions.

Related Question