Introduction to the course

Writing Functions and Stored Procedures in SQL Server

Meghan Kwartler

IT Consultant

Course objectives

  • Perform temporal exploratory data analysis (EDA) using SQL date functions
  • Create, update, execute User-Defined Functions
  • Create, update, execute Stored Procedures
  • Prerequisites
    • Introduction to SQL Server
    • Joining Data in SQL
    • Intermediate SQL Server
Writing Functions and Stored Procedures in SQL Server

Temporal EDA

  • Transactional datasets
    • CapitalBikeShare
    • YellowTripTaxi
  • Exploratory Data Analysis (EDA) Process
    • Iterative
    • No specific checklist for EDA questions
    • Get curious!
    • Reduces re-work effort

EDA Visual

Writing Functions and Stored Procedures in SQL Server

SQL functions for EDA

-- CONVERT Syntax:  
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )  
-- Returns expression based on data_type
-- DATEPART Syntax
DATEPART ( datepart , date ) 
-- Returns int
-- DATENAME Syntax
DATENAME ( datepart , date ) 
-- Returns nvarchar
-- DATEDIFF Syntax
DATEDIFF ( datepart , startdate , enddate ) 
-- Returns int; can't use datepart weekday value
-- datepart values = year, quarter, month, dayofyear, day, week, weekday, hour, 
-- minute, second, microsecond, nanosecond
Writing Functions and Stored Procedures in SQL Server
-- CONVERT
SELECT 
  TOP 1 PickupDate, 
  CONVERT (DATE, PickupDate) AS DateOnly 
FROM YellowTripData
+-----------------------------+------------+
| PickupDate                  | DateOnly   |
|-----------------------------+------------|
| 2017-01-09 11:52:13.0000000 | 2017-01-09 |
+-----------------------------+------------+
Writing Functions and Stored Procedures in SQL Server
-- DATEPART
SELECT 
  TOP 3 COUNT(ID) AS NumberofRides, 
  DATEPART(HOUR, PickupDate) AS Hour 
FROM YellowTripData 
GROUP BY DATEPART(HOUR, PickupDate) 
ORDER BY COUNT(ID) DESC
+----------------------+
| NumberOfRides | Hour |
|----------------------+
| 616281        | 18   |
| 616281        | 19   |
| 540629        | 17   |
+---------------+------+
Writing Functions and Stored Procedures in SQL Server
--DATENAME
SELECT 
  TOP 3 ROUND(
    SUM(FareAmount), 
    0
  ) as TotalFareAmt, 
  DATENAME(WEEKDAY, PickupDate) AS DayofWeek 
FROM YellowTripData 
GROUP BY DATENAME (WEEKDAY, PickupDate) 
ORDER BY SUM(FareAmount) DESC;
+--------------------------+
| TotalFareAmt | DayofWeek |
|--------------------------+
| 19026645     | Sunday    |
| 18749482     | Tuesday   |
| 17213351     | Thursday  |
+--------------+-----------+
Writing Functions and Stored Procedures in SQL Server
--DATEDIFF
SELECT 
  AVG(
    DATEDIFF(SECOND, PickupDate, DropOffDate)/ 60
  ) AS AvgRideLengthInMin 
FROM YellowTripData 
WHERE DATENAME(WEEKDAY, PickupDate) = 'Sunday';
+--------------------+
| AvgRideLengthInMin |
|--------------------+
| 13                 |
+--------------------+
Writing Functions and Stored Procedures in SQL Server

Let's practice!

Writing Functions and Stored Procedures in SQL Server

Preparing Video For Download...