書式設定ツール

SQL Server における関数とストアドプロシージャの作成

Meghan Kwartler

IT Consultant

書式設定前

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       |
+-----------+---------------|

曜日順に並べ替え

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 |
+-----------+---------------|

あなたの番！

SQL Server における関数とストアドプロシージャの作成