Outils de mise en forme

Écrire des fonctions et des procédures stockées dans SQL Server

Meghan Kwartler

IT Consultant

Avant la mise en forme

SELECT 
  DATENAME(weekday, StartDate) AS 'DayOfWeek', 
  SUM(Duration) AS TotalDuration 
FROM CapitalBikeShare 
GROUP BY DATENAME(weekday, StartDate) 
ORDER BY DATENAME(weekday, StartDate)

+-----------+---------------+
| DayOfWeek | TotalDuration |
|-----------+---------------|
| Friday    | 7264870       |
| Monday    | 6571322       |
| Saturday  | 13411642      |
| Sunday    | 8418226       |
| Thursday  | 8646359       |
| Tuesday   | 3788474       |
| Wednesday | 3525955       |
+-----------+---------------|

Trier par jour logique de la semaine

SELECT DATENAME(weekday, StartDate) as 'DayOfWeek', 
SUM(Duration) as TotalDuration
FROM CapitalBikeShare
GROUP BY DATENAME(WEEKDAY, StartDate)
ORDER BY 
     CASE WHEN Datename(WEEKDAY, StartDate) = 'Sunday' THEN 1
          WHEN Datename(WEEKDAY, StartDate) = 'Monday' THEN 2
          WHEN Datename(WEEKDAY, StartDate) = 'Tuesday' THEN 3
          WHEN Datename(WEEKDAY, StartDate) = 'Wednesday' THEN 4
          WHEN Datename(WEEKDAY, StartDate) = 'Thursday' THEN 5
          WHEN Datename(WEEKDAY, StartDate) = 'Friday' THEN 6
          WHEN Datename(WEEKDAY, StartDate) = 'Saturday' THEN 7
    END ASC;

+-----------+---------------+
| DayOfWeek | TotalDuration |
|-----------+---------------|
| Sunday    | 8418226       |
| Monday    | 6571322       |
| Tuesday   | 3788474       |
| Wednesday | 3525955       |
| Thursday  | 8646359       |
| Friday    | 7264870       |
| Saturday  | 13411642      |
+-----------+---------------|

SELECT TOP 5 
FORMAT(CAST(StartDate as Date), 'd', 'de-de')
AS 'German Date',
FORMAT(CAST(StartDate as Date), 'd', 'en-us')
AS 'US Eng Date',
FORMAT(Sum(Duration), 'n', 'de-de')
AS 'German Duration',
FORMAT(SUM(Duration), 'n', 'en-us')
AS 'US Eng Duration',
FORMAT(SUM(Duration),'#,0.00') 
AS 'Custom Numeric'
FROM CapitalBikeShare
GROUP BY CAST(StartDate as Date)

+-------------+-------------+-----------------+-----------------+----------------+
| German Date | US Eng Date | German Duration | US Eng Duration | Custom Numeric |
|-------------+-- ----------+-----------------+-----------------+----------------|
|  09.03.2018 | 3/9/2018    | 1.141.796,00    | 1,141,796.00    | 1,141,796.00   |
|  18.03.2018 | 3/18/2018   | 3.074.907,00    | 3,074,907.00    | 3,074,907.00   |
|  12.03.2018 | 3/12/2018   | 1.088.822,00    | 1,088,822.00    | 1,088,822.00   |
|  26.03.2018 | 3/26/2018   | 2.160.609,00    | 2,160,609.00    | 2,160,609.00   |
|  29.03.2018 | 3/29/2018   | 3.552.955,00    | 3,552,955.00    | 3,552,955.00   |
+-------------+-------------+-----------------+-----------------+----------------|

SELECT DATENAME(weekday, StartDate) 
AS 'DayOfWeek', 
FORMAT(SUM(Duration),'#,0.00') 
AS 'TotalDuration'
FROM CapitalBikeShare
GROUP BY DATENAME(WEEKDAY, StartDate)
ORDER BY 
     CASE
          WHEN Datename(WEEKDAY, StartDate) = 'Sunday' THEN 1
          WHEN Datename(WEEKDAY, StartDate) = 'Monday' THEN 2
          WHEN Datename(WEEKDAY, StartDate) = 'Tuesday' THEN 3
          WHEN Datename(WEEKDAY, StartDate) = 'Wednesday' THEN 4
          WHEN Datename(WEEKDAY, StartDate) = 'Thursday' THEN 5
          WHEN Datename(WEEKDAY, StartDate) = 'Friday' THEN 6
          WHEN Datename(WEEKDAY, StartDate) = 'Saturday' THEN 7
    END ASC

+-----------+---------------+
| DayOfWeek | TotalDuration |
|-----------+---------------|
| Sunday    | 8,418,226.00  |
| Monday    | 6,571,322.00  |
| Tuesday   | 3,788,474.00  |
| Wednesday | 3,525,955.00  |
| Thursday  | 8,646,359.00  |
| Friday    | 7,264,870.00  |
| Saturday  | 13,411,642.00 |
+-----------+---------------|

À vous de jouer !

Écrire des fonctions et des procédures stockées dans SQL Server