SQL-Language

Intagration von Aggregatfunktionen in eine Partition

http://msdn2.microsoft.com/de-de/library/ms189461.aspx

USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO

http://msdn2.microsoft.com/de-de/library/ms187928.aspx

bei SQL beachten, Kommazahlen mit Punkt !

varchar in decimal konvertieren

• Price2 ist varchar, z.B. Price2 = '0.115395'
• UPDATE DialPrefixCharge SET Price3 = CAST(Price2 AS decimal(18, 15))
• Ergebnis Price3 = 0,115395000000000

in SQL-Konvertierungen muss die zu konvertierende Dezimalzahl ein Punkt enthalten

decimal(18,15): 18 Stellen insgesamt, 15 Nachkommastellen