Formatting tools

Writing Functions and Stored Procedures in SQL Server

Meghan Kwartler

IT Consultant

Before formatting

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

Sort by logical weekday

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

Your turn!

Writing Functions and Stored Procedures in SQL Server