Biçimlendirme araçları

SQL Server'da Fonksiyonlar ve Saklı Yordamlar Yazma

Meghan Kwartler

IT Consultant

Biçimlendirmeden önce

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

Mantıksal haftaya göre sırala

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

Sıra sende!

SQL Server'da Fonksiyonlar ve Saklı Yordamlar Yazma